I'm trying to select the top 10 of a specified data set for use in a report. However, I'm not sure where it would go in the query. My current MDX query is below and mostly resembles what is automatically generated by the designer. What I'm trying to get is the top 10 Subcontractors by the value of Revised Value. Currently when I try to run this I get the error "Error running the data source query"
SELECT
TopCount([Dim Subcontractor].[Subcontractor Name].[Subcontractor Name].ALLMEMBERS, 10, [Measures].[Revised Value]),
{ [Dim Subcontractor].[Subcontractor Name].[Subcontractor Name].ALLMEMBERS }
ON COLUMNS,
{ [Measures].[Revised Value] }
ON ROWS
FROM [BGDEMO]
WHERE ( [Dim Project].[Project Name].DEFAULTMEMBER, [Dim Date].[Full Date].DEFAULTMEMBER )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR
Any help with this is appreciated.
Figured it out. I'll post it here in case others might find it useful:
SELECT
{ TOPCOUNT([Dim Subcontractor].[Subcontractor Name].[Subcontractor Name],10, [Measures].[Revised Value]) }
ON COLUMNS,
{ [Measures].[Revised Value] }
ON ROWS
FROM [BGDEMO]
WHERE ( [Dim Project].[Project Name].DEFAULTMEMBER, [Dim Date].[Full Date].DEFAULTMEMBER )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR