Click here to Skip to main content
15,443,549 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm writing an MDX query for SQL server 2008 and are trying to look at the data from a crossjoin of 2 dimensions. There is probably too much data to display the result so I just want to return the top 10 results. How can i do this?

Current Query

SQL
select {[Measures].[Measure1], [Measures].[Measure1], [Measures].[Measure1]} on columns, 
crossjoin([Dimension].[Dimension].[Dimension].&[123], [Dimension2].[Dimension2].[Dimension2])  on rows
from [Cube name]


What Should my query look like?
Posted
Updated 19-Jul-16 11:14am
Comments
Herman<T>.Instance 15-Jan-16 9:45am    
Isn't it so that it has to create the cross join first before it can determine the 1st 10 rows?

1 solution

Use the TopCount()[^] function. Remember, the set must already be ordered using the Order (MDX)[^] function.



SQL
select 
{
    [Measures].[Measure1], [Measures].[Measure1]
    , [Measures].[Measure1]
} 
on columns,
topcount(
    crossjoin(
        [Dimension].[Dimension].[Dimension].&[123]
        , [Dimension2].[Dimension2].[Dimension2]
    )
,10
)
on rows
from [Cube name]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900