Reporting for PowerBI - Helpdesk view

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.

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

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.

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

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.

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':

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 .

Menu File → Export → Export to pdf

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

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

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.

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

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

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.

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.

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.

View the team's tickets.

You will see tickets per service and per agent.

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.

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

• 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

• 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”

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