JoGo


apologies for this basic question, my MDX book hasn't arrived in mail yet.

i have the following query which defines the dataset used for a report parameter. The MDX was autogenerated when I specified a parameter for my main dataaset.. This is the auto generated MDX:

WITH

MEMBER [Measures].[ParameterCaption] AS '[Time].[Year - Quarter - Month - Date].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Time].[Year - Quarter - Month - Date].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Time].[Year - Quarter - Month - Date].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

[Time].[Year - Quarter - Month - Date].[Date].ALLMEMBERS ON ROWS FROM [Itdev1 Hk]

Now, I'd liek to tweak it so that only those dates which actually have turnover data are shown. My turnover field is called [MEASURES].[TURNOVER].

how do I layer that in

i've tried
SELECT non empty {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel], [Measures].[TURNOVER - WM INTERDAY]} ON COLUMNS ,
and
SELECT non empty {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel], nonEmpty([Measures].[TURNOVER - WM INTERDAY])} ON COLUMNS ,

but in both cases I get ALL dates, most with null turnover values. I also tried using Filter() adn a where clause.




Re: floundering in half an inch of water - tweak an mdx recordset to show only those dates that actually have values.

Deepak Puri


You could try filtering out empty dates on rows using NonEmpty():

WITH

MEMBER [Measures].[ParameterCaption] AS '[Time].[Year - Quarter - Month - Date].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Time].[Year - Quarter - Month - Date].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Time].[Year - Quarter - Month - Date].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

NonEmpty([Time].[Year - Quarter - Month - Date].[Date].ALLMEMBERS, {[MEASURES].[TURNOVER]}) ON ROWS

FROM [Itdev1 Hk]







Re: floundering in half an inch of water - tweak an mdx recordset to show only those dates that actually have values.

JoGo

Saved me from drowning Smile