How does Power BI work with CASM Reporting APIs

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

This article offers a detailed walkthrough on setting up Power BI for report generation, with a particular emphasis on its integration with the CASM Reporting API. It details the essential procedures for utilizing Power BI's visualization features to construct reports based on SDM out-of-the-box reports.

This document provides an example for the "Change Orders by Category Report" out-of-the-box report. You can use it as a base for other reports (OOTB or custom), where some items will differ, like the value set for X-Report-Code, Result items, etc.

Environment

Service Desk Manager 17.4 RU4 and later

Microsoft Power BI 2.x

Resolution

1. Log into Power BI | Blank Report

2. Click on Get Data | Blank Query | Rename the query to GetAccessToken in the right pane under "Properties"

3. Click on the "Advanced Editor" on the menu bar | Copy and paste the following code:

(tokenUrl as text, authHeader as text) =>
let
    headers = [
        #"Authorization" = authHeader,
        #"Content-Type" = "application/json"
    ],
    body = Text.ToBinary("{}"),  
    response = Json.Document(Web.Contents(tokenUrl, [
        Headers = headers,
        Content = body
    ])),
    access_token = response[accessToken]  // Adjust key name as per casm json response for access token.
in
    access_token

4. Click on the "Close & Apply" button

5. Click on Get Data | Blank Query | Rename the query to GetReportDataByCode in the right pane under "Properties"

6. Click on the "Advanced Editor" on the menu bar

7. Copy the following code to a text editor:

 

let
    //Get token using the function defined in step1 and get the base 64 encoded username+password. You can use swagger to grab this and it's one time definition.
    token = GetAccessToken("http://<SDM hostname>:<Report API port>/CASMReports/reports/v1/access", "Basic <BASE 64 encoded username+password>"),

    // API call to get the report data using token + report code
    url = "http://<SDM hostname>:<Report API port>/CASMReports/reports/v1/getReport",
    rawResponse = Web.Contents(url, [
        Headers = [
            #"X-AccessToken" = token,
            #"X-Report-Code" = "Change_Change_Orders_by_Category_Report",
            #"accept" = "*/*",
            #"Accept" = "application/json"
        ]
    ]),

    // Parse JSON as needed if required.
    jsonResponse = Json.Document(rawResponse),
    resultsArray = jsonResponse[results],

    resultsTable = Table.FromList(resultsArray, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedTable = Table.ExpandRecordColumn(resultsTable, "Column1", {
        "countall_casd_chg_total_change",
        "casd_chg_category_symbol"
    }, {
        "Count",
        "Category"
    })
in
    expandedTable

 

Note: Replace the following items with the values from your environment:

7.a: Replace "http://<SDM hostname>:<Report API port>/CASMReports/reports/v1/access" with your SDM hostname where the CASM Report API is running and the Tomcat port being used to deploy this API.

7.b: Replace "<BASE 64 encoded username+password>"  with the Base 64 code for the username and password that is generated via Swagger or some other external tool to encode to Base 64.

7.c: Replace "http://<SDM hostname>:<Report API port>/CASMReports/reports/v1/getReport" with your SDM hostname where the CASM Report API is running and the Tomcat port being used to deploy this API.

 

8. Copy the updated code and paste it into the Advanced Editor | Click on the "Close & Apply" button

9. Now you'll see the GetReportDataByCode query under Data

10. Create the report.

 

Example 1: Click on Table under Visualizations | Click on the checkbox next to the items you want to add to the table.

 

Example 2: Click on Pie Chart under Visualizations | Click on the checkbox next to the items you want to add to the Chart.