Create a Power BI Report From Data Loaded by Service Manager REST API
IntegrationIntroduction
You can integrate third-party systems with 
in many ways. The method you select will depend on business requirements, architectural and security constraints as well as the characteristics specific to each third-party application or service.
This document describes an integration process that has already been implemented in a customer context.
To find out more about this integration (e.g. scripts to be used), please contact the 
Consulting & Professional Services team, the Support team, or your service provider and integrator.
- Consequently, you should schedule the execution of Power BI reports when users do not need to access Service Manager, i.e. at night or during weekends.
Should you need more information, see How to configure Power BI report scheduled refresh - If you work in an international organization with different service hours for each country, you should run Power BI reports on a dedicated Report server, rather than on the production environment. Should you need more details, please contact your EasyVista consultant.
Overview of the integration
This integration enables you to create a Power BI report using data retrieved by the Service Manager REST API.
You do this in two main steps:
- Extract data from Service Manager in JSON format using the Execute an internal query method of the REST API.
- Build a Power BI report using this data.
| Integration Data Feed (direction) | Authentication Type | Integration Connector Options | ||
|---|---|---|---|---|
|
|
|
| Available versions | ||
|---|---|---|
| SaaS-based | On-premises | |
| Service Manager |  |
|
| Power BI Desktop | |
N/A |
Example
Create a Power BI report displaying the list of late incidents
- The list of late incidents is displayed in List mode in Service Manager.
- Data in JSON format is retrieved in Power BI Desktop using the Execute an internal query method of the Service Manager REST API.
- The Power BI report is created.
| Service Manager | Power BI report |
|---|---|
|
|
Step-by-Step Integration Process
See:
Prerequisites
- You must have installed Microsoft Power BI Desktop on your workstation. You can download it free of charge.
- You must have the URL of the Service Manager REST API as this is used to retrieve the list of records in JSON format.
ProcedureObtainURL
How to quickly obtain Service Manager reporting data in JSON format?
- Open a screen in List mode.
- Click
in the top banner. - Select the Rest API Url option.
The URL of the active page is generated and copied to the clipboard as follows:
Create a Power BI report using data retrieved by the Service Manager
REST API
Step 1: Retrieve Service Manager data in Power BI.
1. Run Power BI Desktop on your workstation.
2. Click Get data and select the Web data source.
3. Copy and paste the URL of the Service Manager REST API. This is used to retrieve the list of records in JSON format. Next, click OK.
4. In the Basic tab, specify the credentials for accessing the Service Manager REST API, i.e. user name and password, and click Connect.
- The query associated with the REST API URL will be run.
- Power Query Editor will open and data will be imported.
- HREF refers to the URL run.
- recordcount refers to the number of records returned by the query.
- The records object is used to access the list of records.
5. Click List in the records object.
The list of Service Manager records will appear.
Step 2: Create the Power BI report in Power Query Editor.
1. Select Convert > To Table to create a table using the list of records.
The table will be generated and will display one row for each record.
2. Click 
to display the fields retrieved by the REST API and select the ones you want to display in the Power BI report.
Untick the Use original column name as prefix option if you only want to display labels in the report. If you tick this option, each field name will be preceded by the column name, Column1.
The Power BI report will be generated using the selected columns.
3. To find out how to format the data table, see the relevant Power BI documentation.
Step 3: Save the Power BI report.
1. Select File > Close & Apply in the menu.
- Power Query Editor will close.
- You will return to Power BI Desktop.
- Data will be imported.Note: If you applied a filter to data, only the selected data will be imported.
2. Tick the fields to be displayed in the report in the order in which they should appear.
The report will appear.
3. Select File > Save to save the report.
A PBIX file will be generated.