Tuesday, March 20, 2012

Nonempty Problem

following query is nt working properly. need a solution (need to filter the non empty rows)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
non empty CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]NonEmpty({filter(CROSSJOIN([CustomTimeSet],[GeneralLedgerSet]),[Measures].[MdaCodeTotal] <> 0 )})on rows

I am a bit suspicious about the [Measures].[Description] measure. Is this a calculated measure? It could be what is causing the non empty clause not to work. If you are using SSAS 2005, something like the following might work:

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
nonempty( CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) , {Measures.MdaCodeTotal})on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]

Note I am using the second parameter in the NonEmpty() function to set the measure context for the non empty evaluation.

No comments:

Post a Comment