MDX SSAS. TOP COUNT

0
votes

I have created a cube in SSAS: Measures: Income Dimension Time (Hierarchy: day-month-quarter-year) Dimension CustomerId

I need to create a report where: I will see Top10 and Bottom10 Customers by Income and there % of income from total. It should work at each level of hierarchy. I need to write an MDX in Visual Studio (not SQL Server) for each of the transformations. The issue is that visual studio differs from SQL server in terms of MDX format so I struggle to create a code that will work properly. This one perfectly works in SQL Server but doesn't work in SSAS:

Select [Measures] . [Income] on columns , TopCount ([Dim Customers].[Customer Id] . children,10 ) on rows from [CUBE FINAL] I tried to rewrite it: TopCount([Dim Customers].[Customer Id].Members,10,[Measures].[Income])

Though it is processed normally, id doesn't work and gives me 'null' in report.

Where is the mistake? Thank you!!

1
visual-studiossasmdx
Can you describe more precisely what you mean by "works in SQL Server but doesn't work in SSAS"? What do you think the difference is between SQL Server and SSAS? – Tab Alleman
Sorry fro being not clear. I am trying to do the calculations in Visual Styudio in Cube. It doesn't display the resultsthere, also in Visual studio (in Cube-Calculation) it doesn't let me use "select" or "on rows/on columns" etc. I understand that my question sounds stupid to you. I have zero experience in MDX. – Julia Gumina
I really doubt that you can do a TOPCOUNT in a cube calculation like this. Is there some reason you want to do that instead of making it part of the MDX in the report? – Tab Alleman
Yep... the requirements of the final project at studies...... Thank you! – Julia Gumina

1 Answers

0
votes

Cube Calculations are for producing calculated Measures. They can't be used to control the number of rows returned, so I don't think you can use TOPCOUNT() in a Cube Calculation to do what you want.

A calculation could possibly be used to Aggregate the income of the top 10 customers, but it wouldn't tell you who those customers were, nor would it limit the rows returned to only those customers.