This guide is about how to connect ServiceNow and PowerBI via REST API, in order to build reports and publish them on PowerBI Service.
This way, we will be able to share ServiceNow information with our colleagues on Microsoft Teams, access from mobile and use all the opportunities PowerBI provides us.
Building the REST call
First, you need to build the REST call that will be made by PowerBI to get the data in ServiceNow.
To do this, we can use the Rest API Explorer tool to help us, which is already included in ServiceNow.
Furthermore, our user must have the “rest_api_explorer” role. If he does not, you can still design the call to an administrator user or with the correct role.
Then, we must select the ServiceNow table containing the data we are interested in (for example the incident table) and press “Sent”.
After loading, ServiceNow will show us the call (highlighted in blue in the screenshot) to provide to PowerBI
Here is a preview of the answer
At this step, you can apply filters, choose the fields to be shown in the body and many other options.
In particular, by choosing the fields you can use dot-walking to get information.
For example, in case you want to get the incident table records having only “number”, “opened_by” and “short_description” fields this will be the call:
And this the result:
The “opened_by” field is a reference to the users table, therefore to display the value shown to users you need to add the “sysparm_display_value” parameter to the call by setting it to true
This will be the call:
And the following result:
To avoid “opened_by” to be seen in PowerBI as a record, we can use the parameter “sysparm_exclude_reference_link”, by setting it to true, to make ServiceNow directly return the display value. Call:
You can also use dot walking to get information from reference fields, as shown with “opened_by”.
In case we want to get the email address of the user who opened the case, it will be enough to edit the part of the call where we select the fields to show.
It’s just going to have to be edited from this:
sysparm_fields=number,opened_by,short_description To this:
And once made the call:
This will be the response:
Connection to PowerBI
Once the call is ready, we can switch to PowerBI Desktop to enter the data source and create the report.
First you will need to create a new Data Source, Web type
Then enter the previous call created in ServiceNow
Important: remove the final parameter (&sysparm_limit=1) to avoid having a maximum limit of results
The first connection will require some credentials: you can use your local ServiceNow credentials
The resulting data will respect the user’s security (ACL and any business rule before query), so be careful and, if necessary, create a dedicated reporting user.
In order to make the data usable you need to:
- Convert the data into tables
- Expand the rows
- Expand the columns
- Press “Close and Apply”
Now PowerBI Desktop will have taken us back to the work screen and, on the right, the table and fields we imported from ServiceNow will appear
Now we can create the report, like the one here attached:
Once finished you can share it on PowerBI Service through the path “File” “Publish” “Publish to PowerBI”. If shared on “My Workspace”, it will be accessible via web and mobile. This way, you can share it with other colleagues and also via Microsoft Teams.