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 an Active Form.
What is a TM1 Active Form?
TM1 Active Forms 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 Active Form
You might want to present a report that lists Products sold in a specific Region Last Month. You could create a zero suppressed Active Form 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 Active Form
To create the Active Form 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 Active Form 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 Active Form 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 an Active Form 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 of the Active Form 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.