Reporting for PowerBI - Helpdesk view
- Reporting for PowerBI - Helpdesk view
- Reporting for PowerBI with query phrasebook
This extension provides new queries phrasebook on iTop.
The report template on Microsoft Power BI Desktop uses these
iTop queries to display the helpdesk activity of your service.
It is provided in English. You will be able to track and share the daily workload of your helpdesk.
Microsoft Power BI Desktop software is free, and it can help you analyze your ITSM iTop activity.
The purpose of this hybrid extension is to show :
sample reports you can create linked with iTop,
potential time savings for operational meetings.
Important: this extension should be seen as an example of the
reporting possibilities. It is a primer to help you.
You must use an Administrator profile to run the PowerBI report template (as it requests information from the history tables).
The Power BI template is based on the English label attributes of iTop. If you change the label, you will need to change the report template on Power BI Desktop.
Keep in mind that Microsoft Power BI Desktop is an external software and for any questions regarding its use and functionality, please consult its documentation directly at website microsoft. The Combodo helpdesk does not provide support or help on Microsoft PowerBI.
iTop 2.7 or higher with the Simple Ticket module installed (or with the User Request Management or Incident Management modules installed).
Microsoft Power BI Desktop version 2.100.1182.0 64-bit (December 2021) or higher.
It is recommended to run the Power BI report against an iTop
server other than the production one.
If you are using your production server, it is recommended that you do not run your report during the day. In this case, you must use the Service Power BI and not Power BI Desktop application.
For iTop extension, use the Standard installation process.
For Microsoft Power BI Desktop, you can download the installation file on website Microsoft Power BI.
Configuration on iTop
Create an user on iTOP
To prepare the connection from Power BI, you need to create a dedicated English speaking “iTop user” on your iTop instance.
The user must have Bulk Read permission on the Team, Ticket and CMDBChange(history) classes. Currently, only administrator profile can bulk read history on iTop.
You can therefore create a user “power_bi_user” with the Administrator profile and configure the English language for this user.
Query phrasebook on iTop
On iTop, three queries phrasebook will be created by the extension.
In the Administration > Query phrasebook
menu, search query phrasebook whose name contains
On each query, you will find the details of the OQL queries, the exported fields and the url to use with the webservice. You will link this url with the Power BI report.
Check that your profile and the export of your data to iTop are correct
On iTop, copy the url of “UserRequest updated” from the OQL
Query and paste it on a new private browser web page, log
in with your Power BI user and password and check that
your data is not empty.
Do the same for “List team name” and “The first team dispatched on Tickets” from the OQL Query.
The column headers displayed on the export are the labels of the
fields used in the Power BI report.
You can check your labels on each export. If you have any differences, you will need to modify the model on Power BI Desktop.
Column headers on “UserRequest updated” :
id (Primary Key), Operational status, Status, Ref, Organization, Organization Name, Caller, Caller Name Team, Team, Agent, Agent Name, Impact, Urgency, Priority, Origin, Request Type, Start date (date), Start date (time), End date (date), End date (time), Last update (date), Last update (time), Assignment date (date), Assignment date (time), Resolution date (date), Resolution date (time), Last pending date (date), Last pending date (time), SLA tto passed, SLA ttr passed, Resolution delay, Resolution code, TTO Deadline, TTR Deadline, Service name
Column headers on “List team name” :
id (Primary Key), Name
Column headers on “The first team dispatched on Tickets” :
Configuration on Microsoft Power BI Desktop
Connection parameters between iTOP and Power BI
The connection from PowerBI to iTop is a web connection according to the iTop webservice.
Open the file Power BI template on PowerBI.
A window appears to configure the connection settings with your iTOP.
After the configuration, reports will be synchronized with your iTOP.
|user_login||Login of the iTop user we configured above||power_bi_user|
|user_password||Password of the iTop user we configured above||password of power_bi_user|
|url_user_request_itop||The web url of the iTop query of user requests updated in the last 12 months. Retrieve from your iTop the id of the corresponding query phrasebook loaded by the extension.||https:/itop_power_bi/webservices/export-v2.php?format=spreadsheet&login_mode=basic&date_format=Y-m-d+H%3Ai%3As&query=26|
|url_list_team_name_itop||The web url of the iTop query of list teams' name. Retrieve from your iTop the id of the corresponding query phrasebook loaded by the extension.||https:/itop_power_bi/webservices/export-v2.php?format=spreadsheet&login_mode=basic&date_format=Y-m-d+H%3Ai%3As&query=27|
|url_list_the_first_team_dispatched_itop||The web url of the iTop query of the list of the first teams dispatched on the tickets updated in the last 12 months. Retrieve from your iTop the id of the corresponding query phrasebook loaded by extension.||https:/itop_power_bi/webservices/export-v2.php?format=spreadsheet&login_mode=basic&date_format=Y-m-d+H%3Ai%3As&query=28|
When all ids are set, select the anonymous method and click 'Connect':
Save your report
The report is updated, you can navigate through tabs and adjust
Save your report with your configuration : File → Save → Choose directory and name file .
Share your report on pdf format
Publish your report online
You can create an online account on Power BI Service plateform
and publish your report.
Depending on the options, this is a fee service. For more information, please visit the websiteMicrosoft Power BI
View all tickets with their current operational status (neither
resolved nor closed). This is a day-by-day view of your support
Capacity management tickets
View the capacity of your teams by comparing incoming tickets to
the tickets your teams can handle.
This is an annual view.
This view also shows the teams that resolve tickets during this period.
Monthly view - Created tickets
Monthly view - Updated tickets
Monthly view - Resolved tickets
View tickets according to their resolution date. They are sorted
by month, team, department.
The average time resolution is calculated on the resolution
delay attribute of iTop.
Weekly view - Created tickets
View tickets by date of creation. They are sorted by time, day,
The report allows you to determine the critical time and day for
Weekly view - Updated tickets
View tickets according to their last update date and the
This report provides a view of updated tickets, sorted by
support agent, by day.
You can also compare open tickets with solved tickets over a day.
You can focus on specific team.
Teams view - Updated tickets
Teams view - Resolved tickets
View the closed and resolved tickets.
You can select the first team dispatched to the ticket.
Then you will be able to see the number of tickets initially dispatched to your team but no resolved by them.
Questions & answers
Problem connecting to iTop with Power BI Desktop
Configure your connection on PowerBI in Anonyme Connection if you have an error message.
In Power BI Desktop, on the File tab, select Options and settings > Data source settings.
In the Data source settings dialog box, select Global permissions, choose the website on which you want to change the permission setting, and select Edit Permissions.
In the Edit Permissions dialog box, under Credentials, select Edit.
Edit the credentials in Anonymous, select Save, and select OK.
Refer Microsoft documentation Authentication with a data source
Error message on Power BI Desktop - the Ref column in the table was not found
This message is displayed, if you do not have a row on the first UserRequest table
You have to check your profile and your data export
Error message on Power BI Desktop because a english label of attribut iTOP has been modify
You made light modification on english label on iTop This message is displayed, if you do not have a row on the first UserRequest table
Example : on UserRequest the attribute “time spend” has been rename with the label “Time to Resolution” (origin label was “Resolution delay”).
On PowerBI template, you will have the error “The column Resolution delay of the table wasn't fount.”
For resolve this problem, Close the Refresh windows on PowerBI Clic on Menu Home / Transform data
On Queries UserRequest, you will have error above
Open “Advanced Editor”
Search the origin label “Resolution delay” and change with new label “Time to resolution”
After, clic on “Done”, “Refresh” and “Close and Apply”
Using report template PowerBI without admin profil
The admin profil is necessary for export data of history iTOP and serach the first team dispatched(class CMDBChange).
But, it's possible to use an user with a profil not admin but that allow read Query Phrasebook class. For example, if you have the “Admin tools delegation” extension, you could create an user with profil “Query manager”.
This profile will be run the query UserRequest and Team but not the query “The first team dispatched”. Also, in the PowerBI parameters, you could leave empty third parameters url “url_list_the_first_team_dispatched_itop”. Therefore the sheet “Teams view - Resolved or closed tickets” on report Power PI will be empty.