Start a new topic

Microsoft Power BI intigration

a very useful way to allow users to meet their own reporting needs.

Zen Desk is doing this very successfully.


38 people like this idea

@Patrick: Re your first question about querying the number of pages, I haven't managed to figure out an elegant way to do this yet. The FreshService API (v2) doesn't appear to return a variable for the number of pages. But it does eventually indicate that there is 'no more pages' via the 'link header'. As I am still learning the Power BI 'm query' language, I have not managed to leverage this yet, however, it would need to be triggered in the middle of the run, rather than setting 'X' pages at the beginning of the run.

With respect to publishing a PBIX file, I have attached a template which should work once configured. Hopefully this doesn't lead to a bunch of people hammering the API servers, but I have included an allowance to limit the number of tickets retrieved. In practice, this only limits the number of pages requested, users will need to ensure they don't refresh too many times per hour, or try to retrieve too many tickets. Eg to retrieve 5000 tickets will be 50 pages @ 3 API credits per page = 150 credits. Thus an entry level plan (Sprout) can easily pull up to 30,000 tickets per hour assuming they aren't using the API elsewhere.

This is by no means a professionally developed file, but it suits my purposes and may work for others. It is provided 'as is', and I am not in a position to support it I'm afraid. That being said, if people improve it, please share it back! Just be sure to save as a template as this stops your data & credentials going with it.

Instructions for FreshService.pbit template file

  1. Open the File and Enter url, DateFrom & MaxTickets to retrieve. Note if you try to retrieve more tickets than are available in your date range, you will get an error. Ensure your 'Max Tickets' is less than the number of tickets in your range. I suggest you start with the defaults for testing.
  2. After clicking Load, you will likely get a 'Refresh' Error. You will need to input your credentials. → Click 'Close', then click 'Refresh'.
  3. You will be prompted for credentials. Click 'Basic' then enter your login details for (not SSO details if used) image
  4. You may need to play with filters etc to get tickets to show.
  5. You should also populate 'Agents' and 'Departments' tables in the Query Editor if you need these.
  6. You can also adjust the Papramaters (URL, DateFrom, MaxTickets) in the query editor.


2 people like this

@Adam, This is very helpful. Thank you very much. I am sure many more people would be interested.

I am very happy to share any of my work developed from this file. I am thinking that a Github project should probably be created for this. I would probably just fork the project and do work from the fork.

Again this is really cool stuff. Thanks again. 

@Patrick, I'm glad you may find it useful. I  did consider GitHub, but assumed it wouldn't be ideal as the template doesn't have human readable code. However, I have followed your advice and created a repo. Good luck!

1 person likes this

@Adam, thank you very much for providing guidance. I am wondering how I could get the "Agents" and "Group" to be populated into Power BI as well if you could provide some hints on it.

Thank you

Login or Signup to post a comment