Custom report creation in Microsoft Dynamics CRM 2011 using BIDS Fetch Extension

Creating reports for developers was a traditional wizard walk-through activity for a CRM application with very minimum of flexibility. But with Microsoft Dynamics CRM 2011 there are loads of customization features and flexibility in creating reports.

Follow the step-by-step procedure below to create a custom report using Microsoft Dynamics CRM 2011.

Prerequisites:

Before you start customization of reports

• Install Microsoft SQL Server 2008 or 2008 R2 Business Intelligence Development Studio (BIDS)
• Install Microsoft Dynamics CRM 2011 Online account
• Install Microsoft Dynamics CRM 2011 BIDS Fetch Extension

The customization is done in two parts

• Setting up a Microsoft CRM 2011 FetchXML Custom Report development environment
• Creating a Microsoft CRM 2011 FetchXML Report

Setting up a Microsoft CRM 2011 FetchXML Custom Report development environment

• Setup a PC with Windows 7 (32 or 64 bit)/ Vista/ Windows Server 2008/ Windows Server 2008 R2
• Install the Microsoft SQL Server 2008 or the Microsoft SQL Server 2008 R2 BIDS feature.

• Install the Microsoft Dynamics CRM 2011 Fetch Extension
• Run the CRM2011-BIDSExtensions-ENU-i386.exe.

Creating a Microsoft CRM 2011 FetchXML Report

To create a report, open the Microsoft SQL Server BIDS and follow the steps below:

• Create a new project.
• From the templates, select the Report Server Project. Provide a title to the project and location where the project would be saved.

• A Report folder should be available in the project folder. Select the Report folder to right-click and select Add New Report.

• The Report Wizard appears. The on-screen instructions will step you through the process.
  • Click next until Data Source Options wizard screen appears. Select a new datasource.

  NOTE: DO NOT REFER TO THE SAVED DATA SOURCE. ALWAYS CREATE A NEW DATA SOURCE.

  • For type select Microsoft Dynamics CRM Fetch and enter the connection string in this order:  ServerURL;OrganizationName;HomeRealmURL
    • Provide only ServerURL, which would be the CRM server url. For example, https://xyzserver.crm.dynamics.com
    • In case of multiple CRM environments include optional OrganizationName. First organization name is used else find the value under Developer Resource area in CRM Client located under Settings, Customization.

• The HomeRealmURL is optional. It will be a registry value from HKLM\SOFTWARE\Microsoft\MSCRMBidsExtensions\HomeRealmUrl
• Click Credentials to fill in your credentials, which would be your Windows LiveID and password that you use to login to CRM online account. Click Next.
• The wizard will prompt for a FetchXML type query, if you login successfully.
  • Using CRM online web client and creating an Advanced Find query you can find a FetchXML query.

• Click Edit Columns to select the data that should appear in the report.
• Click Download Fetch XML to view the xml file with the query.

• Follow the wizard to customize the look and feel of the report.
• You can preview the report using Preview tab before uploading. The benefits of creating a report using this procedure is that you can edit the report using the Design tab in Visual Studio.

• The report generated would be an .RDL file,  which needs to be uploaded into the CRM environment
  • Open the web client and map to Reports, which can be located under Workplace section. Click New at the top left to create a new report.
  • Select Existing File as the Report Type and enable the File Location.

• Locate and upload the .RDL file created by project.
• The report creation is completed and also has been uploaded to the CRM. Click Save and View the created report.