Transform your cell set into a structured format

Canvas comes with lots of directives which are ready to use. For example with tm1-ui-dbr you can quickly request a data from TM1 and display it in your page. With these directives you can already achieve a lot but in certain case where you want to do more customization, you can use Canvas services such as cubeExecuteMdx to request data from TM1 and then transform it into your controller:

1. Request data to TM1 from a MDX query.
2. TM1 will send you back an object with OData structure.
3. Transform this object so it can match your HTML component structure.
4. Finally display this object into your HTML page.

The tricky part in this process is the data transformation. Good thing Canvas comes with a service named resultsetTransform which takes your OData object and then restructure it and add more properties such as pagination which you can then use in your table.

An example of this service can be found in the samples application (/sample/component/mdx-chart). This article explains in details how this sample works.

The first step to request data from TM1 server is to use an MDX statement. On this example we used the following query:

SELECT NON EMPTY {[Period].[Jan], [Period].[Feb], [Period].[Mar]} ON COLUMNS, NON EMPTY {[Account].AllMembers} ON ROWS FROM [General Ledger] WHERE([Version].[Actual], [General Ledger Measure].[Amount], [Currency].[Local], [Region].[1], [Department].[1], [Year].[2012])

This query will give us a set of data coming from the General Ledger cube with three columns (Jan, Feb and Mar) for all accounts ([Account].AllMembers) on rows.

To retrieve the cell set from TM1, we use the Canvas service cubeExecuteMdx, in the controller (mdx-chart.js), you can see the following code:

$tm1Ui.cubeExecuteMdx($scope.values.instance, $scope.mdxQuery).then(function(data){

The cell set coming back from TM1 is then stored in an object called data This data object follows the OData structure:

You do not have to know the OData structure to be able to use Canvas but if you are curious, you can find out more about OData in this article:

• Understand OData in 6 steps

Instead of manipulating this object, you can transform it using the Canvas service resultsetTransform into a new object and store it to let us say $scope.values.table:

This service transfoms a cell set from TM1 into an object which you can easily manipulate to create a table. You can find more information about all Canvas services into the Canvas Help page (application/#/help):

This new object $scope.values.table has much more properties than the previous data object and it is easier to access:

Now that our object is ready, we can now build the table in our HTML page.

The new object created by the service resultsetTransfrom has been structure in a way that it is much easier to access the most important properties of a table such as headers and values:

The headers can be found in the elements property of any row, in this example we choose the first one (rows[0]):

The HTML code to display the row header is:

 <th ng-repeat="rowDimension in values.table.rows[0].elements" class="text-center">      {{rowDimension.dimension}}</th>

Again in this example we pick the first row to get all column headers:

<th ng-repeat="cellHeader in values.table.rows[0].cells" class="text-center">    {{cellHeader.key.split(',').join(' - ')}}</th>

The elements on rows are in the elements properties, we use the alias property to show the alias value:

The HTML code to display the element on rows is:

<tr ng-repeat="row in values.table.rows">    <td ng-repeat="rowDimension in row.elements" class="text-center">                {{rowDimension.alias}}     </td>

The values are stored in the cells property of the rows:

The HTML code is:

<tr ng-repeat="row in values.table.rows">        <td ng-repeat="cellHeader in row.cells" class="text-right">                {{cellHeader.value | formatNumber: 2}}         </td>

You are now ready to build your own table based on an MDX query!