Power BI

[Craig Corfield]

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.

[Stanley Ng]

Keen to know how you did it..with Power BI :)

[Craig Corfield]

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.

[networkn]

I'd be very keen to see this.

[bridgesint]

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?

[Craig Corfield]

You would create a ODBC connection into the DB. We have perfected the reporting and would highly recommend using power BI.

[bridgesint]

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?

[bridgesint]

i manage to identify that I am using sybase SQL db, so that's a start. will be tinkering with it further.

[Craig Corfield]

Hi Bridgesint,

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

Craig

[Stanley Ng]

Hi @Craig,

Can you share with us snapshot of your reporting?
Thank You

[Craig Corfield]

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.

[Craig Corfield]

This is for the contracts and assets for technicians

[KelleyKilback]

Hi I am a software developer. I can provide help regarding software development

[bridgesint]

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

[Grtaeme-ACT]

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

[Grtaeme-ACT]

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.

[Craig Corfield]

Are you using a system DSN ODBC connection?

[Grtaeme-ACT]

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!

[eliavbaruch]

can anyone share the connections in powerBi between tables?

[Grtaeme-ACT]

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.

Graeme

[Support Team]

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.

[eliavbaruch]

@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

[eliavbaruch]

@Support Team
the link is broken

[Support Team]

The link was fixed in the original post and here it is again. Thank you for noticing.

[jasonBTT]

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?