RangerMSP Business Automation for successful ITs

July 29th, 2017, 04:36 PM
Craig Corfield
Posts: 25
Has anyone done a Power BI report from CommitCRM?

I have gotten incredibly far with the Power BI reporting for CommitCRM (Set up connection, set up gateway, done a ton of DAX calculations for tables and measures, done the linking for most database tables etc.) Just interested if there is anyone who has done this yet and someone i could work with on this?

I have mostly focused on tickets and Accounts and Assets and Charges. Also integrated a third party survey tool. Would love to work with someone on getting this working fully as this will easily make the reporting of CommitCRM better than anything else out there currently.
August 8th, 2017, 10:12 AM
Stanley Ng
Posts: 89
Keen to know how you did it..with Power BI :)
August 9th, 2017, 02:22 PM
Craig Corfield
Posts: 25
I have completed the Accounts and Tickets reporting as well as the employees for the tickets as well. Still working on getting feedback on this first. Once thats done will put up a screenshot.
September 15th, 2017, 05:10 AM
Posts: 6
I'd be very keen to see this.
February 10th, 2018, 05:21 AM
Posts: 26
hi, i am interested to try out connecting my powerbi to the commitcrm sql database. what would be the connection parameters for me to perform the connection?

is there any kb or info on the process of making the connection?
February 10th, 2018, 10:13 AM
Craig Corfield
Posts: 25
You would create a ODBC connection into the DB. We have perfected the reporting and would highly recommend using power BI.
April 11th, 2018, 10:10 AM
Posts: 26
Hi Craig,
Apologies for being such a newbie on this.
I installed powerBi, and i tried making an odbc connection, but what parameters do i use? like what db do i connect to?
Since i am using SQL on the CommitCRM db, which sql server option should I be using as I am not using the Ms SQL for CommitCRM db?
What username and password should I be using to connect to the SQL DB?
April 11th, 2018, 10:16 AM
Posts: 26
i manage to identify that I am using sybase SQL db, so that's a start. will be tinkering with it further.
April 12th, 2018, 01:57 AM
Craig Corfield
Posts: 25
Hi Bridgesint,

I would highly recommend you map the drive of your CommitCRM server as well. This is essential for the odbc connection.

August 10th, 2018, 06:10 AM
Stanley Ng
Posts: 89
Hi @Craig,

Can you share with us snapshot of your reporting?
Thank You
August 12th, 2018, 02:00 PM
Craig Corfield
Posts: 25
Ill have to block out the sensitive information but here is what the technicians power BI report looks like. We have one for sales as well as for the customers. Also using it for KPI and surveys.
Attached Images
File Type: jpg Tickets.jpg (140.5 KB, 170 views)
File Type: jpg Charges.jpg (135.9 KB, 159 views)
August 12th, 2018, 02:01 PM
Craig Corfield
Posts: 25
This is for the contracts and assets for technicians
Attached Images
File Type: jpg Contracts.jpg (125.7 KB, 153 views)
File Type: jpg Assets.jpg (137.8 KB, 148 views)
August 14th, 2018, 02:23 AM
Posts: 1
Hi I am a software developer. I can provide help regarding software development
October 11th, 2018, 10:03 AM
Posts: 26
hi Craig, thanks for the tips. I didn't do mapping to the mapped drive. I did the odbc connection and stuff.

here are the steps to help other people if they are keen to try -

Based on here - [url=/wiki/API_Developers_Guide#API_Reference_Manual[/urlmp]

Download the advantagedb ODBC connector
since i am running a 32-bit server, my AdvantageSQL Version is 32-bit.
I need to download a 32-bit connector
and I need a 32-bit Microsoft PowerBI as well

during ODBC conneciton, follow the guide, and take note that its -
- Important to not tick the Data Dictionary and not tick the Local and Internet servers

in addition, i had to - Copy the ads.ini from the server \\(myserver)\commitcrm\server into C:\Windows\System32

i go to powerbi and connect to the odbc connection and open -
the 3x main categories - cards, tickets and slips.

Side point Craig, your dashboard is so-so-so AWESOME!

are you able to share a bit on what are the steps to create some of it or where to start? I'd like to build something similar but i don't know where to start... for one, I'd like to see the hours use by different clients for the past 3 months or YTD...
November 23rd, 2018, 10:19 AM
Posts: 17
Hi everyone,

I'm a bit of a Power Bi noob but this looks very useful. I would be grateful if someone could do a guide on how to use these tables once in Power Bi, link them and query them etc.

Any help or advise you can offer would be appreciated!!!

Thanks in advance
April 18th, 2019, 02:17 AM
Posts: 17
Hi all,

After some work and help from the CommitCRM team I now have some working Power Bi reports. We have uploaded these and they run on a schedule and email out, the problem I have now is that I have been using the "on-premise personal gateway" I have now loaded the "on-premise data gateway" for servers but cannot seem to get it to connect is anyone else having the same issue?

Thanks in advance for your help.
April 18th, 2019, 05:48 AM
Craig Corfield
Posts: 25
Are you using a system DSN ODBC connection?
April 18th, 2019, 05:57 AM
Posts: 17
Hi Craig, yes I've setup a dedicated sever with the ODBC driver installed and a System DSN ODBC connection setup. I have installed the "on-premise data gateway" and signed in with the correct user to link to the Power Bi service. I get the below error when setting up the Data Source on the Power Bi side:
"Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance.""

Thanks again for your help!
March 7th, 2021, 11:44 PM
Posts: 3
can anyone share the connections in powerBi between tables?
March 8th, 2021, 12:57 AM
Posts: 17
Hi eliavbaruch,

I'm not sure why but for some reason people are unwilling to share their progress or how they get this working.

Having said that, I have attached the relationships from a couple of my reports, I hope this helps.

Attached Images
File Type: png PowerBi relationships.png (18.2 KB, 37 views)
March 8th, 2021, 06:24 AM
Support Team
Posts: 7,432
Graeme, thank you for sharing this information!

@eliavbaruch - this article contains information about major tables and fields in the application.

Although the relationships are not shown there, it should be straightforward to get them - based on the field name.
For example:

Account record in various tables:

Cards.RECID = Tickets.CARDID = Slips.CARDID = NoteBook.CARDID

Please feel free to post here or email us in case you have any questions.

Hope this helps.
March 9th, 2021, 12:00 AM
Posts: 3
@Grtaeme-ACT thank you so very much!
i'll share my work in a different post when i'll finish our dashboard.

if it's ok with you, I'll be thankful for future assist if need be from you.

thanks for sharing
March 9th, 2021, 12:07 AM
Posts: 3
@Support Team
the link is broken
March 9th, 2021, 06:03 AM
Support Team
Posts: 7,432
The link was fixed in the original post and here it is again. Thank you for noticing.
April 7th, 2021, 08:19 AM
Posts: 5
We're trying to pull data into BrightGauge, can anyone share a list of actual SQL queries they are using to pull various reports mentioned here?

Thread Tools Search this Thread
Search this Thread:

Advanced Search

All times are GMT -6. The time now is 12:29 PM.

Archive - Top    

RangerMSP - A PSA software designed for MSPs and IT Services Providers
Forum Software Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.