Donnerstag, 9. Juli 2015

TopCount ResultSet with always the same number of rows

Some days ago I had the request to create an MDX Query which should return the Top 10 Customers. Well, no big problem - just using the TopCount() function. But the requirement also includes, that there should be always 10 rows returned, nevertheless there can be 10 records delivered by the query or not.
For the example here I demostate it by using the AdventureWorks:


Resolution:

First, I created 10 Dummy Members:

MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder1] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder2] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder3] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder4] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder5] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder6] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder7] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder8] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder9] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder10] as NULL


Then a Set based on that Dummy Members

SET [Placeholder] as
{[Customer].[Customer Geography].[All Customers].[Placeholder1]
,[Customer].[Customer Geography].[All Customers].[Placeholder2]
,[Customer].[Customer Geography].[All Customers].[Placeholder3]
,[Customer].[Customer Geography].[All Customers].[Placeholder4]
,[Customer].[Customer Geography].[All Customers].[Placeholder5]
,[Customer].[Customer Geography].[All Customers].[Placeholder6]
,[Customer].[Customer Geography].[All Customers].[Placeholder7]
,[Customer].[Customer Geography].[All Customers].[Placeholder8]
,[Customer].[Customer Geography].[All Customers].[Placeholder9]
,[Customer].[Customer Geography].[All Customers].[Placeholder10]}
 

Then a Set based for the TopCount

SET [Top10] as
TopCount([Customer].[Customer Geography].[All Customers].children
,10
,[Measures].[Internet Sales Amount])
 


Next I created a combined Set of my DummySet and may Top10 Set

SET [Top10PlaceholderCombined] as
UNION([Top10], [Placeholder])

Then I created a measure to get the rownumber of the combined set by using the Rank() Function:

MEMBER [Measures].[Row_Number] as
RANK([Customer].[Customer Geography].currentmember
,[Top10PlaceholderCombined])

Finally I come to my Select Statement where I just Filter() my set.

Select FILTER([Top10PlaceholderCombined], [Measures].[Row_Number]<11) on rows,
[Measures].[Internet Sales Amount] on columns
FROM [Adventure Works]

Result:

Keine Kommentare:

Kommentar veröffentlichen