Power BI Reports in MS CRM

Power BI for Microsoft Dynamics CRM Custom Reports

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.

Figure 1: Select entities

STEP 4: Power BI work area dissected:

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:

Figure 3: Edit Queries

1. Translate related records into names.

Figure 4: Get Name value for the entity

Figure 5: Select Name attribute

Figure 6: Related entity record converted into Name

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

Figure 7: Apply filters

4. Final entity:

Figure 8: Edited entity

Note: Remove Columns for cleaner dataset.

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

Figure 9: Close and Apply query changes

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

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

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.

Figure 11: Turn off Totals

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.

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.

Integrate POWER BI And Dynamics CRM

Steps to integrate Dynamics CRM Online and Power BI

As we know, POWER BI is very robust application, which helps to analyze Business Data. It can be a powerful visualization tool for Dynamics CRM users for analyzing their Business. Hence, Microsoft has introduced integration of POWER BI with Dynamics CRM from this 2016 release.

Here are the steps to integrate POWER BI with Dynamics CRM:

There are three Microsoft Dynamics CRM content packs for Power BI. Using this content pack user can analyze Sales, Service, and Marketing data.

1) Microsoft Dynamics CRM Online Sales Manager.

2) Microsoft Dynamics CRM Online Service Manager.

3) Microsoft Dynamics Marketing.

In this blog, we will see how to use Microsoft Dynamics CRM Online Sales Manager content pack of Power BI for connecting with CRM.

Steps using Power BI Web:

1) Register with Microsoft Power BI.

2) Once you Sign in with Power BI, click on Get Data button which is present on left bottom.

3) To select the “Microsoft Dynamics CRM Online Sales Manager” content pack, click on GET button inside Services tab.

4) Now select the “Microsoft Dynamics CRM Online Sales Manager” content pack.

5) Provide your Dynamics CRM organization URL and fiscal year end month number and click on next button.

6) Then select the Authentication method as OAuth2 and click on Sign in button.

7) Provide your Dynamics CRM Credentials.

8) After Successful Sign in process, you can see the Dashboard.

 Here you can see the Reports.

Steps using Power BI Desktop:

Download the Power BI Desktop application and install it.

1. Launch the Power BI Desktop application and sign in with Power BI Credentials.

2. Click on Get Data button from menu bar and select the “Dynamic CRM Online” service. Then click on Connect button.

3. Now copy the Dynamics CRM OData Service and click on Ok.

You will get this URL from CRM.

To get this URL from Dynamics CRM navigate to Setting-> Customization-> Developer Resources4. Now select Organizational account tab and click on Sign in button. Then provide the Dynamics CRM Credentials.

After signing in with Dynamics CRM credentials click on Connect button as shown in below screen.5. Select any data set from open window and click on Load button.

In screen shot below, we selected opportunities set.

Using this data set we can create dashboards or reports in Power BI desktop.6. Here you can create reports or dashboards using Dynamics CRM Data

Note: To use Power BI feature user will need subscription of Power BI.

Conclusion:

Power BI being a compelling data visualization tool, Dynamics CRM Online integration with Power BI is a noteworthy step taken by Microsoft. This blog gives a stepwise guideline required for connecting Power BI web and Power BI Desktop to Microsoft Dynamics CRM Online.

Completed with Dynamics CRM implementation? What’s next? Monitor User Adoption in Dynamics CRM. Try User Adoption Monitor.

Category: Dynamics CRM 2016 Tags: Dynamic CRM Online Integration with Power BI, integrate POWER BI with MSCRM, Power BI Dynamics CRM Online Connector, Power BI with Microsoft Dynamics CRM Online

Integrating Adobe Sign into MS Dynamics CRM

Complete installation and configuration instructions for a new implementation of the Adobe Sign product in to the Microsoft Dynamics CRM.

Overview

Adobe Sign offers a plug-and-play integration solution with Microsoft Dynamics CRM. This integration provides the following benefits:

• Accelerates the quote-to-cash process by sending agreements—such as contracts and sales documents—from Dynamics with one click.
• Automatically merges data from Dynamics entities—such as accounts and contacts— into agreements and pushes data gathered from signers during the signing process back to Dynamics.
• Makes it easy for recipients to sign anywhere, anytime, on any device.
• Makes it possible to track documents sent out for signature from within Dynamics.
• Eliminates manual steps across the entire process.

This integration is available for Microsoft Dynamics CRM Online and CRM

• 2013 Service Pack 1, Update 4
• 2015 Update 2
• 2016 
• Dynamics 365

Prerequisites

• Verify the correct userID (email address) is designated as an administrator in the Adobe Sign system
• Provide all the owned domains that your users could be using in their email addresses (Do not include public use domains like gmail.com, yahoo.com etc.)
• Obtain a list of all users in the Adobe Sign system using your owned domains
  • Once obtained, you may need to request valid users to be moved into your CRM linked Adobe Sign account
  • It is vital that the user email in Adobe Sign match the primary email of the CRM user. In the event where it is not, the Adobe Sign email value should be changed.

Within the Dynamics environment there are a couple of items to take note of:

• Licensing - Adobe Sign Users and Administrators need to be assigned with read/write CRM Cal Licensing.
• File Limitations – The file upload size limit is configurable in CRM (default value is 5 mb). Users get an error if they try to attach file larger than the configured value.
  • If the signed PDF is larger than the configured value, Dynamics prevents the file from attaching to the parent (or mapped) entity.

The auto-update process that updates the in-flight Agreements runs under the ownership of the administrator who installs the package. This administrator’s email address must also be configured as an Adobe Sign user for the auto-updates to succeed. (The API makes the “update” call authenticating as the installing Admin email address. If that email is not valid and active in Adobe Sign, the API call fails)

This limitation can be circumvented by assigning the ownership of the three auto-update processes to another user who is valid in the Adobe Sign system.  The three processes that need to be re-assigned are:

• RunPluginForUpdate
• Trigger status update
• Update Status recursive

Obtaining the Package

To obtain the current Adobe Sign for MS Dynamics package from the Adobe Sign integrations page. You will also be able to review release notes, known issues and additional install/user guide information on this page.  If you have any questions or concerns about/during the installation process, please contact your Customer Success Manager.

Installing the package

Log in to your Dynamics CRM environment as an Administrator

• Navigate to Main > Settings > Solutions

The All Solutions page loads:

• Click the Import icon

The Select Solution Package page loads:

• Browse to the AdobeSign_MSD_5_0_0_release.zip file that you downloaded
• Click Next

The Solution Information page loads:

• Click Next

The Import Options page loads:

• Ensure that the Enable any SDK message processing steps… option is checked
• Click Import

The Importing Solution page loads and imports the package. 

This process may take a few minutes. Once the process is completed, you should receive a success message.

• Click Close

Post Install Configuration (Required)

Install the Integration Key

New installations require an integration key to be generated in Adobe Sign and then entered into Dynamics. This key is the access token that authenticates the Adobe Sign and Dynamics environments to trust each other and share content.

To generate an Integration Key in Adobe Sign:

• Log in to your administrative user for your Adobe Sign account
• Navigate to Account > Adobe Sign API > API Information
• Click the Integration Key link in the middle of the page

The Create Integration Key interface loads:

Provide an intuitive name for your key (e.g. Dynamics CRM)

The Integration Key must have the following elements enabled:

• user_read
• user_write
• user_login
• agreement_read
• agreement_write
• agreement_send
• library_read
• library_write

• Click Save once the key is configured.

The Access Tokens page is exposed showing the keys designed in your account.

• Click the key definition created for Dynamics CRM
  • The Integration Key link is exposed at the top of the definition.
• Click the Integration Key link

The Integration key is exposed:

• Copy this key and save in a secure place for  the next step
• Click OK

To install the integration key into Dynamics CRM:

