MDX query

I'm building a report from SSAS 2005 with three parameters.

start date

end date

project stream

I would filter only the non empty project stream in the actual cost fact table included in the time interval specified with the first two paramenters.

SSRS automatically compose an MDX query that I try to modify to get what I want:

WITHMEMBER [Measures].[ParameterCaption] AS'[PROJECT].[STREAM].CURRENTMEMBER.MEMBER_CAPTION'MEMBER [Measures].[ParameterValue] AS'[PROJECT].[STREAM].CURRENTMEMBER.UNIQUENAME'MEMBER [Measures].[ParameterLevel] AS'[PROJECT].[STREAM].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel], [Measures].[EUR] } ONCOLUMNS ,

NONEMPTY [PROJECT].[STREAM].ALLMEMBERSONROWS

FROM [PMO_MDB]

WHERE { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END)}

In this way I get all project streams and I'm not able to filter away the empty cell (using the [Measures].[EUR] defined in the actual cost fact table). In fact SSRS put some intrisict member properties on columns as calculated measure !

There is another way to extract the same info but filtering away the empty cell ?

Cosimo

Take a look at the EXISTING function. I believe you could use it on your row axis to limit Project.Stream members to those associated with a fact entry.

Bryan

|||

Thanks Brjan ... I tried but I get the same result also using EXISTING !

I think the problem is that SSRS put on columns as a calculated measure some properties.

There is another way to extract such properies ?

I try the dimension properties clause ... but nothing happen !

Cosimo

|||

Sorry. I meant the EXISTS function. (The names are so similar I always screw them up.) Here is an example of what I'm describing:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

EXISTS(

[Product].[SubCategory].Members,

{ STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")},

'Reseller Sales'

) on 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

For comparison:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

[Product].[SubCategory].Memberson 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

Sorry for the confusion,

Bryan