Sidebar

Combodo

iTop Extensions

Reporting for PowerBI - Helpdesk view

name:
Reporting for PowerBI - Helpdesk view
description:
Reporting for PowerBI with query phrasebook
version:
1.0.1
release:
2022-11-18
itop-version-min:
2.7
code:
combodo-powerbi-integration
state:
Stable

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.

Features

The purpose of this hybrid extension is to show :

  • sample reports you can create linked with iTop,
  • potential time savings for operational meetings.

Revision History

Version Release Date Comments
1.0.0 2022-08-29 First version

Limitations

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.

Requirements

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.

Installation

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 “powerbi”.

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” :
newvalue, objkey

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.

Parameters Configuration

Field Meaning Sample Value
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

To retrieve the id, open the 'Query phrasebook Menu', click on the queries starting by “PowerBI extension” and copy their id:

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 filters.

Save your report with your configuration : File → Save → Choose directory and name file .

Share your report on pdf format

Menu File → Export → Export to pdf

Your pdf file is opened in pdf application, save your pdf file.

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

Usage

Ongoing tickets

View all tickets with their current operational status (neither resolved nor closed). This is a day-by-day view of your support activity.

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

View tickets by date of creation. They are sorted by month, customer, type, origin and priority.

Monthly view - Updated tickets

View tickets according to when they were last updated. They are sorted by month, team, priority.

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, week.

The report allows you to determine the critical time and day for your helpdesk.

Weekly view - Updated tickets

View tickets according to their last update date and the dispatched's team.

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

View the team's tickets.

You will see tickets per service and per agent.

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

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.
extensions/combodo-powerbi-integration.txt · Last modified: 2022/11/21 09:29 (external edit)
Back to top
Contact us