Manipulating Relationships in VBA (in 2013)

June 10, 2014

Guest Post From Dany Hoter

Intro from Rob:  Ah, the international man of mystery returns!  My first instincts when I think of Dany Hoter, other than “one of the most fabulous humans I have ever known,” generally can be summarized as “MDX and Cube Formulas Monster.”

But he’s far from a one-trick pony.  Generally speaking, he has a level of tenacity and patience rarely encountered outside of laboratory conditions.  Couple that with an insatiable drive for The Right Thing, and you get some crazy results.

Today is one such CRAZY example.  Simultaneously, he shows us how to compensate for a drillthrough bug, AND delivers a working example of relationship manipulation via VBA macros.

THIS IS AN ADVANCED TOPIC POST.  Feel free to skip this one.  This is the deep end of the pool and even I don’t swim in these particular waters yet.

Note of course that this technique is 2013 only, and will not work with Power Pivot in 2010.

Take it away, Dany…

A Drillthrough Bug with Inactive Relationships

I started this VBA project after one of our partners wrote to me about a customer complaint regarding inactive relationships in Power Pivot:

image

The Sales Table has TWO Relationships to Calendar – One is Based on OrderDate (Active), and the Other is Based on ShipDate (Inactive – Dashed Line)

 

Read the rest of this entry »


Reverse Polarity RELATED()

October 3, 2013

 

Guest post by Ken Puls

I was recently working on an interesting thing in PowerPivot, and thought I’d share it in case it may help someone… A quick summary of the issue is that I needed to examine multiple records in a child table, and return a single result to the parent table if a condition was present; something that goes against the normal relationship flow.

Scenario Background

I have a file that tracks the purchase and sale of land, as well as the respective property taxes and assessments that we get on an annual basis. It is set up like this:

9-17-2013 2-56-59 PM

At first glance this might look complicated… Each Parcel is assigned a unique Parcel Identifier, known as a PID, a key identifier that will never change for a piece of property.  This info is then used to identify that parcel by the tax authority, the assessment authority, and our appraisers and accounting systems.  In the case above, we can see that the PID from the Parcel table links to the Transaction, Taxes and Assessments tables. From the other side we’ve got the Key_Date that links back to the three tables as well, although it links to TranDate, TaxYear and AssessYear respectively.

Read the rest of this entry »


The Dreaded “One to One” Relationship, or Why “Boaring” Movies Are Best

June 4, 2013

Another Example of “Relationship May Be Needed” When You Do, In Fact, Have a Relationship

Let’s pick up the topic from last week and examine another instance where you might encounter the dreaded warning message above.

Let’s start with this simple pivot:

image

Which is a column from the Years table and a column from the Movies table:

image

But now I have a new table:  Animals, which simply lists, for each year, which Animal is specified by the Chinese Zodiac:

image

Hollywood Insiders Know That the Animals of the Chinese Zodiac Are
Incredibly Important to Box Office Revenues

 

Read the rest of this entry »


“Relationship may be needed?” But I already have a relationship! What’s going on?

May 30, 2013

 
image

“Well I’m not quite ready for a relationship right now Lois.”

A confusing error getting you down?

I got an email this morning from a friend who was running into this problem:

PowerPivot says "Relationship may be needed" - but you do have a relationship.  What's going on?

Are you getting this warning in the field list,
even though you DO have a relationship in place?

What gives?  Let’s return to our recent movies example.

To help you diagnose this problem, should you run into it, first let’s get a simple example ready.

Two tables of data in PowerPivot:  Movies, and Years.

Read the rest of this entry »