Display data from an Excel file and then send it to TM1 via REST API

With Canvas, you can upload an Excel file into your browser, validate the data and then send the data into TM1 using the REST API. A sample showcasing this has been introduced in Canvas 2.0:

  • http://localhost:8080/samples/#/sample/sample-sheetjs

This article explains in details how this sample work.

Enable SheetJS library

The first step is to enable the optional libray sheet JS, the steps are described int he following article:

Upload Excel File

1. Create upload button in the view

To upload an Excel file into your browser, you need to create an upload button that will handle and help us process the Excel file, within the client's browser. To do that you just need to add and to utilize the js-xls directive as follows:

When you upload an Excel file, the read function is triggered and if there is an error, the error function is triggered.

2. Create 'read' function in the controller

You then need to define the read function. This function takes the processed workbook by SheetJS as an argument, initializes the variables. We will then call our parseWorksheet function which will help us extract the data from the Excel file and show it into our Canvas page:

3. Get values from the cells

The parseWorksheet function loops through all rows and associate the cell values, to an object we will name as record:

For example: for every row, we know that the product name is in Excel sheet's column A. To then access the name for each row (A1, A2, A3, etc.):

var product = sht["A" +(r + 1)].v;

SheetJS creates an object and make the cells accessible via properties. A cell in Excel has different properties such as: the formula used, the actual display value, etc. But where we are interested in is in the property named 'v', which contains the value. 

We will then associate all values for a row to a record. Each record will have one Boolean property indicating if it was selected, the product name on that row, one value for each month on that row and an error message.:

var record = {
      row: r - startRow + 1,
      selected: true,
      product: product,
      m01: m01,m02: m02,m03: m03,m04: m04,m05: m05,m06: m06,
      m07: m07,m08: m08,m09: m09,m10: m10,m11: m11,m12: m12,
      error: error
};

Each record will then gets pushed into an array stored within the lists.records object:

$scope.lists.records.push(record);

Near the end of this function, the lists.records should contain a list of record objects. Lastly, the validate function is then called to check these records for any errors or missing information.

4. Validate the set of records

The validate function loops through all the records we have created and check if the product is in the Product dimension. If it is not, it updates the error message and set its Boolean property to indicate selection, to false (this will make the record be not selected on the front end):

sheetjs4.png

5. Display the data into the browser

To display the records in the view, we use the ng-repeat directive in a table and iterate through the list of records we have prepared previously:

Send data into TM1 via REST API

Now that we have displayed the data in the browser, the last step is to allow the users to send this data to the right intersection of the TM1 cube:

1. Store all information in an object

We create first an array of CellPut request objects with the following properties:

  • [{value:'Test', instance:'dev', cube:'System Info', cubeElements:['Server Time', 'String']}]

We need one request per month:

2. Use CellSetPut

Once the array is populated, we just need to use one of the available Canvas scripting service for sending data into TM1 - $tm1Ui's service cellsetPut:

This service should then send a batch of cells into TM1 for update. In the case above, this should batch and send the cells within the row all at the same time into TM1 for updating.

If you prefer to send data into TM1 line per cell, use cellPut instead of cellsetPut:

  • cellPut(value, instance, cube, element1, element2, elementN)

Alternate Hierarchies in Canvas

Probably the most exiting feature in IBM Planning Analytics is alternate hierarchies. Using alternate hierarchies will enable you to potentially reduce the number of dimensions in your cube and therefore improve cube size and performance.

For example you could merge the Year and the Month dimension into one Time dimension:

Enabling Alternate Hierarchies

Alternate hierarchies are available since IBM Planning Analytics. To turn on alternate hierarchy, you need to add the new parameter EnableNewHierarchyCreation=T into the tm1s.cfg file and restart the TM1 instance.

DBRs

Support for alternate hierarchies has been added from Canvas 2.0 with tm1-ui-dbr. To get values using alternate hierarchies, you can now use the following syntax within the tm1-elements:

  • "Hierarchy1::Element1 && Hierarchy2::Element2"

For example, to get the value from 2009 Jan on the Time dimension, you will need to use the following when referring to the intersection of these hierarchies:

  • "Year::2009 && Month::Jan"

An example of DBR with alternate hierarchies can be found in the Components page of the samples application:

  • http://localhost:8080/samples/#/sample/component/dbr-hierarchy

MDX

Another way to get data using alternate hierarchies is to use an MDX query. To specify an element on another hierarchy within the same dimension, use the following format:

  • [Dimension].[Hierarchy].[Element]

For example to get the element All Months which is in the Month hierarchy of the Time dimension:

  • [Time].[Month].[All Months]

One of the ways to show data from an MDX query within Canvas is using a Named MDX. For more advance scripting, you can also create your own MDX query and pass it on one of the Canvas scripting service - cubeExecuteMdx(). Additional information on this and other available Canvas scripting services can be found on you application's Help pages. 

Different Sources for Bursting Reports

This article details the different bursting methodology you can use to send and to generate different PDFs and/or Emails.

Overview

To enable Report bursting on your reports, select the following option on your Report action:

Report-Config.PNG

An option to burst these reports is provided via the 3rd tab:

Tba.PNG

From which the following options is shown when you click the plus icon on the right side:

Type.PNG

Most types of bursting methodology allows you to define a placeholder or variable, which you can then use on your Emails or the URLs of your attachments which will then generate the PDFs.

For example, if we have defined a dimension subset as follows:

Burst-Subset.PNG

This means that the elements of the subset 'Burst' in dimension 'Region' will be available for each 'burst'. If for example the Burst subset has the following elements:

