Custom MDX Query for Excel Filters
More Ooten we may want to write custom Filters for Cube Data retrieved through Excel. You can achieve it through VBA or simply by adding few iterations on Excel. We can Achieve Custom calculations by simply adding OLAP PIVOT TABLE Extension to EXCEL .
A common scenario would be building a 13-week rolling window for Pivot report on Excel.
- Bring in your regular data through Excel and click on the Pivot area, In the options Pane select Fields, Items & Sets and select New/Manage Sets.
2. Now click create sets using MDX and then Paste the code for the rolling window.
3. Sample Code for rolling Window
ClosingPeriod
(
[By Fiscal Calendar].[FP Week Of FP Month].[FP Week Of FP Month]
,[By Fiscal Calendar].[FP Week Of FP Month].CURRENTMEMBER
).Lag(NO of weeks to lag)
:
ClosingPeriod
(
[By Fiscal Calendar].[FP Week Of FP Month].[FP Week Of FP Month]
,[By Fiscal Calendar].[FP Week Of FP Month].CURRENTMEMBER
)
Give a Name for the "SET" and Save.
You will now Find the Set under your time dimension and select to use it for your pivot report.
You can repeat the same for other dimensions too. We can use Multiple Filters on the same Excel Book. The only limitation on this exercise is the whole code will reside on the Excel workbook only.