a very useful way to allow users to meet their own reporting needs.
Zen Desk is doing this very successfully.
@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
@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!
@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.
Is anyone using this to get the time tracking information? I can get total time on the tickets but cannot get time tracked by agent. Seems that is in a different API or something.
Something as critical as scheduled exports, time tracking per agent, etc seems like a huge miss for Fresh Service on their reporting and/or analytics.
@Justin, I am not using this but the v1 api allows for surfacing this information.
If you are using powerBI it should be easy to putt this data out.
@Kenny Chu: I had written some detailed instructions on how to do this, but then just ended up automating it, so please download the latest template from GitHub.
For groups, I don't use these much for reporting, but I have added a column called 'Group'. You can play with this on the second tab or add it to other reports.
In order to pull Group, Department and Agent info, I had to revert to using the old V1 API, so this may break at some future point if it gets disabled.
@Justin Fox: Following from @Patrick's response, if you open the query editor and take a look at the way I have pulled 'Agent', 'Department' and 'Group' data via the v1 API, you may be able to add in the funtionality you are looking for. Otherwise, reply back in mid September and I will see if I can do this.
Thank Adam for the update. This is really helpful.
Finally I managed to retrieve the Agent / Department / Group from the FS and imported into Power BI.
Sort of having a basic reporting with customization on the Power BI now. Further feature that I am now look into are the "Re-Open" case count and "User Satisfaction" but I don't think they have related API on V1 and V2 now.
let me know if you have any experiences on that. Thank you.
Adam, I am just now getting back to this but am having trouble getting authentication to work. We use SSO and I noticed your readme says (not SSO details if used). Honestly, this has been my issue all along. I can't get PowerBI to authenticate with my FreshService URL no matter what I do.
If you use SSO to login, you should still be able to access your helpdesk by going to https:/XXXX.freshservice.com/support/login
From here you should be able to confirm your 'non-SSO' login details or at least reset them.
Alternatively, if you are feeling adventurous, you could try and set up authentication using an API Key, I confident this works as I have seen other Power BI queries use it. You would need to edit the queries in the editor to accomplish this. If you do do this, it would be good to share the file via Github Project Page so we can incorporate it into the main pbix file for others to use.