Burst-Sample.PNG

When the task runs, it will generate Emails and PDFs depending on how many iterations is available on your burst tab. On this case, since there are only two elements on the Burst subset, it will run two times. These elements are available and can be accessed by typing the following:

{{region}}

For example, in the subject of your email you can type like:

Email-Sample.PNG

The effect of this will then be, for each burst, the placeholder {{region}} will then be replaced with the iteration value - first is with 'Finland', second is with 'England'.

You can do this on the various parts of the email and also in the URLs if you have an attachment.

Here are the other source you can use for your report bursting:

Dimension Subset

Selecting this burst type means being able to utilize the elements in a subset, into your emails or reports. 

Dimension MDX

This option provides you the option to retrieve the elements by executing an MDX against a dimension.

Cube View

Burst-CubveView.PNG

As you have noticed, there is no input for placeholder. What it actually uses as placeholders are the names of the dimensions on the row of your Cube View! And this is how the cube view looks like:

Burst-Cube.PNG

With this, you can manage the bursting even via Cube View.

CSV

Using comma separated values as a starting point can also be used. This method allows you to use adhoc data. You can start by uploading a CSV into the provided box or create your own table of information:

Burst-CSV.PNG

Similar to Cube View, the placeholder name will be the header of the CSV, or the first row.

CSV Path

Instead of starting with a CSV file, you can provide a CSV path accessible in the server which will then get parsed when the task runs. This allows you to update the file prior to running the actual task, or have a TI for example, to re-generate the file prior to running this Report action. Note that you can also schedule a TI to run as part of this task (the others being Chores, and Wait times).

This is the options you will be provided with:

Burst-CSV-Upload.PNG

The files in here should then be accessible within the current Canvas' applications file directory as defined in the Settings. These are further separated by the instance name. So in the case above, the location will be at:

 <Canvas Application>\files\<instance>

Or as an example for our samples application that is for example installed in drive C,

C:\CWAS\webapps\samples\files\dev\content.csv

And that is it!

Lastly, also note that you can mix and merge different bursting sources!

Burst PDF reports with the Task Scheduler

From Canvas 2.0 and up, there is now a Task Scheduler which you can use to schedule tasks. These tasks can contain TM1 processes, TM1 chores or Canvas pages. This article describes the steps to follow to set up a new task which is going to send by email one dashboard per region.

SMTP Configuration

Before scheduling a task, you should check if the SMTP configuration has been configured correctly. Canvas allows you to send emails, which it does by accessing an SMTP server. For most corporate networks, the SMTP server can accept any requests to send an email from servers within the same network.

This is an example of settings with an Office 365 accounts, you can test your settings with the paper plane button at the top right:

For more information about SMTP configurations, you can check the following Help article: Email settings.

Open Task Scheduler

There is one Task Scheduler per application, you can access it from the admin section:

  • http://<server name>:8080/<application name>/admin#/task-scheduler

For example, to access the Task Manager of the samples application:

Create a new task

In this page you will see the list of tasks. Click the Add New Task button at the top right: 

Add name and instance settings:

The name is the task name which will appear in the main page. There is a status button which is active by default. You then need to add the login information of the TM1 instance that will be used on this task:

  • Instance: TM1 instance name defines in the instances.json
  • User Name: TM1 user
  • Password: TM1 Password
  • Use CAM: Yes if you are using CAM Security
  • CAM Namespace: CAM Namespace

Add Schedule (optional)

The next step is to schedule the task, add a name for the schedule and then click the schedule button:

A pop-up window will appear with different options to choose from for scheduling your task such as every month, week, day, etc.:

Click the Check Schedule button to see when its next runs. Finally click Apply Schedule.

Add an action

There are different action that you can add such as run TM1 process, TM1 Chore, add a wait and run a report. In this example we will add a Report:

Then click on the icon to update the information about the report:

Email information

For each report you need to specify the email information. The description is used only in the Task Scheduler. If you burst multiple reports, you can choose between sending one Email for all reports or one email per attachment:

Report information

Attachments are the URL path to the Canvas pages. Canvas uses the URL to create PDF reports. If you want to send the report with the filter region, you have to make sure that the filter is in the URL, if not it will always print the report with the default filters. To learn how to dynamically update the URL, you can check the following help article: Update the URL before printing.

In this example, the bursting will be based on the regions. In the attachment name and in the url we use {{region}} to get the value of the region we are bursting:

Burst information

There are different ways to drive your bursting. You can either drive the bursting based on a Dimension Subset, Dimension MDX, Cubeview, CSV file or a path to a CSV file. You can mix and match these. You can also use these in the three special markups. Combining these can potentially help you manage the recipients by Region for example.

In this example we drive the bursting based on the Default subset of the Region dimension:

Each type of burst item presents you a placeholder variable which you can then use in the Email or Report tab. The placeholder name is the name of the variable you can then use in either Report or Email tab.

Click Save button

The last step is to save the task:

Burst report 12.png

Tasks list

Once save you will than see the new task in the Task List, you can then disable or enable them from this page:

Execute

Just click the execute button if you want to test your new task. In this example we receive one email with one report per region in the Default substet:

To send one email per report, in the Email tab click An Email per Attachment Burst, in the email subject you can use {{region}} to add the region name:

You can even customize the email body with some information about the region such as attributes or key KPIs. To bring this information you can either use DBRA or DBR with the following syntax:

  • [[ dbr:<instance>:<cube>:<elements> ]]

    o   i.e. [[dbr : dev: Product :Actual, 2012, Local, Finland, All Products by Category, Standard Cost]]

  • [[ dbra:<instance>:<dimension>:<element>:<attribute> ]]

    o   i.e. [[dbra : dev: Region :Finland: Currency]]

  •  [[ rundate:<date format> ]]

    o   i.e. [[ rundate: yyyy.MM.dd]]

