Max () MDX

# Max () MDX

• Friday, August 08, 2008 12:32 AM

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

### All Replies

• Friday, August 08, 2008 5:15 AM

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