CODEWITHSUNDEEP
olapmdx
Tim C
Tim C

Reputation: 70608

How to display the total of a level as the value of its last child in MDX

I have an MDX query which lists a measure for all 'Week' and 'Day' levels in the OLAP database. For example

SELECT 
{
    HIERARCHIZE( {[Business Time].[Week].members, [Business Time].[Date].members} )
} 
ON ROWS,
{
    [Measures].[Quantity]
} 
ON COLUMNS 
FROM [Sales]

Where the measure is displayed for a Week though, instead of showing the total of all the Day values, I would like to show the value for the last day within the week. For example

Week 1: 12
 15 Sept: 10
16 Sept: 20
17 Sept: 12
18 Sept: 15
19 Sept: 8
20 Sept: 9
21 Sept: 12
Week 2: 15
 22 Sept: 12
23 Sept: 15

How can I achieve this within the MDX?

Upvotes: 1

Views: 3356

Answers (1)

Magnus Smith
Magnus Smith

Reputation: 5963

Add a new calculated measures onto the start of your MDX that shows the last day's value only if it is being shown at a week level, otherwise leave it unaltered:

WITH MEMBER [Measures].[Blah] AS 
'IIF(
   [Business Time].currentMember.level.name = "Week",
   ([Business Time].currentMember.lastChild, [Measures].[Quantity]),
   ([Business Time].currentMember, [Measures].[Quantity])
)'

I expect a client asked for this odd request, and I predict you'll get a call in a month from someone in the same office, saying the weekly 'total' is wrong on this report!

Upvotes: 1

Related Questions