TM1 Perspectives (for Excel) Report Methods

Posted on August 13, 2011 by Ben Hill

Here is another minor extract from TM1 Tutorials.com training material. We talk through the common sustainable reporting methodologies within TM1 Perspectives for Microsoft Excel. These being:

Static/Standard Reporting
Active Form Reports
Filter Reporting
MDX Reporting
VUSLICE Reporting

This isn’t an exhaustive list, but we believe these are reporting styles TM1 Consultants should at the very lease know of.
The common methodologies for reporting from TM1 Servers within Microsoft Excel:

Name	Description	Usage Example
Standard	The default TM1 in Excel Reporting Method. No items on the report change apart from the report parameters (SUBNM’s) and the resulting data cells (DBRW’s).	Any basic report which doesn’t need zero suppression of report elements can be made using this method. This is not however very ideal for detailed Profit & Loss or Trial Balance Reports as these may display different accounts based on the chosen Department.
Active Form	The IBM Cognos Approved method for Dynamic Reporting. Active Forms were introduced in version 9.4 of TM1 and allow Level Based Formatting, Expression based Subsets and Double Click Element Expansion.	Zero Suppressed dynamic reporting strictly based on Dimension Element Structures. We recommend this format of reports when you require double click Element expansion. Be sure to add a “Refresh” button/textbox to the report and assign the TM1 Macro “TM1RebuildCurrentSheet” to it as some versions have bugs when refreshing using the “F9” Key (This isn’t a problem with TM1Web).
Filter Report	This reporting method is based on a Standard TM1 Report but with an Excel Filter applied (possibly on a hidden column). A little macro is assigned to a “Refresh” button which will remove the filter, refresh the data then add the filter again. Note: This report style doesn’t allow double click expansion.	This is great for custom filtered groupings. Blocks of reports can be grouped under each other and use the same filter. Reports are chart safe as rows aren’t ever deleted (as in Active Forms), they are just hidden. Combining this style with Selectable Group Elements) (return children via ELCOMP) or Subsets (return members via SUBNM) adds a lot of additional functionality. Ideal for Budget entry reports, Profit & Loss, Balance Sheet etc.
MDX Report	This is a programmatic solution in VBA (Macros) where TM1 is queried using the Multidimensional Query Language (MDX). The results of the query are output to the worksheet (Read-Only Report) or sometimes are used to write DBRW Formulas which provides a Write-Back enabled suppressed report.	This is typically used for large reports which required suppression of over a thousand or more elements. An example where this technique is recommended would be a Salaries & Wages Employee Report for a selectable department within a large enterprise (1000+ Employees). This kind of report requires an advanced TM1 Specialist to build but is very efficient and performs well.
VUSLICE	This is also a programmatic solution which utilizes a predefined cube view and the TM1 “VUSLICE” macro function. The result isn’t formatted nicely but can be via VBA Coding.	This kind of Report is used less and less as Active Forms and Filtered Reports can do a better job and are much easier to create. Additional Information can be found in the TM1 Reference Guide Document provided by IBM Cognos.

This is probably good ground to expand and add articles to TM1 Tutorials with, How to build reports using each of these styles. Please let me know via comments if I have missed any Reporting methodologies when using IBM Cognos TM1 within Microsoft Excel.