For example in the example below, the email body will contain the Currency attribute, the Standard Cost and List Price value:

Tasks folder

All Tasks can be found in the \CWAS\webapps\<application name>\WEB-INF\config\tasks folder:

For the samples application:

  • C:\CWAS\webapps\samples\WEB-INF\config\tasks

The Task ID is found in the URL when a task is opened in the Task Scheduler:

This should help you find the file within the config/tasks folder if it needs to be moved into a different server, or for backing up purposes. 

To learn more about the different sources for bursting reports you should check the following article:

 

Refresh DBRs by Groups

By default when you input a value in a cell containing DBRs in Canvas, all the DBRs will refresh. From Canvas 2.0 and up, you can now group the DBRs so that when you input a value, only the DBRs of the same group as that input will be refreshed. This could greatly improve the responsiveness of your page.

For example if the table contains data from two different cubes, you could split the DBRs into two groups.

To group DBRs, you will need to add to the tm1-ui-dbr directive, the new attribute tm1-refresh-group="<group name>". For example if you want to call the group Group 1, it will look like this:

  • tm1-refresh-group="Group 1"

An example of the tm1-refresh-group button can be found in the components of the samples application.

  • http://localhost:8080/samples/#/sample/component/dbr-group-update

This page contains the DBRs which get the same value in TM1, if you input a value in the first DBR, all DBRs will refresh:

The DBR in the No Group block does not have any group defined:

<tm1-ui-dbr
   tm1-instance="dev"
   tm1-cube="General Ledger"
   tm1-elements='Actual,2011/12,Mar,Local,England,Sales and Marketing,Employee Benefits,Amount'>
</tm1-ui-dbr>

When you write a value to this DBR it will update every other DBRs, even the one which are in a specific group.

However if you input a value in the first group DBR, you will see that it will only update the DBRs within that panel (all the these were given the same group for sample purposes). And in connection to this, the DBRs in Group 2 panel will not refresh:

If we have a look at the code, we can see that both DBR are in different groups:

This grouping also applies to the other DBRs within that panel, per group.

Conclusion:

This feature should allow you to localize your inputs and updates, giving you further control on how to update your page.

When you input a value to a DBR and if it is part of a group, only the DBRs within the same group will be refreshed. If the DBR is not part of any group, as per normal cases, then all DBRs will be refreshed even the ones which are parts of a group.

Synchronizing SUBNM and URL

This article describes how to make the SUBNM component to sync with the URL whenever it changes. This will also describe how the SUBNM's initialization works.

The problem

Let's take the following page which has one SUBNM, every time the SUBNM is updated, the URL is updated as well.

One common issue that you might have, is when you print the report in PDF, instead of showing the current value (England), the value in the SUBNM always shows the default value (Total Europe).

To understand why, when you refresh the page or when you print the page, the value displayed in the SUBNM is always the default value, we will go through the code of of the HTML page.

Define the default value

In the controller there are two variables:

  • defaults.region: the default value is set to 1 (Total Europe).
  • selections.region: The variable which is going to be updated from the SUBNM.
    $scope.defaults = {
        region: '1'
    };

    $scope.selections = {
        region: $scope.defaults.region
    };

SUBNM

In the HTML page, we have a basic SUBNM driven by the Default subset of the Region dimension.

<tm1-ui-subnm
    tm1-instance="dev"
    tm1-dimension="Region"
    tm1-subset="Default"
    tm1-default-element="{{selections.region}}"
    ng-model="selections.region"
    tm1-change="setRegion(data)"
>
</tm1-ui-subnm>
  • tm1-default-element enables us to set the default value of the SUBNM to the selections.region.
  • ng-model is used to store the value chosen by the SUBNM in the variable selections.region.
  • tm1-change is used to trigger an action each time the SUBNM value is updated. Every time the value of the SUBNM is updated, the function setRegion is triggered.

The function setRegion is defined in the controller. This function updates the selections.region variable and update the URL:

$scope.setRegion = function(region){
  $scope.selections.region = region;
  if(region != $scope.defaults.region){
    // Set the URL parameter
    $location.search("region", region);
     }
   else {
     // If the parameter equals the default value remove it from the URL
     $location.search("region", null);
      }
 };

We use location.search() to get the value from the URL. If there is a value in the URL, when you refresh the page, the selections.region will be set to this value:

if($location.search().region){
        $scope.selections.region = $location.search().region;
    }

SUBNM Deep Dive:

When you refresh the page, the SUBNM initializes following these two steps:

Step 1: The SUBNM retrieves the elements from the subset.

Step 2: It initializes the ng-model (ng-model="selections.region"). It means that it initializes the selections.region variable with the first element in the subset (Total Europe).

Even if the selections.region value was defined in the controller, this value is overwritten by the SUBNM.

Step 3: The SUBNM checks the value defined in tm1-default-element attribute, selections.region. At this stage the selections.region is currently equal to Total Europe.

Each time you refresh the page the selections.region variable is set back to Total Europe (the first element in the subset).

That is why the variable used for the tm1-default-element and the ng-model has to be different.

The solution:

To do that, replace the selections.region with a new variable values.urlRegion:

  • tm1-default-element="{{values.urlRegion}}"

