Max () MDX

Question

 

I have some data that looks like this.

Client, Person, Role, Date are the dimension associated to a Duration fact. 

 

I filtered the data on some roles([Role Group1]) from Role hierarchy of Role dimension

Client	Assigned Person	Year	Duration
ABC Corp	Jay Smith	2007	295
ABC Corp	Jay Smith	2008	52
ABC Corp	Jessica Alba	2007	330
ABC Corp	Jessica Alba	2008	54
ABC Corp	Johnny Depp	2008	28
ABC Corp	Steve Carell	2007	272
ABC Corp	Steve Carell	2008	53
ABC Corp	Orlando Bloom	2008	97

 

 

I am interested in getting the Max(Duration) per client sliced by peron. That means the max fom the following data...

 

 

ABC Corp	Jay Smith		347
ABC Corp	Jessica Alba		384
ABC Corp	Johnny Depp		28
ABC Corp	Steve Carell		325
ABC Corp	Orlando Bloom		97

.. and the max duration would be 384

 

I wrote the following expression to get it.

Code Snippet

With Member [Max Duration] AS

max(

{

[Role].[Role Hierarchy].[Role Groups].[Role Group1]

*

[Person].[Name].[All].children

},

[Measures].[Duration])

 

 

when I wrote the following query

Code Snippet

Select [Max Duration] on columns,

non empty

[Organization].[Geography Hierarchy].[Area].[US].[Midwest].[Illinois].[ABC Corp]

Center]

on rows from Cube1

 

 

it gave the expected result which is 384.

 

I took the same expression for max duration and created a calculated member [Max Duration] and browsed the cube with just [ABC Corp] on the Rows and [Max Duration] on the coulumns. It showed the total duration 1181!

I expected it to show the value 384 because the calculated member [Max Duration] must have the maximum value.

 

Other findings..

 I pasted the same expression (not the calculated member) into KPI Value expression in the cube and deployed. The KPIValue is showing 384 in BIDS project when i browse the KPI filtered on ABC Corp.

 

When i created a report in SSRS with the KPI Value filtered on Client = 'ABC Corp', it is showing 330 !

It was strange.

 

The expression i wrote for the max duration seems to be acting weird.  Do i have to change the expression, or am i missing any slice in the expression..?

any input would be very helpful...

 

Thanks

 

 

Answer 1

 

When u browsed the cube for the ABC Corp, filter the rows set by [US].[Midwest].[Illinois].[ABC Corp]. By default all members are checked on the slice.

 

If u use the SSMS, you can fire the exact query and get the expected result. Not sure, if SSRS expose the KPIs value. Check it out using Excel Services

 

Thank you

Imran Shah