Friday, March 9, 2012

MDX range not working

I've come across an issue with MDX with regards to selecting a range of consolidation units. On my MDX statement,

Code:
SELECT {[Measures].[0CS_TRN_LC], [Measures].[0CS_TRN_QTY]
} ON COLUMNS, NON EMPTY {CROSSJOIN(
{
[ZCS_UNIT].[DPCO991-CONSCORR]:[ZCS_UNIT].[DPCO991-99001000]
},
[0CS_ITEM].[All]:[0CS_ITEM].
)}

If the first entry in ZCS_UNIT is valid and contains data in it, my mdx statement retrieves the data accordingly. However, if the first entry is valid and does not contain any data, I do not retrieve any data at all (even if the other cons unit in the range contain data) ! Since the users put in the range of cons unit in the selection screen, there is no way to validate if the first cons unit has data or not. I tried removing the "NON EMPTY" before cross join but nothing changed.

Seems that the first entry is critical, as it has to be both valid and contains data for me to be able to retrieve anything.

This is my first MDX report (no formal training), I am writing this report for the BW system in SAP.

Is this an MDX/SAP bug, or is there something wrong with my MDX statement contruction?

Thanks.

I would advise asking your question in the SQL Server Analysis Services forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1 as this specific forum is targeted at reporting in the context of Team Foundation Server

Thanks,

Othmane

No comments:

Post a Comment