sharepointmdxperformancepoint

Using TopCount in MDX query


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.


Solution

  • 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