Universal Reports

You can use Universal Reports to create hierarchy aware and parameterized reports, which can dynamically execute formulas and queries against the TM1 Server.

Important: Before you use Universal Reports, review the prerequisites to make sure you have the required components and services.

Driven by TM1SET formulas, a Universal Report is a hierarchy aware and parameterized report type that achieves its parameterization through the use of named ranges and named range schemas. You can make really powerful changes to a Universal Report by directly editing the query parameterization cells.

Features supported in Universal Reports allow you to build complex and sophisticated reports. You can add multiple Universal Reports to a single sheet or stack multiple dimensions on rows and columns. Unlike a Dynamic Report, which can only insert cells as new rows in your report, a Universal Report supports cell-based insertion. Cell-based insertion allows rows and columns to be toggled to expand and collapse even if there are multiple Universal Reports on a sheet.

Formulas in a Universal Report recognize not only a single member, but also the member's position in a hierarchy of data. Universal Report formulas recognize leaf members, a single reference to the member itself, and references to the entire tree below a member.

You can also use formulas in a Universal Report to read from and write to cells. A Universal Report's layout places key data into known specific cells, such as named ranges.

A Universal Report can be executed against the TM1 Server or Planning Analytics Engine to retrieve updated data.

Additionally, you can manipulate data in a Universal Report to change the scope of the data that is being viewed or to modify the appearance of the report. You can apply Excel's conditional formatting to further customize a Universal Report.

When you're finished, a Universal Report can be published to the TM1 Server Application folder and opened as a live websheet in TM1 Web or Planning Analytics Workspace websheets.

Note: For a view to work in a Universal Report, the dimension attribute names must follow these guidelines:
  • The name must not be longer than 128 characters.
  • The name must start with either an alphabetical character, a unicode character from the categories L or Nl, or an underscore.
  • The name must only contain alphanumeric characters, unicode characters from the category L, Nl, Nd, Mn, Mc, Pc, Cf, or an underscore.

    For more information, see TM1 object naming conventions.

Universal Report structure

A Universal Report is structured into three key areas:

Universal Report

  1. Query parameterization cells

    The query parameterization cells are hidden by default in a Universal Report that is created from an existing view or Exploration View. You can unhide the query parameterization area to see the cells included in the properties and the query named ranges that drive the report.

  2. Context area

    The context area displays hierarchy information for members used in the report. The context area cells are defined by TM1SET formulas. You can access a different view of the data by changing a member in the context area by simply clicking a member and selecting a different one from the list. You can also edit the TM1SET formula directly in the cell.

    The context area also includes the Zero Suppression feature. You can apply zero suppression to rows and columns in a Universal Report.

  3. Data cells

    The data cells are driven by TM1SET formulas and named ranges in the query parameterization area that map to the row, column, and data cells.