Forum Discussion
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.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
- mwegenerMost 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.
- AnonymousNot 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.
- mwegenerMost 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.