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).
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).
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
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.
STEP 4: Power BI work area dissected:
STEP 5: Click Edit Queries from Task bar to translate related entities unique identifiers into names as shown below:
- Translate related records into names.
- Repeat above step for other related entities required for the report.
- Apply filters (if required).
- Final entity:
Note: Remove Columns for cleaner dataset.
STEP 6: Once completed, editing queries, Close & Apply changes to the dataset.
STEP 7: Click Relationships and connect the entities using respective Unique Identifiers.
STEP 8: Select the required visualisation. In this case, I selected “Matrix” > drag and drop fields in the appropriate areas as shown below
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.
STEP 10: Formatting options are available under Visualizations.
Final formatted report after removing Totals and applying some colours.
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.
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. 🙂
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.