MDX Duplicates and Values

Question

MDX Duplicates and Values

wesley.boyd

SSC-Addicted

Points: 437
More actions
February 15, 2017 at 4:46 am

#146339

We havea FACT of Purchase orders linked to a date dimension [Date - PO Orders]
It hasmembers Calendar Year (Jan-Dec)
                       Contract Year (Nov-Oct)
                       Financial Year (Apr-Mar)
There is anSSRS report which originally had a Calendar view which is no problem but nowthey want to change the view for either the of the above dimension members.
I created atest hierarchy called [SSRS] and when you group them on PO Count they have duplicatessince there are overlaps in granularity.
My issue isthe MDX which brings back the data set contains the duplicates and doesn'tbring back the member value as I would expect.
WITH MEMBER[Measures].[ParameterCaption] AS [Date - POOrders].[SSRS].CURRENTMEMBER.MEMBER_CAPTION
        MEMBER [Measures].[ParameterValue] AS[Date - PO Orders].[SSRS].CurrentMEMBER.LEVEL.UniqueName
           MEMBER [Measures].[ParameterLevel] AS [Date- PO Orders].[SSRS].CURRENTMEMBER.LEVEL.ORDINAL
           MEMBER [Measures].[ParameterFilter] AS[Date - PO Orders].[SSRS].CURRENTMEMBER.LEVEL.NAME
SELECT NON EMPTY DISTINCT{[Measures].[ParameterCaption],[Measures].[ParameterValue],[Measures].[ParameterFilter],[Measures].[ParameterLevel]}ON COLUMNS ,
NON EMPTY
DISTINCT {[Date - POOrders].[SSRS].[FinancialYear].ALLMEMBERS,
       [Date - POOrders].[SSRS].[ContractYear].ALLMEMBERS,
       [Date - POOrders].[SSRS].[CalendarYear].ALLMEMBERS
       }ON ROWS
FROM [Model]
Returns
How do I getthe ParameterValue to return the correct year/period? The report works as itshould as I can filter out the dates on in the report with the ParameterFilter.I just need the correct ParameterValue and removal of the duplicates.
I apologisefor the bad MDX by the way.
All help isgreatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19116 More actions · Answer 1

As you stated above, the query will return duplicates because of the overlaps in granularity. I'd recommend one of the following approaches:

Build the MDX dynamically based on the user selection, assuming they only select one of the Date hierarchies in the report.
Execute 3 separate MDX queries (one for each Date hierarchy) and combine the results with a union.

wesley.boyd SSC-Addicted Points: 437 More actions · Answer 2

How do you do the latter as i thought they all had to same dimenensionality?

Martin Schoombee SSCoach Points: 19116 More actions · Answer 3

You could create calculated members for all of the dimensional attributes, or perform the union in SSRS (assuming that is what your reporting platform is).