Create a Universal Report from scratch

You can build a Universal Report from scratch by defining the report properties and query named ranges.

1. To define the properties of a Universal Report:
   1. Add the URL of the Datasource that the Universal Report will run against. You can also use the TM1PRIMARYDATASOURCE() formula to get the URL of the first data source you logged into.
   2. Add the Server name or use TM1PRIMARYDBNAME() formula to get the name of the first database you were authenticated through.
   3. Define the Display Toggle property by using boolean values TRUE or FALSE.
   4. Define the Indents Per Level property by using an integer value. For example, 1 indicates the space between the cell margin and the member name in consolidated cells.
   5. Define Active Display by specifying each hierarchy that is represented in the query and their display alias attribute. Here is an example:

      “{ "[plan_business_unit].[plan_business_unit]" : "BusinessUnit", "[plan_chart_of_accounts].[plan_chart_of_accounts]" : "AccountName", "[plan_department].[plan_department]" : "Department", "[plan_time].[plan_time]" : "Time", "[plan_version].[plan_version]" : "VersionName"}”

   6. Define Expand Aboves by specifying each hierarchy that is represented in the query and how you want to expand each one. Here is an example:

      “{ "[plan_business_unit].[plan_business_unit]" : "False", "[plan_chart_of_accounts].[plan_chart_of_accounts]" : "True", "[plan_department].[plan_department]" : "False", "[plan_exchange_rates].[plan_exchange_rates]" : "False", "[plan_source].[plan_source]" : "False", "[plan_time].[plan_time]" : "False", "[plan_version].[plan_version]" : "False"}”

   
   
   
2. To define and apply the properties named range (p) to the properties, select all six properties that you just added and go to Formulas.
3. Click Define Name and enter the following:
   1. Name: tm2\\_0_p (where 0 is the ID associated with the Universal Report and p is the named range ID)
   2. Scope: Select the sheet where the cell is located
   3. Refers to: Enter the cell reference

   
4. Create a Query cell with a valid MDX expression.
   You can get a valid MDX expression by using the MDX viewer for an existing Exploration View, or creating one by hand. If the query is too long to fit in a single cell, you can use Excel’s CONCATENATE() formula or use accessory formulas such as MakeQuery() to define a query string from sub components.
5. To define and apply the query named range (q) to the Query cell, select the cell and go to Formulas.
6. Click Define Name and enter the following information:
   1. Name: tm2\\_0_q ((where 0 is the ID associated with the Universal Report and q is the named range ID)
   2. Scope: Select the sheet where the cell is located
   3. Refers to: Enter the cell reference

Results

The Name Box field displays the named ranges that you created and applied.

You can also see the Universal Report that you just created under the Universal Reports folder on the Workbook tab in the Task Pane.

Tip: If the Universal Report doesn't appear in that folder, try saving the workbook and reopen it in Excel again. Refreshing the sheet allows Planning Analytics for Excel to execute the query against TM1 Web's EvaluationService and return the data and axis information.