<tm1-ui-subnm
     tm1-instance="dev"
     tm1-dimension="Region"
     tm1-subset="Default"
     tm1-default-element="{{values.urlRegion}}"
     ng-model="selections.region"
     tm1-change="setRegion(data)"
>
</tm1-ui-subnm>  

This new variable will be used only to update the URL and set the SUBNM default value.

In the controller, the URL is now driven by this new variable values.urlRegion:

    $scope.values = {
        urlRegion: $scope.defaults.region,
    };
    
    if($location.search().region){
        $scope.values.urlRegion = $location.search().region;
    }
    
    $scope.setRegion = function(region){
        $scope.values.urlRegion = region;
        if(region != $scope.defaults.region){
            // Set the URL parameter
            $location.search("region", region);
        }
        else {
            // If the parameter equals the default value remove it from the URL
            $location.search("region", null);
        }
    };

Now when you print the report or if you refresh the page, the SUBNM value will match the URL value:

Repeat more than one row in your table

This article describes how to repeat two rows for one item using the angular directive, ng-repeat-start:

The easiest way to create a table in Canvas is to use the angular directive ng-repeat. In a table, ng-repeat is going to repeat one row or one column per items. If you do not have lots of space in your dashboard showing too much information in one row might look a bit too squashed:

To make some room in your table, instead of showing everything in one row, you could create two rows per product, one with the alias and a second row with the year and months values:

Create one row using ng-repeat

Instead of using ng-repeat="product in lists.products" which creates one row for each product with:

  • One column for Product description.
  • One column for Year value.
  • One column per months.
<tr ng-repeat="product in lists.products" ng-hide="selections.zeroSuppress && product.yearTotal=='0'">
    <td> 
            {{product["Description"]}}
    </td>
    <td style="font-weight:bold">
        <tm1-ui-dbr 
            tm1-instance="dev" 
            tm1-cube="Retail" 
            tm1-elements="{{selections.version}},{{selections.year}},year,local,{{selections.region}},{{product.key}},{{selections.measure}}"
            tm1-data-decimal="0" 
            ng-model="product.yearTotal">
        </tm1-ui-dbr>
    </td>
    <td ng-repeat="period in lists.periods">
        <tm1-ui-dbr 
            tm1-instance="dev" 
            tm1-cube="Retail" 
            tm1-elements="{{selections.version}},{{selections.year}},{{period.key}},local,{{selections.region}},{{product.key}},{{selections.measure}}"
            tm1-data-decimal="0">
        </tm1-ui-dbr>
    </td>
</tr>

We are going to use the Angular directives ng-repeat-start and ng-repeat-end. This is how our HTML structure will look like:

<tr ng-repeat-start="product in lists.products">
    <td>
    </td>
</tr>
<tr ng-repeat-end>
    <td>
    </td>
</tr>

ng-repeat-start

The ng-repeat-start directive works the same as ng-repeat, but will repeat all the HTML code (including the tag it's defined on) up to and including the ending HTML tag where ng-repeat-end is placed.

In our example we use ng-repeat-start to create a first row with the product description and a small chart.

We actually created only one data column <td>, and we made this column the same size as the 13 columns of our table by using a <td> attribute, colspan="13"

<tr ng-repeat-start="product in lists.products" ng-hide="selections.zeroSuppress && product.yearTotal=='0'">
    <td class="padding-left" colspan="13" style="background-color:#E5E5E5">
        <div class="pull-right" style="width:15%">
            <tm1-ui-chart 
                tm1-chart-type="discreteBar" 
                tm1-data-decimal="0" tm1-hide-controls="true" tm1-height="20" tm1-margin="{top:2,bottom:2,left:2, right:2}"
                tm1-legend="none" tm1-color-scheme='["#05668D", "#028090" ,"#00A896"]' tm1-axis-label-y=""
                tm1-state="{{product.yearTotal}}" tm1-axis-label-x="">
                <tm1-ui-chart-dbr 
                    ng-repeat="month2 in lists.periods" tm1-label="{{month2.alias}}" 
                    tm1-instance="dev" tm1-cube="Retail" 
                    tm1-elements="{{selections.version}},{{selections.year}},{{month2.key}},local,{{selections.region}},{{product.key}},{{selections.measure}}">
                </tm1-ui-chart-dbr>
            </tm1-ui-chart>
        </div>
        <div style="width:85%;font-weight:bold"> 
            {{product["Description"]}} 
        </div>
    </td>
</tr>

Even if {{product["Description"]}} is on the left of the chart, it is good practice to put the chart first, because the chart determine the height of that section. We use the Bootstrap class pull-right to push the chart to the right:

ng-repeat-end

Now, we use ng-repeat-end to create the second row which shows the Year and the months values:

<tr ng-repeat-end ng-hide="selections.zeroSuppress && product.yearTotal=='0'" style="border-top:none!important;border-bottom:none!important">
    <td style="font-weight:bold">
        <tm1-ui-dbr 
            tm1-instance="dev" 
            tm1-cube="Retail" 
            tm1-elements="{{selections.version}},{{selections.year}},year,local,{{selections.region}},{{product.key}},{{selections.measure}}"
            tm1-data-decimal="0" 
            ng-model="product.yearTotal"></tm1-ui-dbr>
    </td>
    <td ng-repeat="period in lists.periods">
        <tm1-ui-dbr 
            tm1-instance="dev" 
            tm1-cube="Retail" 
            tm1-elements="{{selections.version}},{{selections.year}},{{period.key}},local,{{selections.region}},{{product.key}},{{selections.measure}}"
            tm1-data-decimal="0">
        </tm1-ui-dbr>
    </td>
</tr>

This is how your table will look like:

You can use the Angular directive ng-repeat-start with any HTML components, more information can be found in the AngularJS website.

Customize PDF export layout with CSS and Bootstrap

One of the great feature of Canvas is that the style of your dashboard can be different regardless of viewing it in a web browser or as PDF format.

For instance, your dashboard could have a grey background in the browser but when you print it, the background could change to white and the buttons could be replace with text:

You can achieve this outcome with just a few lines of CSS and one bootstrap class.

Update background when printing

Inside the internal style sheet (<style> </style>), first define the grey background (#EAEAEA) of the page (page-view) and then to change the background when printing you need to define a new page-view background color inside the @media print CSS query:

        #page-view {
            background-color: #EAEAEA;
        }

        @media print {
            #page-view {
                background-color: #FFF;
            }
        }

