How to Create a List Report using a TM1 Active Form

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.

How to Determine the TM1 Perspectives Version Installed

Finding out the version of TM1 Perspectives is easy. TM1 Perspectives is an Excel Addin that gives great functionality for using TM1 information in Excel to create forms and reports that refresh automatically with the latest data from TM1.

Here is what you need to do to determine what version of Perspectives you have installed:

  1. Navigate to the Program Files/TM1/bin folder.
  2. Find the file tm1prc.dll (for Perspectives).
  3. Right click on tm1prc.dll and choose Properties.
  4. From the Version tab check the Product Version.

If you need to know the version of TM1 installed on a server, please follow this link.

Or, if you need to know how to use the “cmplst” file, take a read of this post.

TM1 10.1 – The new look for Perspectives

One of the first questions I’m ever asked about a new release of any kind of software is “How does it look?”. And since TM1 10.1 introduces a new icon set across Architect and Perspectives I thought I’d add in a quick little gallery. Click the images below to see the different screens of TM1 Perspectives 10.1.

Please note that Turbo Integrator and the Advanced Rules editor both haven’t changed when accessed through Perspectives.