Create a table from a MDX statements

MDX is a powerful query language similar in structure of standard SQL. Where it differs is it ability to deal with multidimensional data as opposed to the flat structure of a table in a relational database.

Canvas has many helpful components that enable you to avoid the complexity of writing MDX statements but if you want to dive deeper into the internals of TM1 you can still do this via Named MDX statements.

Named MDX statements are created on the server so you only have to pass through parameters from JavaScript. It also adds an important layer of security, it is generally bad practice to allow SQL or MDX to be created/modified in a user interface as it allows users or third parties the ability to change what is executed. This could include changing a SELECT statement to an UPDATE or DELETE command.

Named MDX statement structure

Named MDX statements are stored in a JSON file on your Canvas server. The file is located at:

  • <Canvas Install Location>\<Application>\WEB-INF\resources\mdx_named.json
  • Example: C:\CWAS\webapps\ROOT\WEB-INF\resources\mdx_named.json

The file contains an array of named mdx statements in JSON format:

[
    {
    "id": "P&L",
    "mdx": [
      "SELECT NON EMPTY {[Period].[Oct], [Period].[Oct YTD], [Period].[Year]} * {[Version].[Actual], [Version].[Budget]} ON COLUMNS,",
      "NON EMPTY {[Account].AllMembers} * {[Department].AllMembers} ON ROWS",
      "FROM [General Ledger]",
      "WHERE ([Year].&[{{Year}}], [Region].&[{{Region}}], [General Ledger Measure].&[Amount], [Currency].&[Local])" 
    ],
    "mdxParams": [
        { "name":"Year", "value":"2012" },
        { "name":"Region", "value":"3", "escaped": true }
    ]
]

The file has the following properties:

  • id (string): This is a unique id used to identify the statement you want to execute.
  • mdx (array of strings): This is the MDX statement itself, instead of a single string it is an array of strings to make it more readable. Note the use of [ ] square brackets around each of the strings.
  • mdxParams (array of parameters):
    • name (string): The name of the parameter used in the MDX statement. In the MDX statement the parameter should be used like this {{ParameterName}}.
    • value (string): The default value of the parameter, if the parameter is not passed to when executing the MDX the default value will be used.
    • escaped (optional boolean): Whether to escape the any closing square brackets ( ] ) in the parameter values. The default value is true.

include NAMED MDX STATEMENT in a HTML page

The easiest way to show data  from a MDX statement into a Canvas page is to use the Canvas directive tm1-ui-table-mdx:

<tm1-ui-table-mdx
   tm1-instance="Instance Name"
   tm1-cube="Cube Name"
   tm1-mdx-id="MDX ID"
   >
</tm1-ui-table-mdx>

tm1-ui-table-mdx is going to manage how the data will be displayed depending on the MDX statement. If two dimensions are on rows, the directive will merge the cells to avoid elements to be replicated many times. 

An example of the tm1-ui-table-mdx can be found in Canvas components called MDX Table:

USE TM1-UI-TABLE-MDX TO CREATE THE TABLE

Let's build a new tm1-ui-table-mdx using the P&L MDX statement already written in mdx_named.json.

In a new page, add the following HTML code:

<tm1-ui-table-mdx
   tm1-instance="dev"
   tm1-cube="General Ledger"
   tm1-mdx-id="P&L"
   >
</tm1-ui-table-mdx>

If you save your HTML file and refresh the Canvas page, you should see the table as below:

Use tm1-dimension-attribute to show dimensions alias

To show alias instead of the elements id for dimensions on rows and columns, we need to add the parameter tm1-dimension-attribute="{<dimension name>:<attribute name>"}:

<tm1-ui-table-mdx
   tm1-instance="dev"
   tm1-cube="General Ledger"
   tm1-mdx-id="P&L"
   tm1-dimension-attribute="{Account:'Description', Department:'Description', Period: 'Short Description', Version: 'Description'}"
   >
</tm1-ui-table-mdx>

Save and refresh the page, you should see now the aliases on rows and dimensions:

Dynamic MDX statement

Currently the structure of our MDX statement is static, one way to show data from a different year is to go to the mdx_named.json file and update the MDX statement. The P&L MDX statement has 2 parameters Year and Region. Let's replace 2012 with 2011:

Save the mdx-named.json and refresh the Canvas page, you should see now 2011 values:

Another way to update these parameters is to add tm1-mdx-parameters="{Parameter Name:Parameter Value}" to the tm1-ui-table-mdx. Using the tm1-mdx-parameters will enable you to pass the parameters value from the HTML page to the JSON file where the MDX query is defined:

<tm1-ui-table-mdx
   tm1-instance="dev"
   tm1-cube="General Ledger"
   tm1-mdx-id="P&L"
   tm1-dimension-attribute="{Account:'Description', Department:'Description', Period: 'Short Description', Version: 'Description'}"
   tm1-mdx-parameters="{Year:'2011', Region:'England'}">
</tm1-ui-table-mdx>

Customize the tm1-ui-table-mdx

To customize the look of the tm1-ui-table-mdx such as removing the pagination, you'll need to update the code behind the tm1-ui-table-mdx. You can find an example in the samples called Named MDX:

You can copy the HTML code behind this table and start customizing the table. For instance to remove the pagination, you'll need to delete the red part below: