Dynamics CRM Custom Report through Power BI

Hesam Seyed Mousavi, September 30, 2016

hesam_seyed_mousavi_dynamics_crm_power-bi-custom-visuals

Source: dynamicscrmcoe

blog.mousavi.fr
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…

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.

pic1

STEP 4: Power BI work area dissected:

pwer-bi-work-area-pic2

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

edit-queries-pic3

Translate related records into names.

name-value-for-entity-pic4

name-attribute-pic5

related-entity-record-converted-into-name-pic6

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

apply-filters-pic7

final-entity-pic8

Note: Remove Columns for cleaner dataset.

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

close-and-apply-query-changes-pic9

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

specify-relationships-pic10

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

matrix-pic11

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

Final formatted report after removing Totals and applying some colours.

final-formatted-report-pic14

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.

Source: dynamicscrmcoe

blog.mousavi.fr

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s