Service Now Power BI Template

This Power BI template is used for loading Service Now records from the task table using Rest API. I used Alberto Colombo's blog post as a reference to create this template.

References:

• https://developer.servicenow.com/dev.do#!/reference/api/orlando/rest/c_TableAPI
• https://docs.servicenow.com/bundle/kingston-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html

Dependencies

Software	Dependency
Power BI Desktop	Template
Power BI Desktop	Theme

Glossary of Terms

Term	Meaning
Rest API	A REST API (also known as RESTful API) is an application programming interface (API or web API) that conforms to the constraints of REST architectural style and allows for interaction with RESTful web services. REST stands for representational state transfer and was created by computer scientist Roy Fielding.
Service Now	A ticketing tool that processes and catalogs customer service requests. You can raise requests that deal with incidents, changes, problems, and other services.

Functionality

This message is because the template was created in Power BI Desktop for Report Server, but it can be used in either RS or Service.

This template requires the user to enter parameters as per the following screen.

• servicenow_url = https://YOUR_COMPANY_NAME.service-now.com/
• assignment_group = Only one assignment group e.g. "Service Desk"

• closed_date_offset = Used to offset the closed date updated by the system so it better reflects when the status is updated to a completed/closed status. I find 3 works well.
• domain_name = Used to return a list of users from Active Directory

The first connection will require some credentials: you can use your local ServiceNow credentials or a service account

Rest API call examples

Number of months:

= Json.Document(Web.Contents(servicenow_url & "/api/now/table/task?
sysparm_display_value=true
&sysparm_query=sys_class_nameINsc_req_item,incident
^sys_created_onONLast%20" & number_of_months & "%20months
%40javascript%3Ags.beginningOfLast" & number_of_months & "Months()
%40javascript%3Ags.endOfLast" & number_of_months & "Months()
&assignment_group=" & assignment_group & "
&numberISNOTEMPTY
&sysparm_exclude_reference_link=true
&sysparm_fields=sys_id
%2Cactive
%2Capproval_set
%2Cclosed_at
%2Cdue_date
%2Cnumber
%2Copened_at
%2Cshort_description
%2Csla_due
%2Csys_class_name
%2Csys_created_on
%2Csys_updated_on
%2Cu_service_area
%2Curgency
%2Cassignment_group
%2Cu_requestor
%2Cassigned_to
%2Cstate
%2Cu_affected_user"))

Between 2 dates:

= Json.Document(Web.Contents(servicenow_url & "/api/now/table/task?
sysparm_display_value=true
&sysparm_query=sys_class_nameINsc_req_item,incident
^sys_created_onBETWEEN
javascript:gs.dateGenerate('" & Date.ToText(start_date, "yyyy-MM-dd") & "','00:00:00')@
javascript:gs.dateGenerate('" & Date.ToText(end_date, "yyyy-MM-dd") & "','00:00:00')
&assignment_group=" & assignment_group & "
&numberISNOTEMPTY
&sysparm_exclude_reference_link=true
&sysparm_fields=sys_id
%2Cactive
%2Capproval_set
%2Cclosed_at
%2Cdue_date
%2Cnumber
%2Copened_at
%2Cshort_description
%2Csla_due
%2Csys_class_name
%2Csys_created_on
%2Csys_updated_on
%2Cu_service_area
%2Curgency
%2Cassignment_group
%2Cu_requestor
%2Cassigned_to
%2Cstate
%2Cu_affected_user"))