Ever wondered how to create an Excel list report over a TM1 cube that dynamically expands based on filters so you always get all relevant rows? Well you can, by using what’s called a Dynamic Report (previously called Active Forms).
What is a TM1 Dynamic Report?
These are a way of presenting a dynamically expanding list report in Excel using TM1 data as the source. They are live using TM1 data and can be used for write back. They also allow the user to expand and collapse consolidations on rows. Very nifty!
Example of an Dynamic Report
You might want to present a report that lists Products sold in a specific Region Last Month. You could create a zero suppressed Dynamic Report that lists Products as rows, have Revenue, Gross Margin $, Gross Margin % and Quantity Sold as columns and leave Time, Region, Version etc as context filters. Then as you change the filter for Region, Time or Version, the list will dynamically update, showing only the Products that satisfied the filter.
Creation of an Dynamic Report
To create the report specified above, simply create a TM1 Perspectives Cube Viewer view that has Product as rows, Measures as columns and leave Time, Version and Region in the filter area. I then have Zero Suppression enabled. The view looks like this.
Then simply click the Dynamic Report button in the Perspectives Cube Viewer toolbar. Note that it is not available in the Architect Cube Viewer. So if you’re in Architect, save the view, close Architect and re-open the view in Perspectives. The Dynamic Report toolbar icon looks like a report with a small flash of lightening striking through it.
This will then dump the view out to Excel and create a Dynamic Report that looks like this:
A few things to note here:
- This is an identical layout to the View, so it is important the starting View is the layout you need.
- The three labels at the top (World, Last Month and Actual) can be double clicked to change the filter.
- The plus signs on the rows can be expanded or contracted as desired.
Finally, here is a version where the Region has been changed from “World” to “Denmark”, the row “5 Series Wagon” has been expanded and because zero suppression is enabled, “L Series Wagon” has been removed automatically.
