Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Adding date table to mdx query

Hello

 

In my last post I asked how to correctly pull data using MDX filtering and got everything working.
Code that helped me filter out and format it just how I needed:

SELECT
NON EMPTY {[Project POS].[Type hierarchy].[WinPOS], [Project POS].[Type hierarchy].[SelfCheckout]} ON COLUMNS,
NON EMPTY {[Project POS].[POS hierarchy].[Project]} ON ROWS
FROM [Property Cube]
WHERE ([Time].[Time].[Calendar Year].&[2020],[Measures].[Count of Receipts])

So now this MDX is nicely pulling the correct data from year 2020. Now I need to add a date column/row so I could add a filter to my report. I need to be able to filter between January, February, March etc. With this code atm I can only show year 2020 data without the option to choose what month,day. 
In this cube I have a Date - Time table which I'd need to add to my previous code somehow. 
When I try to add it as a new line:

SELECT
NON EMPTY {[Project POS].[Type hierarchy].[WinPOS], [Project POS].[Type hierarchy].[SelfCheckout]} ON COLUMNS,
NON EMPTY {[Project POS].[POS hierarchy].[Project]} ON ROWS,
NON EMPTY {[Time].[Time].[Calendar Year].&[2020]} ON COLUMNS
FROM [Property Cube]
WHERE ([Time].[Time].[Calendar Year].&[2020],[Measures].[Count of Receipts])

I get error 

An axis number cannot be repeated in a query.
  • mwegener's avatar
    mwegener
    6 years ago

    Hi Anonymous ,

     

    try this ...

    SELECT
    NON EMPTY {({[Project POS].[Type hierarchy].[WinPOS], [Project POS].[Type hierarchy].[SelfCheckout]} * [Time].[Time].[Calendar Year].&[2020])} ON COLUMNS,
    NON EMPTY {[Project POS].[POS hierarchy].[Project]} ON ROWS
    FROM 
       (SELECT ({[Time].[Time].[Calendar Year].&[2020]}) ON COLUMNS  
        FROM [Property Cube]) 
    WHERE ([Measures].[Count of Receipts])

     

6 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional

    Hi Anonymous ,

     

    take a look at this Excel add-in.

    https://archive.codeplex.com/?p=olappivottableextend

     

    If a PivotTable is performing poorly or returning incorrect numbers, it may be necessary for the Analysis Services administrator to troubleshoot the MDX query which the PivotTable is using. The MDX tab of the OLAP PivotTable Extensions dialog shows you this MDX.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi

       

      The data is not incorrect, I already had this extension installed. I need to figure out a way how to add date to my code. The date and data in the cube are linked, in cube I can see what data was created on a specific date but when I pull the entire 2020 data into PBI the link between date and data will dissapear. I can always create a new date table in PBI but this way I can not see which data was updated on what date so thats why I need to include the date table to my code from cube.

      • mwegener's avatar
        mwegener
        Most Valuable Professional

        Hi Anonymous ,

        the idea was that you set up your required query in an Excel pivot table and then copy the resulting query from this extension view.