Here is what I would consider to be a magic post: http://richardlees.blogspot.com/2011/06/linkmember.html
Mr. Lees does the Lord's work here, and surely helped me out of a jam. I was trying to figure out a way to collect a single measure across multiple role-playing dimensions. Everything I tried was epic fail, including the solution I initially arrived at which was to create 3 separate queries and let SSRS sort them out....
To set the scene, an example of the problem I was having would be:
- I want to find the numbers of New Widgets, Sold Widgets, and Improper Widgets, by month over the last 12 months (Rolling 12 Month totals).
- In my SSAS cube I have a Measure for Widget Count, and I have 3 role-playing Date dimensions -- Released Date, Sale Date, Inspection Failed Date -- all based on a single Date dimension living in the underlying Data Mart.
- Appropriately filtering and slicing the Widget Count by any of the above dates yields correct results. But since the cube believes (techically properly, I guess) that those role-playing dimensions are all actually different dimensions, I cannot get a good slicer Axis out of only one of the date dimensions. So, in a nutshell
In other words, I couldn't get into a single query/dataset all 3 of the desired measures. This was because I really needed to query a single measure across 3 different role-playing dimensions.
So, in comes Mr. Lees. He introduced me to the magic of LinkMember(). And thus I quickly solved my problem. LinkMember allowed me to create a Calculated Member that gave me totals aligned with each of the Role Playing Dimensions I was looking at. Booyah, granny!
