SSRS: Retrieving Member Properties in Reporting Services (with Analysis Services)

In Reporting Services, it is unclear how, or if it is even possible to retrieve member properties when using an analysis services data source.

Member properties refer to the extra information attributed to members apart from their names. For instance, in the AdventureWorksDW, the Customer dimension contains several member properties including Address, Gender and Phone.

Screenshot 1 - Member Properties

Dragging these properties into the query window in Reporting Services unfortunately does not do the job. From what I've seen so far, it also does not seem possible from the query designer in design mode. However you can make a small change to the MDX code in order to retrieve these properties.

Change the design mode to see the MDX . Within the code, one line reads "DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME" as in the screenshot below.

Screenshot 2 - MDX Code to look for

Following that, add a comma and insert the syntax for the member property that you wish to retrieve. For instance, if I want to retrieve the Customer's Address, I would add [Customer].[Customer].[Address].

Screenshot 3 - Addition to the MDX Code

If you are in doubt about the syntax, look for the property in the left panel and hover your mouse over it. A tooltip will show you the syntax, but you need to modify it slightly.

Screenshot 4 - Tooltip showing Member Property Syntax

Remove the "Properties" and parenthesis, and replace the double quotes with square brackets. So [Customer].[Customer].Properties( "Address" ) would become [Customer].[Customer].[Address]

After successfully doing this and executing the query to test the result, you can freely use the property as you use any other field in the report (Fields!Address.Value).

Screenshot 5 - Result with Customer Address

Few Notes:

• After doing this, you will be unable to use the graphical designer to edit the query as that will undo any changes you made to the MDX code. This could compromise the maintainability of the report especially if you are unfamiliar with MDX.
• An alternative is to have some design changes to the cubes, such that all member properties are separate members themselves. This allows you to easily drag the fields into the query and it's not a bad cube design approach in itself.

Tags: reporting services analysis services mdx member properties dimension properties