This post is about Panorama because it is the UI tool I'm working with, but this can be made with every BI UI tool.
My customer wanted to get the effect shown by Analysis Services 2005 when browsing a dimension (see the picture below). He wanted to see some properties of the members shown in the rows, along with the usual measures. Unfortunately, Panorama (and I'm sure that also other tools) does not have this option in the GIU. The solution is this code:
Create Member CurrentCube.[Measures].[MyProperty] as
iif(IsLeaf([MyDimension].[MyHierarchy].CurrentMember),
[MyDimension].[MyHierarchy].CurrentMember.Properties("MyProperty"),
Null)
Note that declaring only the third row will cause that every member that is not a leaf will cause an error, which is something we don't want the viewer to see. If the dimension has properties for members in other levels too, you can adjust this decleration. This member can be declared either in the DataBase's Script (after the CALCULATE expression) or inside the session/query (not recommended in Panorama). Now, all you have to do is to show the dimension's members in the rows and this new measure in the columns (after or before the regular measures), and you'll get what you want.
