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>