All CSS defined inside the @media print CSS query will be applied only when the dashboard will be printed into PDF.

Replace buttons

To replace the buttons with their values, we need first to hide the buttons using CSS and then we use a Bootstrap class to show the filters value only when printing.

To hide buttons when printing, you just need to add the following code inside the @media print CSS class:

.btn {
      display: none;
      }

Note that the above code will hide all buttons in the page.

If you want to hide only a specific button, you will need to add an id to the HTML component so you can specify that out later. Take for instance a filter:

<div  id="filter" >

Now we then add the CSS code to @media print query:

#filter {
         display:none;
         }

CSS Selectors:

Take a note also that we now use '#' instead of '.' in front of the id, we have just created. There should be lot of articles discussing CSS Selectors. To help on this article, just remember:

  • '.' means selecting all HTML elements with that CSS class. 
  • '#' means selecting all HTML elements with that ID.  

For those other sections that you only want to show during printing, you can use the visible-print-block Bootstrap class. For example, if we want to show the values of the filters on the section we have hidden previously, we can create another section and add the class there:

<div class="col-md-6 visible-print-block"> 
    <h3> Filters: {{selections.version}} - {{selections.month}} {{selections.year}} - {{selections.region}} - {{selections.category}}
    </h3> 
</div>

All HTML element inside the visible-print-block div will be shown only when report will be printed into PDF.

To learn more about how to customize your report depending on screen size or devices, you should check CSS Media Queries page on the w3school website.

Configuring SheetJS (Optional Library)

Beginning with Canvas version 1.2.2, optional libraries will be included within assets folder but will need to be enabled before it can be used. This will involve:

  • Adding the link to the JS/CSS file in the header
  • Pushing the Angular module of the library into your application

Enable SheetJS

SheetJS a library that helps working with spreadsheets on the web! Check their website for more info. 

To enable the functionality in Canvas, do the following steps:

    1. Update WEB-INF/pages/header.library.ftl file with the following:

<script src="assets/js/optional/libs.opt.sheetjs.js"></script>

    2. Update WEB-INF/pages/header.script.init.ftl file by adding below line of code:

customAngularModules.push('angular-js-xlsx');

It is now enabled!

Create upload button

To check it out, create a simple Canvas Blank page with the following HTML line of code:

<js-xls onread="read" onerror="error"></js-xls>

And on the page's corresponding controller:

$scope.read = function (workbook) {
      /* DO SOMETHING WITH workbook HERE */
      console.log(workbook);
};
    
$scope.error = function (e) {
      /* DO SOMETHING WHEN ERROR IS THROWN */
      console.log(e);
};

You should be able to see on your HTML page, something similar to:

Clicking the "Choose File", will open up a Windows dialog to select an Excel file example to load. You will get the following response in the console showing the contents of the file:

That is it!

Which files are overwritten during a Canvas upgrade

During a Canvas upgrade, the installer can upgrade either the Cubewise Application Server or the application folders (webapps) or both. That is why you have to be careful about which files you can and can't be changed to allow upgrades to work smoothly.

In this article we use the following color coding:

  • red: all files should not be manually modified.
  • yellow: some files can be manually modified.
  • blue: all files can be manually modified.

