Looking into the past of Microsoft Dynamics CRM (when it used to be installed in a box in one of the office rooms, and we had full access to the SQL Server) custom reports were built using “SELECT” statements in SQL. It was dreadful for Dynamics CRM developers to build queries involving complex PIVOT and UNPIVOT clauses.

Nowadays, when most of the deployments are in the cloud, and we have no idea where that Dynamics CRM box sits and no access to SQL Server, we cannot just use SELECT statements to query CRM data.

Designing custom reports through “SELECT” statements is still possible with its own limitations for on-premises deployment of Microsoft Dynamics CRM. However, as clearly specified in this MSDN article Developers guide to reports for Microsoft Dynamics CRM, specifically the following statement…

For security reasons, you cannot deploy custom SQL-based reports to Microsoft Dynamics CRM Online.

So what are the options for Microsoft Dynamics CRM Online?

Fetch-based reports only? Well, that’s not entirely correct. We have more than one option for querying CRM online data. And one of the options I am going to explore today is Power BI.

Power BI is a self-service business intelligence (BI) platform which can be used in several ways with Microsoft Dynamics CRM. (courtesy this TechNet article).

Sample Report

Let’s walk through a simple report example.

In one of my recent Dynamics CRM engagements, we were asked to deliver a comparison report between two surveys (Pre-Audit and Post-Audit) which are conducted at the beginning and the end of an Instance (Custom entity).

Report Configuration

Steps involved in configuring this report are as below:

STEP 1: Download Power BI Desktop here (it’s free) and install.

Power BI and CRM Connection

STEP 2: Launch Power BI desktop and follow the steps mentioned in the articles below for connecting Power BI to Microsoft Dynamics CRM Online

Connecting Power BI Desktop to Dynamics CRM Online

Microsoft Dynamics CRM content pack for Power BI

Report Dataset

STEP 3: Select entities required for querying in the Navigator. In this case, Posted Survey (cdi_postedsurveySet) and Survey Answers (cdi_surveyanswerSet) and click Load.

navigator

Figure 1: Select entities

 

STEP 4: Power BI work area dissected:

pwer bi work area

Figure 2:Power BI work area

 

STEP 5: Click Edit Queries from Task bar to translate related entities unique identifiers into names as shown below:

edit queries

Figure 3: Edit Queries

 

  1. Translate related records into names.
name value for entity

Figure 4: Get Name value for the entity

 

name attribute

Figure 5: Select Name attribute

 

related entity record converted into name

Figure 6: Related entity record converted into Name

 

  1. Repeat above step for other related entities required for the report.
  2. Apply filters (if required).
apply filters

Figure 7: Apply filters

 

  1. Final entity:
final entity

Figure 8: Edited entity

Note: Remove Columns for cleaner dataset.

 

STEP 6: Once completed, editing queries, Close & Apply changes to the dataset.

close and apply query changes

Figure 9: Close and Apply query changes

 

STEP 7: Click Relationships and connect the entities using respective Unique Identifiers.

specify relationships

Figure 10: Specify Relationships

 

STEP 8: Select the required visualisation. In this case, I selected “Matrix” > drag and drop fields in the appropriate areas as shown below

matrix

 

STEP 9: Report is now taking shape. However, there is a lot of unwanted data and the report is not formatted correctly. It’s not visually appealing.

Report Formatting

STEP 10: Formatting options are available under Visualizations.

formatting options

Figure 11: Turn off Totals

 

Final formatted report after removing Totals and applying some colours.

final formatted report - custom reports

As we can see above, questions and answers are compared for each instance record under Pre-Audit Organisational Survey and Post Audit Organisational Survey columns. Value of 1 demonstrates that the answer was given for that survey only, and value of blank shows the answer was skipped for that question.

CRM Security

Unfortunately, CRM security is not taken into consideration when viewing reports in Power BI built using Dynamics CRM datasets. Security structure has to be configured/customised in Power BI reports. For more details, please refer to the Blog Article published by Suresh Maurya, Row Level Security using Power BI.

Why not Fetch XML?

I guess I found it too difficult to Pivot and Unpivot the data using Fetch XML. It could be purely due to my unfamiliarity with Fetch XML, but feel free to leave your comments below for a Fetch XML solution of the above report. Always eager to learn. 🙂

Conclusion

I believe Power BI is extremely powerful both for Dynamics CRM customisers and End Users to build complex Dynamics CRM reports with ease. It allows better insights and anytime-any-device access because of its availability on cloud. It should act as your one stop shop for self-service BI.