Creating a SSRS Report With Microsoft Dynamics CRM On-line

Introduction

Dynamics CRM is a Customer Relationship Management Software designed and deployed by Microsoft for Sales and Services organization. In CRM, we have three main predefined modules: Marketing, Sales and Services. Technically. CRM is comprised of .NET, Sharepoint, JavaScript as front-end, SQL Server as database and Reporting Services to generate reports.

What BI can do for CRM?

BI people can have different types of reports which can be useful for dash boards. As CRM uses Reporting services, BI professionals can upload their .rdl files or they can consume their reports hosted in Report Server through URL.

Creating a SSRS Report for CRM On-line

Reporting Services require a driver “Microsoft Dynamics CRM Fetch” driver to connect CRM database can be download here. Come, we will create a new report for CRM.

1. Create a Reporting Services Solution.
2. Right Click on Reports Folder -> Add -> New Item, select Report and click ok.

   

3. New blank report will open. Now we need to add data source.
4. Go to “Report Data” section, right – click on “Data Sources” select “Add Data Source”. Data source properties will be appear as popup. Select “Embedded Connection” and in Type drop down, select ““Microsoft Dynamics CRM Fetch”.

   

5. Place your CRM Online URL in connection string box and give credentials.

   Note: If you have more than one organization on the same account, you need to specify organization unique id along with CRM online URL.

   Syntax: <<CRM ONLINE URL>>;<<Organization_UniqueID>>

   Example: http://----.CRm5.dynamics.com;org2beffc

   

   

6. Now, we need to create data set which is a carrier between Report area and database. To get data from CRM, we need to download a special structured query called “Fetch XML” from CRM and place it in Query part of dataset.

   

7. Make a tablix in report area and preview the report. Horray!!! the report is there.

   

How to get Fetch XML from CRM

Go to Advance find, Select Entity and condition. On ribbon, you will find “Download Fetch XML” button.

Deploying the Report in CRM

Deploying Report in CRM is quite an easy task like we use to SSRS reporting portal. Go to Module menu, Tools and Reports.

Click on new Button in reports, a popup will open for new report.

Select “Existing report” from Report type drop down and select report and click on “save”, your report will be added to reports in CRM.