i have a query that runs just fine, returning the turnover per sector, where the sector has non-null turnover.
I'd like to add ranking into the query, but when I do, I now get ALL sectors, with those sectors having null turnover all ranking equal bottom.
This kinda makes sense, but essentially I'd like to exclude the null turnover ones. I've tried various combinations of nonempty, NON EMPTY and Exists without success. What's the answer to this, and more importantly, how should I be thinking this through to get the right answer myself?
1. First query: i have defined the ranking measure, but not used it. I get only non empty sectors.
WITH
member measures.[turnover rank] as rank([SECTOR3].[SECTOR3 ID].CurrentMember, [SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS)
SELECT {[Measures].[TURNOVER]} on columns,
nonempty {[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS} ONROWS
FROM [WmCube4]
where [TIME].[Date].&[2007-06-04T00:00:00]
2. Second query: i include the ranking measure in the output - now null records appear.
WITH
member measures.[turnover rank] as rank([SECTOR3].[SECTOR3 ID].CurrentMember, [SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS)
SELECT {[Measures].[TURNOVER],measures.[turnover rank]} on columns,
nonempty {[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS} ONROWS
FROM [WmCube4]
where [TIME].[Date].&[2007-06-04T00:00:00]
Not sure how ranking could occur without using a 3rd parameter for Rank() - but you can return null sectors with empty turnover, like:
member measures.[turnover rank] as
iif(IsEmpty([Measures].[TURNOVER]), Null,
rank([SECTOR3].[SECTOR3 ID].CurrentMember,
[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,
[Measures].[TURNOVER]))
|||works perfectly, thanks!