Log in to your administrative user for your Dynamics CRM environment

• Navigate Main > Adobe Sign > Global Settings

The Adobe Sign Configuration page loads.

• Paste your key into the Integration Key field
• Click Validate Key

After a short delay, the page refreshes showing that “You are logged in with Adobe Sign” and displaying the integration key being used.

Verify Legacy Form Rendering is Disabled

It is necessary to disable legacy form rendering to use Adobe Sign. To do this:

• Navigate to Main > Settings > Administration

• Click System Settings

The System Settings page loads.

• Ensure that the Use legacy form rendering setting is set to No
• Click OK

Enabling Activity Feeds on Adobe Agreement entity

Enabling the activity feed for the adobe_agreement object permits Adobe Sign to update the events of the agreement under the Posts section on the Agreement record as well as the CRM records that were the parent object for the agreement (Contacts, Accounts, etc.).

• Navigate to Mail > Settings > Activity Feeds Configuration

The Post Configurations page loads

• Click Refresh to ensure all configurations load
• Single click the adobe_agreement record to select it, and then click Activate

The Confirm Post Configuration Activation pop-up is presented

• Click Activate    

The “You will need to publish…” notification pops up

• Click OK

You are returned to the Post Configurations page

• Click the ellipsis (…) to open the hidden options
• Click Publish All Customizations

Deploy Adobe Sign to Users by Assigning Security Roles

Every Dynamics user that is expected to use the Adobe Sign application needs to have their security role configured in the Dynamics environment.

The security roles are:

• Adobe Sign Reader –Allows the user read only access to Agreement records.
• Adobe Sign User - Allows the user to work with Agreements and other application entities. This role permits the user to see only their own records and allows User level access to built-in CRM records such as Contacts, Accounts, Leads, Opportunities, Orders, Invoices, Quotes.
• Adobe Sign Administrator – This role includes additional privileges in addition to the Adobe Sign User role such as adjusting Global Settings, Data Migration and allowing visibility over all Agreements throughout the application.

To configure the security roles:

• Navigate to Main > Settings > Security

This opens the Security page

• Select Users

The Enabled Users page loads

• Select all of the users you want to promote to one type of security role
• Click Manage Roles

The Manage User Roles pop-up displays

• Check the security role you want to define for these users
• Click OK

Optional Configurations

Adobe Sign Global Settings

The Adobe Sign Global Settings page permits environment-wide adjustments to how users are automatically provisioned in the Adobe Sign system, as well as how the completed agreements are presented within the Dynamics environment.

To access the settings:

• Navigate to Main > Adobe Sign > Global Settings

The Adobe Sign Configuration page loads:

The settings available are:

• Enable user auto-provisioning – If enabled, new users that are given access to the Adobe Sign entity automatically provision a user in the Adobe Sign system upon first use.  Provisioning is based on the users’ primary email address, not the User Name, and takes place in the background without further action by the user or account administrator.  

Note:

New users that are automatically provisioned in Adobe Sign have a randomized password. Because the integration is working as an authenticated application via API, there is no functional problem within the Dynamics environment.  However, if the user seeks to log in to Adobe Sign directly, they need to first reset their password via the “I forgot my password” link on the Adobe Sign home page. 

• Attach Signed Documents To Agreements – This option pushes a PDF copy of the completed document back into Dynamics, attached to the agreement record (under the Notes section).  This would be a copy of the authoritative original which is maintained on the Adobe Sign servers.
• Attach Audit Trail To Agreements – Enabling this causes the Adobe Sign audit trail PDF to be attached to the agreement record (under the Notes section) once the agreement is signed / completed. The audit trail explicitly enumerates the events (viewed, signed, delegated, etc.) of the agreement, the timeline of those events, as well as the IP address where the events were enacted.
• Display Document Preview Images On Signed Agreements – This option attaches an image of the signed / completed document to the record for easy viewing without having to download and open a PDF file.  This image is installed at the very bottom of the agreement record.