SSRS & MDX : Include Member Key in Report

Requirement:  to use the a member Key in a report rather than the display name or Value properties.

Problem the SSRS query builder doesn’t allow selection of properties to return.  Reference to Fields!MemberName.Key returns Null in reports.

One Solution:  Locate following snippet in SSRS-Generated MDX (using the fx button on Dataset properties dialog)

...me].[Typecodename].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM...

and add “, MEMBER_KEY”

...me].[Typecodename].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_KEY ON ROWS FROM...

Effect on Query Builder: the query builder still works but using it will remove the added MEMBER_KEY so it has to be re-added manually.  The risk is that this will be overlooked, so you need to ensure missing this causes noticeable error.

A better solution is to have an additional dimension member that returns the required Value or Name property.

Minimal Permissions

What are minimal permissions required for certain operations?

  • Run Profiler
    • ALTER TRACE
  • SSDT Import a database schema into project
    • VIEW DEFINITION
    • VIEW SERVER STATE
    • If working with multiple databases use  VIEW ANY DEFINITION run on master.
  • SSDT Make schema updates (Publish)
    • Add user to db_ddladmin role.
    • Add to dbcreator server role to create new databases.