Forum Discussion
Adding date table to mdx 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])
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.
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.
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.
Excel pivot insists to use "Hierarchize" which doesn't work in PBI. And so far I have failed to add it to my original code. Can't yet figure out how to use CrossJoin in my code either since the first row with ON COLUMNS already has 2 values, adding a third doesn't work.
SELECT NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[Project POS].[Type hierarchy].[All types]},,,INCLUDE_CALC_MEMBERS)}), Hierarchize({DrilldownLevel({[Time].[Time].[All periods]},,,INCLUDE_CALC_MEMBERS)})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Project POS].[POS hierarchy].[All POS]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM (SELECT ({[Time].[Time].[Calendar Year].&[2020]}) ON COLUMNS FROM [Property Cube]) WHERE ([Measures].[Count of Receipts]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGSHi 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])