Dimension Member as Measure in MDX

Question

I need to get a dimension member returned as a measure.

Given:

Dimensions

[Customer].[customer name] members {ACME, EMCA, EMC}    

[Salesperson].[person name] members {Bob, Fred, Mary, Joe} with the property "name"

Measures Value

Relationships

The Customer is a dimension of the facts that contain Value
The Salesperson is a dimensions of the facts that contain Value, each customer has one salesperson associated

I am trying to do the following:

Create measures that will return the name of the salesperson in a column for a customer. e.g.

| Customer          |Sales person | Value |
| ACME              | Bob         | 500   |
| EMCA              | Bob         | 540   |
| EMC               | Mary        | 840   |

I have tried like this:

With Member [measure].[sp_name] as [Salesperson].[person name].currentmember.properties("name")

Select {[measure].[sp_name], [measures].[value]} on 0 {[customer].[customer name].members} on 1 from

But it always returned error "properties name is not valid for [Salesperson].[all salesperson]" And if I used With Member [measure].[sp_name] as [Salesperson].[person name].currentmember.firstindex.properties("name") I got one person for all the customers, which is obviously not right. Did I miss anything?

FrankPl FrankPl · Accepted Answer · 2014-02-24T18:07:27

I would say that in Analysis Services 2008, this should work as you implemented it, returning null for the All member. If the environment that you use is more sensitive, then maybe you could use something like

With Member [measure].[sp_name] as
     IIF([Salesperson].[person name].currentmember IS [Salesperson].[all salesperson],
         'All',
         [Salesperson].[person name].properties("name")
        )
Select {[measure].[sp_name], [measures].[value]} on 0,
       {[customer].[customer name].members} on 1
  from [MyCube]

Dimension Member as Measure in MDX

2 Answers