CWAS

  • backups: contains all application folders backups created during an upgrade.
  • conf: you can update the server.xml file to update the Canvas port number.
  • webapps: contains all Canvas applications.

    CONF

    In the Conf folder the only file you should update is the server.xml file where you can update the Cubewise Application Server's port number. But do note that this will be overwritten if the option "Install Canvas Application Server" is selected. So migrate your settings after upgrade.

    In the Conf folder you will find the license file (Canvas.xml).

     

    webapps

    In the webapps folder, the files inside samples folder are overwritten during an upgrade when the "samples" application is checked.

     

     

    An application folder

    • css: global format settings of the application.
    • html: all HTML pages.
    • images: all images.
    • js: all controllers.
    • menu: left pane menu structure.
    • WEB-INF: configuration.

     

     

     

     

    HTML

    You can create as many HTML pages as you want. We recommend to use the Page Creator to create a new page.

    JS

    In the JS folder you will find all controllers, you can add as many controllers but just do not touch the admin and system folders.

    Menu

    You can update manually the menu.json and the states.json file but you need to be aware that these files can be updated from the user menu management page in the admin section.

    WEB-INF

    • config: security settings such as updating admin password.
    • pages: index.ftl file, check the following article.
    • resources: Define all MDX queries.
    • instances.json: TM1 connection settings

    License file

    The Canvas application server will not work without a license file. To get a license file, you need to send the server name to your Cubewise local office. You need one license file per server.

    1. Once you have received the license file (Canvas.xml), put it in the CWAS\Conf:

    2. Then restart the Cubewise Application Server.

    3. Clear the cache (SHIFT + F5) and then refresh your browser, the "invalid license" message should have disappeared.

    If the message is still there, you can first check if the server name is correct in the license file. To see if the license has not expired, you can check the expiration date:

    Page break in PDF report

    When printing a report using Canvas, you can customize as much as you like the PDF output. There are lots of CSS properties that you can use such as CSS Media Queries to update for instance the background color of the PDF. The PDF can be very different from the HTML page.

    One thing to be careful about when you print your page is the page break. To avoid a table or a chart to be divided between two pages, you can define in your HTML page where you want the page break to appear.

    If you do not specify any page break in the HTML page when printing, your report can break anywhere:

    This article describes how the page break works in the sample print:

    • http://localhost:8080/samples/#/sample/print

    Create a CSS class

    At the top of the page, first you need to create a new CSS class. In the example below we create a new class called other-pages with the property page-break-before:always:

          .other-pages{
            page-break-before: always;
          }

    page-break-before always insert a page break before an HTML element it is assigned to. There are other CSS properties that you can use such as page-break-after which insert a page break after an HTML element it is assigned to.

    Add the CSS class to an HTML element

    In the print example, we assigned the new CSS class to a div but you can add this class to any HTML element where you want the page break to happen:

    <div class="row other-pages"  >

    This div has now the CSS property to always break the page before, if you print the page you will see the page break just before the chart:

    To add a third page to your report you just need to add a new div with the other-pages property:

    <div class="row other-pages"  >
            Add a third page
    </div> 

    It is important to notice that the page break will appear only in the PDF, in the HTML page, it will be like a normal div:

    After printing you will see a new page:

    Override the default number format

    Canvas includes the accounting.js library. With accounting.js you can do advance formatting for number, money and currency.

    The default number format is:

    • Thousands separator: ","
    • Decimal separator: "."

    There are two ways to update the default format.

    1. Override the format for the all application

    The default number format is defined by applications. To update the default format for the all application, you need to update the client.startup.js:

    • C:\CWAS\webapps\<application name>\js\client.startup.js

    For example to show "." as thousands separator and "," as decimals, you will need to add the following lines:

        accounting.settings.currency.thousand = '.';
        accounting.settings.currency.decimal = ',';

    It is going to update the format for all numbers:

    2. Override the format for a specific DBR

    To override the format for a specific DBR, you need to add to the DBR the attribute tm1-accounting-settings-override. For example to show a "," as thousands, "." as decimal and a precision of 2, you need to add:

    • tm1-accounting-settings-override="{currency:{thousand:',',decimal:'.',precision:'2'}} "

    Save the file and refresh the page, now you should see the DBR with the new format:

    The attribute tm1-accounting-settings-override overrides the attribute tm1-data-decimal. For example if you have tm1-data-decimal="2" and  tm1-accounting-settings-override="{currency:{thousand:',',decimal:'.'}} " in the same tm1-ui-dbr:

    It will show a value with no decimal because the default precision of accounting.js is 0. To see a precision of 2 you need to add precision:'2' to tm1-accounting-settings-override:

    Index.ftl structure

    Canvas is a single page application SPA. Each Canvas application has a single web page which swaps and loads its different contents, depending on what the user click. All HTML pages are loaded in the container (blue). A single page (red) is loaded, that is why you see a “#” between the URL and the HTML path:

    The red area is defined by the index.ftl file and the blue area is your HTML page. The index.ftl file is used to update everything that you find in the red area such as logo, headers, menu and homepage.

    Prior v1.2, to update the logo or add a custom library you had to update the index.ftl in the following folder:

    • C:\CWAS\webapps\<application name>\WEB-INF\pages

    We moved the index.ftl to the following new folder:

    • C:\CWAS\webapps\< application name>\WEB-INF\pages\system

    So updating you logo is now done through the navigation.logo.ftl file.

    Important: If you upgrade your existing Canvas application to Canvas v1.2. You will have to migrate all changes you made from your index.ftl (WEB-INF\pages\) into the different new ftl files (WEB-INF\pages\). The index.ftl file which is used by the Canvas application is in \WEB-INF\pages\system.

    We did this in order to split the index.ftl file between the area you can update and the area you should not update. Starting with v1.2, the WEB-INF\pages\system\index.ftl file should not be updated anymore. The area you can update is now separated into different files that you can find in \WEB-INF\pages. The new index.ftl will include these new customizable ftl file during application load.

    There are now 9 ftl files:

    • header.library.ftl: To add links to your custom libraries.
    •  header.script.init.ftl: For configuring your SSO and adding your angular modules.
    • header.script.others.ftl: Configure your accounting.js settings and other initializations.
    •  navigation.body.custom.ftl: Make your own navigation body.
    • navigation.logo.ftl: Update the logo.
    • navigation.top.left.ftl: For adding items into the top left navigation bar.
    •  navigation.top.right.ftl: For adding items into the top right navigation bar.
    • page.footer.ftl: Add your custom page footer.
    • page.header.ftl: Add your custom page header.

    The contents of these files get inserted into the index.ftl file when your Canvas application loads. Should you want to know more about the expected content of each FTL file, you can check out the index.ftl file to see where these files will be inserted and what HTML elements are near it.

    Schedule jobs to send PDF reports

    With Canvas v1.2, you can now print your Canvas page into a PDF report and schedule a job to send it by email at a specific day or time. This article describes the steps how to create a job scheduler.

    Set up SMTP configuration

    Canvas allows you to send email alerts, to do this it needs to access an SMTP server. For most corporate networks the SMTP server will accept any email send requests from servers in the same network.

    To update the SMTP configuration go to the setings page in the Admin section, you can find below the settings of an office 365 account:

    For more information about SMTP configurations, you can check the following Help article: Email settings.

    Go to Reports Manager

    To create a job, you have to open Reports Manager.

    • http://localhost:8080/<application name>/admin#/reports-manager

    You will see first the job list, click the + button to create a new job:

    Create a new Job

    First you need to enter the Job details such as Name, Subset, Recipients and the Email Body:

    To add more recipients, just separate their email addresses with ",":

    Schedule the job

    Click on the icon to pop up the Scheduler Helper. There are different options to choose from for scheduling your job such as every month, week, day, etc.:

    Click the Check Schedule button to see the next runs. Finally click Apply Schedule.

    Do not forget to add the name, the name will be shown in the job list.

    Attachments

    Attachments are the URL path to the Canvas pages, Canvas uses the URL to create PDF reports. If you want to send the report with the filter England, you have to make sure that the filter is in the URL, if not it will always print the report with the default filters. To learn how to dynamically update the URL, you can check the following help article : Update the URL before printing.

    Do not forget to add the credentials to login to the TM1 instances (instance name, user name and Password).

    Click the save button to make sure your new job is set up correctly. You can test your job by clicking Execute button:

    If you go back to the job list, you should now see your new job:

    Update the URL before printing a page

    With Canvas v1.2, you can now print and schedule a report. Canvas uses the page URL to print a report in PDF. If you do not update the URL, Canvas will always print the report with the default criteria.

    This article explains how you can update the URL without reloading the page.

    In the "print" dashboard, if you click on one of the KPI at the top, for instance Gross Margin, the URL will be updated:

    The print button in Canvas, uses the current URL to create the PDF report.

    If the URL is not updated, when you click on the print button, Canvas will print the default URL:

    • http://localhost:8080/samples/#/sample/print

    instead of printing the report with Gross Margin:

    • http://localhost:8080/samples/#/sample/print?driver=Gross%20Margin

    To modify the URL without reloading the page, we are going to add a new function in the controller (JS page) and from the HTML page, we are going to call this function.

    1. Create the function in the controller

    $location.search definition

    To update the URL, we are using the search method of the angular service $location. The $location service parses the URL in the browser address bar and makes the URL available to your application.

    How does it work:

    1. $location makes the URL available to our application
    2. We use search method to update the URL.

    Add $location as a new controller argument

    To be able to use $location service in our controller we have to add it as a new argument in our controller:

    Declare variables

    We need two variables:

    • $scope.defaults.driver: default values to be used before selections are made
    • $scope.page.driver: value which will be updated during navigation

    In the example below we have three defaults variables (defaults.region, defaults.department and defaults.driver) and 6 variables that we are going to use in our HTML page (page.region, page.departement, page.version, page.driver, page.segment and page.subset):

    Get a value from the URL

    To get a value from the URL we use the search method: $location.search()

    In our example we want to get the driver value so we attach driver to $location.search():

    • $location.search().driver

    If the URL is http://localhost:8080/samples/#/sample/print?driver=Operating%20Expenses then $location.search().driver will be equal to Operating Expenses.

    Initialize the page variable

    If there is a driver value in the URL we initialize the page.driver variable:

    if($location.search().driver){
            $scope.page.driver = $location.search().driver;
        }

    Send a value to the URL:

    We're going to create a new function called setDriver which will be called from the HTML page. The objective of this function is to update the URL if the driver in the page (scope.page.driver) is different from the default driver (defaults.driver):

    $scope.setDriver = function(driver){
            $scope.page.driver = driver;
            if(driver != $scope.defaults.driver){
                // Set the URL parameter
                $location.search("driver", driver);
            }
            else {
                // If the parameter equals the default value remove it from the URL
                $location.search("driver", null);
            }
        };

    2. Update the HTML page

    In the HTML page, we just need to call the setDriver function created in the controller when we click on a button:

    • ng-click="setDriver(item.description)"

    In this example when we click, we send the item.description to the URL.

    Create a new page using Page Creator

    To create a new page, it is recommended to use the Page Creator which you can find in the Admin section. Each Canvas application has its own Admin section. In the following example, our application name is training. You can access the Admin section of the training application via the following URL:

    The default log in is ‘admin’ and leave the password blank, then click the Login button:

    If you forgot the admin password, you can reset it by following these steps.

    You can access the Page Creator via the left pane menu, then click Blank Canvas Page:

    • Page Name: Visualisation
    • Menu Name: Visualisation
    • Menu Icon: fa-bar-chart

    Click the Create button and then go back to the training application:

    Refresh the page (or press F5) and you should be able to see the new page, Visualisation:

    When you click the create button, Canvas will create an HTML page with just the icon and the title defined.

    When you use the Page Creator to create a new page, Canvas creates a new HTML file with the same name in the <application name>\html folder. Canvas generates a basic HTML code, the header with the title and icons you chose (in orange) and an empty row with 3 blocs (in yellow), just to pre-define the structure of the page.

    Note that Canvas will also create a controller (JS file) in the controller folder (js/controllers).

    Query an external database from Canvas

    With Canvas you can display data coming from your TM1 server and also from an external database. An example can be found in the samples:

    • http://localhost:8080/samples/#/sample/sql

    Define ODBC Connection

    For Canvas to access the datasource, you have to first set up the ODBC Connection. Note that if the samples application has been selected during installation, a corresponding ODBC connection should come pre-configured and will be shown as below:

    Define the SQL query

    The SQL query and configuration 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 a 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 INSERT, UPDATE or even a DELETE command.

    To create a new query, you can create a new file based on the <application name>/api/samples/sql.jsp and then replace the SQL settings:

    • In red, this is where you define the connection to the database.
    • In blue, the SQL query.
    • In black, the parameters, one for each “?” in the query.

      query.setString(1, department); represents the first “?” in the SQL query.

    Initialize the data in the controller

    Once the SQL query is set up in the jsp file, the next step is to call the query from the controller and store the result of the query into the $scope.data variable ($scope.data = result.data):

    $scope.query = function() {
            $http.get("api/samples/sql.jsp?instance=dev&department=" + $scope.page.department + "&skip=0&max=" + $scope.page.max).then(function(result){
                $scope.data = result.data;
            });
        };

    If you are using a new query stored in a new file called newsql.jsp, access your SQL query by sending a GET request to the following path: api/samples/newsql.jsp.

    Call the query from HTML page

    Once the $scope.data is defined in the controller, in the HTML page you can display the result of the query by using data variable:

    <tr ng-repeat="item in data">
            <td>{{item.FinanceKey}}</td>
            <td>{{item.DateKey}}</td>
            <td>{{item.OrganizationKey}}</td>
            <td>{{item.DepartmentGroupKey}}</td>
            <td>{{item.ScenarioKey}}</td>
            <td>{{item.AccountKey}}</td>
            <td>{{item.Amount | number}}</td>
          </tr>

    Change the default colors of a chart

    There are 3 different ways to update charts colors in Canvas:

    •      Update the default application color scheme.
    •      Define the color scheme in the chart property.
    •      Define the color of a specific chart element.

    Update the default application colour scheme

    In Canvas, when you create a chart, if you do not specify any colors, the chart will take the default application colors. These default colors are defined in the instances.json file in the WEB-INF folder of your application:

    Colours are defined in CSS colour format. You can find lots of colour supported by all browser in w3schools website:

    You can get more colours by clicking on the Shades button:

    Let’s update the instances.json file with the following colours:

     "chartColorScheme"=["#0000ff", "#3333ff", "#6666ff", "#9999ff", "#b3b3ff"]

    For Canvas to pick up the changes in the instances.json file, you have to restart the Cubewise Application Server. Then refresh your page and you should see the new colours:

     Define the colour scheme in the chart property.

    You can override the application default scheme by adding the tm1-color-scheme attribute to the tm1-ui-chart:

    tm1-color-scheme='["#009900", "#00b300", "#00cc00", "#00e600", "#00ff00"]':

    Save the page and refresh your web browser, you should now see new colours:

    Define the colour of a specific chart element

    You can override the chart colour scheme for a specific bar by adding the tm1-color attribute to the tm1-ui-chart-dbr:

     tm1-color ="#000000”

    Let’s add this attribute to the first bar:

    Save and refresh your page, you should now see the first bar with a black colour:

    Debug a Canvas page (Controllers and Debugger)

    Each browser has each of its own debugging tools to help you troubleshoot your web application. And each tool gives you different views and helpful information about your HTML page. 
    On this article, we will be using Chrome as the browser and how we can debug by enabling breakpoints.

    To debug a web page, there are 2 steps that you will need to do beforehand. First you need to mark a stop in your code where you want the browser to stop when its refreshing the page. Then, once the browser stopped, you can start debugging by going through your code step by step.

    Set up the debug point

    Inside the JavaScript codes

    Note that the Developer Console (press F12) must be open before proceeding. Here are the two common ways that you can do to enter debugging mode:

    var i = 0;
    
    // other logics here...
    
    debugger; // pause here

    Adding this line of code into your JavaScript will tell Chrome to halt execution up to this point. 

    Inside the browser:

    A second way is: on the Sources tab, navigate to the JS file that you wanted to debug. The contents of this file will be shown on the right which should also show the line numbers for each code. Clicking on the line number for which you want to halt execution will enable/disable breakpoint.

    Start debugging


    Now that you have setup your debug points, it is time to start examining!
    Let us say, we have a page debug.html, with the following SUBNM where we would like to do something when the value of the SUBNM changes (tm1-change="page.onSubNmChange(page.year)"):

    <tm1-ui-subnm 
            tm1-instance="dev" 
            tm1-dimension="Year" 
            ng-model="page.year" 
            tm1-change="page.onSubNmChange(page.year)">
    </tm1-ui-subnm>

    and a controller debug.js with the following code:

    $scope.page = {};
        
    $scope.page.onSubNmChange = function(data){
      debugger;
      
      for(var i = 1; i < 10; i++){
        console.debug('i: %o', i);  
      }
      
      console.debug('onSubNmChange %o', data);
    };

    As you can see, there is a line of code containing 'debugger;'. Once you reload the page, your console should look like this:

    Notice the debugger was highlighted in blue. We are now in debug mode!

    On debug mode, there a few more buttons now available that can help you. Hovering the mouse pointer over them will give you the keyboard shortcuts to trigger them.

    For simplicity purposes, we will just focus on two functions: F8 and F10. You will see these on the main browser screen too.

    Pressing F8 means it will continue on until the next breakpoint or debugger; line. Whereas pressing F10 means, "execute this line of code, and go to the next".  Since we are in debug mode already, do explore the Sources tab and hover over some variables. Think of this as a way of examining processed variables at certain point of execution! And this is how it can look like as you hover (I hovered over the variable i, to see what is the current value):

    Pressing F10 and examining the variables along the way will give you an idea on what and how it is currently working. This should also help you trace through your various functions, especially if you are calling a function within a function!