Cascade reports are multiple reports that show a standard template from different points of view. This post will show how to create a Dodeca Cascade Report using an MDX script to choose the intersections.
I started with a template that shows scenarios and a single roll-up account: EBITDA. This account will be automatically zoomed-in to create our final report. Using a roll-up will ensure that any account changes are reflected in our report without needing future maintenance.
I also added tokens for Periods, Location, Departments and Years. I added an Excel formula using simple subtraction to find the previous year.
I added two named ranges. One for the Essbase retrieve and another for the cell on which the zoom-in will be performed.
I hid the header rows and saved the template.
Next I created an Essbase MDX Script called PaloMartCascade.
I used the Crossjoin() function to create my column headers of Periods and Scenarios.
I also used the Crossjoin() function to pull all level zero members under the user selected Department and Location.
Note the use of the NON EMPTY keyword here. Using this will ensure that no sheet will be generated for any missing or irrelevant intersections.
Next I set my view properties, specifically for CascadeSources.
In the Cascade Sources dialog I changed the Source Type to EssbaseMdxScript. Note that this feature is only available in version 7.6.1 and beyond.
I then selected the Script I created in the previous step.
I also set the tokens that will be used to generate the Cascade Report. In this case, the MDX report will return Departments in the first column and Locations in the second column. The order that the tokens are defined should match the order in which they are retrieved in the MDX report.
Finally, I added a bit of Workbook Scripting.
First I added a property called SheetCount and set the Value to 0. This will track how many sheets get built during the Cascade.
Next I added an EventLink that will be triggered by the AfterCascadeSheetBuild. This will be fired after each individual sheet is built.
The first method in this procedure is AddProperty. This method uses the @PVal() Dodeca function to get the current SheetCount property and increment it.
The next method in the OnAfterCascadeSheetBuild Procedure is EssbaseZoomIn. This is the method that will drill in on the EBITDA account on our template. Note how I reference the zoom.cell in the SelectedRange. If we move the cell at some point we won't have to update this Workbook Script.
The next procedure I added was OnAfterBuild. This will be used to show a message box to the user. I use the @PVAL() Dodeca function to get the final SheetCount property.
Then I used the AddProperty method to set the SheetCount back to zero. This will ensure that the property does not keep adding to previous cascades if the view is built again.
There you have it. Creating Cascade Reports in fast, flexible and powerful in Dodeca. From here you could add a Workbook Script to save the sheets to individual files or send them in emails.
Post a Comment