CRM Service Reporting - Best Practices?

[karenlp97]

Hi all,

I am new to CRM, but less new to report-building and Power BI. Our team uses CRM Online as a Incident Ticketing exclusively (no sales or opportunities). I am tasked with reporting out certain metrics to the team.

Since I am more comfortable in PowerPivot land (for now), I am working in Excel and using Power Query to connect to the CRM Odata feed and pulling in the IncidentSet, EmailSet, and QueueItemSet tables.

I have to say, this has been a really frustrating experience - I find myself spending literally hours expanding tables and records and digging around to find what I think should be surfaced top-level (e.g. Categories and SubCategories, Case Status Reason). And then when I'm done, usually my Power Query is 10-levels deep in steps and it takes for-EVER for the data to refresh.

I am getting desperate for some SME help on how to report this stuff out. I have looked and looked but cannot find a definitive guide to using Power BI to report on Service Cases.

Specifically I am scratching my head trying to figure out:

• Total time from when a case is created to marked as resolved
• Where are my case statuses? (Status Reason in CRM)
• Is there a specifc place I should be looking for case categories? We have a 3-level deep subject tree in CRM and I can only find one Category level in the Odata mess.

Thanks much in advance. I am completely open to a better way of doing this.

Karen

[liasa]

CRM reporting is one of the intgeral functions that need to be fulfilled for any platform to operate in an effective and efficient manner. Since you are new to CRM, I will suggest that you try and handle these operations related to CRM by means of mobile app that can very easily sync with the functionality of the e-commerce platform you are using. Here is an app you will definitely find useful: http://dynamics.folio3.com/dynamics-ax-sales-marketing-crm-app/

[Greg_Deckler]

I have a blog article on this, although not specifically for the Ticketing stuff. However it may have some helpful tips. For example, I created an Azure Data Marketplace feed that turns the various status codes into human readable things.

https://www.linkedin.com/pulse/building-ultimate-microsoft-crm-dashboard-under-hour-greg-deckler?trk...

You could also try the CRM Online content pack in the Service. It builds a dashboard focused on sales but the data model it creates already does most of the expansion and turning the esoteric codes into something useable. 

[karenlp97]

Hi and thanks for your reply. Yes I am well aware of your blog post and had hoped I could find something analogous for cases. I will review it again more closely for the status code decoding bit.

Sadly, the CRM Online Content Pack is pretty much useless to me since it doesn't pull in the IncidentSet or QueueItemSet tables. Also, I had tried to pull it in a couple of days ago, but it stopped loading for me - PBI says it fails to load the tables for some reason.

[Greg_Deckler]

Yeah, I am strugglin a bit with the CRM tenant upgrade to 2016. Not sure if this is the source of your problem also. Seems that the old OData url feed "works" but my models won't refresh that way, they now error out since going to 2016 in the CRM tenant. The new OData feed URL doesn't work with the content pack and I'm assuming that if I use the new OData feed URL I have to rebuild all of my models.

😞