Skip to main content
Solved

Connect Power Bi to FreshService (Growth plan)

rolevc
Contributor
Forum|alt.badge.img+1

Hi everyone,

 

how can I connect my Power BI desktop app to the tickets information on FreshService? I want to create custom metrics and dashboards and check them directly. Currently I can export a file manually (as Growth doesn’t allow to schedule and so I cannot get an API URL) and connect directly and do the updates.

However, I would love the option to just have a direct connection to it, check real time (almost) for metrics and dashboard at any time. I have a Power BI Pro license and FreshService has been our ticketing system for over a year now.

I know this is possible with FreshDesk, but with FreshService I have been unable to find a solution using some articles on this group.

 

There are some third party services, but those are static dashboards or is pricey. We want to be able to create a direct connection and produce the exact metrics that our director needs.

 

thank you!

Best answer by rsimmers

Hi @rolevc,

 

If you are trying to get scheduled data out of Freshservice, consider using Powershell.  We are a Freshservice Partner and built a free Powershell library to import and export data into Freshservice.  Only a couple of minutes to setup and start using:

 

https://www.flycastpartners.com/freshserviceps

 

It allows you to use simple commands to get data, for instance to get tickets created after January 1st:

$tickets = Get-FSTicket -Filter "created_at:>'2024-01-01'"
$tickets | Export-Csv -Path C:\PowerBi\tickets.csv -NoTypeInformation

The filters can be found in the documentation (Service Desk API for Developers | Freshservice). This could be scheduled (Windows Scheduled Task) to be run as often as required and point PowerBi to the CSV.  If you prefer to store data in SQL, that can be accomplished with a little more work.  We have many customers use this option to have more flexibility because PowerBi would need to get the entire dataset cached whereas you can perform incremental operations using a client-side scripting solution.

 

Keep in mind that the data in a ticket isn’t flat file (e.g. CSV) ready depending on the data you are trying to capture.  If it is an array, an object\hash (like custom objects), you need to do a bit of work to flatten the data before it can be exported.  Below, you can see fwd_emails, reply_cc_emails, cc_emails, and the custom_fields can be easily exported to CSV:

 

The export will just show those items as System.Object[] in a CSV export, but if there is something specific we can assist here if you need.

View original
Did this topic help you find an answer to your question?

2 replies

rolevc
Contributor
Forum|alt.badge.img+1
  • rolevc
  • Author
  • Contributor
  • 6 replies
  • February 5, 2024

I was reading wrong the instructions:

All I had to do is get the actual company API URL, connect with my API (no password!) and start getting the fields. However, I still need to create queries to get the information on a more proper way (similar to when you export a report - however, our Growth version doesn’t have that option)

 

thank you!

R
Community Debut
  • rsimmers
  • Community Debut
  • 2 replies
  • Answer
  • February 5, 2024

Hi @rolevc,

 

If you are trying to get scheduled data out of Freshservice, consider using Powershell.  We are a Freshservice Partner and built a free Powershell library to import and export data into Freshservice.  Only a couple of minutes to setup and start using:

 

https://www.flycastpartners.com/freshserviceps

 

It allows you to use simple commands to get data, for instance to get tickets created after January 1st:

$tickets = Get-FSTicket -Filter "created_at:>'2024-01-01'"
$tickets | Export-Csv -Path C:\PowerBi\tickets.csv -NoTypeInformation

The filters can be found in the documentation (Service Desk API for Developers | Freshservice). This could be scheduled (Windows Scheduled Task) to be run as often as required and point PowerBi to the CSV.  If you prefer to store data in SQL, that can be accomplished with a little more work.  We have many customers use this option to have more flexibility because PowerBi would need to get the entire dataset cached whereas you can perform incremental operations using a client-side scripting solution.

 

Keep in mind that the data in a ticket isn’t flat file (e.g. CSV) ready depending on the data you are trying to capture.  If it is an array, an object\hash (like custom objects), you need to do a bit of work to flatten the data before it can be exported.  Below, you can see fwd_emails, reply_cc_emails, cc_emails, and the custom_fields can be easily exported to CSV:

 

The export will just show those items as System.Object[] in a CSV export, but if there is something specific we can assist here if you need.

rolevc
1 person likes this
  • rolevc
    rolevc

Reply


Most helpful members this week

Terms of UsePrivacy Notice