The ability to create custom reports has been one of the top asks for Microsoft Dynamics CRM Online. When we set about designing a solution, security, ease of use, and performance were one of our top goals along with portability of reports, so that you can use the same RDL for CRM Online and On-premises deployments. In addition, we wanted to have the best authoring environment for Custom Reports. In Microsoft Dynamics CRM 2011, we have introduced Fetch-based custom reports that can run in CRM Online as well as On-premise, are secure, have great performance, and leverage the best WYSIWYG environment for authoring.
You will now be able to add company logos to the report, perform conditional formatting to highlight issues, control the format of the report to name a few. Business Intelligence Development Studio (BIDS) by Microsoft SQL Server is the best and most powerful authoring environment for authoring SQL Server Reports. By enabling authoring of CRM Fetch-based reports in BIDS, we tried to address all your concerns. We only ask you to stick to CRM’s Fetch XML to query data out of CRM.
Fetch XML based Custom Reports
Custom reports in Microsoft Dynamics CRM 2011 are your regular RDLs with Fetch XML as the data query language. These are identified by MSCRMFETCH in the DataProvider section in the RDL.
<DataSources>
<DataSource Name=”DataSource1″>
<ConnectionProperties>
<DataProvider>MSCRMFETCH</DataProvider>
<ConnectString>http://localhost;AdventureWorksCycle</ConnectString>
</ConnectionProperties>
<rd:SecurityType>DataBase</rd:SecurityType>
<rd:DataSourceID>fd45ed8a-xxxx-xxxx-xxxx-deeeaf87dd31</rd:DataSourceID>
</DataSource>
</DataSources>
Along with the Fetch XML in the CommandText, the below query gets the opportunity details:
<DataSets>
<DataSet Name=”DataSet1″>
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText><fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”opportunity”>
<attribute name=”name” />
<attribute name=”customerid” />
<attribute name=”estimatedvalue” />
<attribute name=”statuscode” />
<attribute name=”opportunityid” />
<order attribute=”name” descending=”false” />
</entity>
</fetch></CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<!– Omitted RDL –>
</Fields>
</DataSet>
</DataSets>
The rest of the RDL is same as any other SQL report which means you can leverage full capabilities of Microsoft SQL Server Reporting Services to render your CRM data as a scorecard, Sales Funnel or Bing Map. We will look at an example later in the post.
Execution of Fetch-Based Custom Reports
To be able to run these Fetch-based reports, we have a runtime component , custom data processing extension (DPE) that resides on and is invoked by the SQL Server Reporting Services (SSRS) on encountering MSCRMFETCH in the DataProvider attribute (as mentioned above). SSRS passes the command text to the DPE to execute. DPE executes the query in the context of the user performing the action and provides the data back. This is then formatted as per the RDL by SSRS before being presented to the user.
Authoring of Fetch-Based Custom Reports
To enable WYSIWYG authoring in BIDS, we have written a custom plugin for Business Intelligence Development Studio that allows users to specify the Fetch XML query and provide the metadata for authoring. It also enables live preview right in the BIDS environment without having to upload the report to CRM. Once you are happy with the report, you can upload it to CRM as an RDL (with embedded Fetch). You only need BIDS and Report Authoring Extension to be installed.
Download Now
The runtime component that resides in SSRS is known as Microsoft Dynamics CRM 2011 Reporting Extensions (English Beta bits available here as part of CRM Server). The authoring component, a plug-in for BIDS, is known as Microsoft Dynamics CRM 2011 Fetch Authoring Extension (English Beta bits available here). Please note: BIDS being a 32-bit environment, the Fetch Authoring Extension is also available in 32-bit only. For purposes of Beta, the Fetch Authoring Extension is known as Fetch Extension.
Other Enhancements
In addition to enabling Fetch-based reports, we have made reports Solutions aware. That is, reports can now be packaged into a Solution and deployed effortlessly to several organizations. A Report Wizard-generated report can be transported via solutions to different orgs and can still be edited via Report Wizard.
The Report Wizard now generates Fetch XML based reports. You can now download these report RDLs to act as a starting point for your Fetch-based Reports.
The Microsoft Dynamics CRM 2011 Reporting Extensions now installs both Fetch data processing extension and SQL data processing extension (aka srs dataconnector) on SSRS. Both the data processing extensions are a mandatory install.
For On-premises deployments, the SQL-based reports will continue to be supported along with scheduled reports and delivery mechanisms like email.
There are two ways of writing a custom Fetch-based Report; we will look at both of them here.
Authoring a Fetch-based Report
Customize a Report Wizard report
If CRM Report Wizard satisfies your reporting needs, but you need to change some formatting, like adding a company logo or perform conditional formatting, you can follow these steps:
1. Create a report in Microsoft Dynamics CRM using the Report Wizard for Opportunities that shows Key fields like Topic, Potential Customer, Estimated Revenue, Estimated Close Data, Probability etc (refer this CRM 4 help topic for more help).
2. While still in the Report Wizard,
a. Click ‘Run Report’ on the Action toolbar to verify the contents.
b. On the Actions toolbar, click ‘Download Report’, and save the RDL to your machine.
3. Install Microsoft Dynamics CRM 2011 Fetch Authoring Extension, if needed.
4. Launch Business Intelligence Development Studio.
5. Create a new Report Server Project.
6. In Solution Explorer, right-click the Reports folder, click ‘Add’, and then click ‘Add Existing Item’.
7. Select the RDL that you downloaded in Step 2.b.
8. Double-click the RDL in Solution Explorer and navigate to ‘Report Data’ window on left
9. Expand the ‘Data Sources’ node and right-click the CRM node.
10. Click ‘Data Source Properties’.
11. In the Data Source Properties dialog, click ‘Credentials’.
12. Select ‘Use this user name and password’, and specify your CRM User name and password. Click ‘OK’.
(For Online customers, the user name would be of format ‘example@hotmail.com’. For On-premises customer, the user name would be of format ‘domain\LoginId’)
Note: On-premises users may skip this step if CRM is installed in same domain as ‘Use Windows Authentication’ is selected by default.
13. Now, navigate to the ‘Preview’ tab. If your credentials are correct, you should see the report preview similar to the one shown below:
Note: If you do not have any data in CRM, You can always enable Sample Data by going to Settings…Data Management…Sample Data and Selecting ‘Install Sample Data’. It takes a few minutes to populate the sample data in the CRM system. You can remove the Sample Data by same route.
Now, let’s add a company logo and some conditional formatting to the report.
14. To add a company logo, click the ‘Toolbox’ tab, and click ‘Image’.
15. Click on the Report where you would like to add the Image.
16. In the Image dialog, click Import to select the company logo (image can be JPG, BMP, PNG, GIF).
17. Click Ok. Adjust the image size by dragging the side bands.
18. Let’s say, you want to highlight all opportunities having probability > 80%. To do that, select the row in the Design Mode (click the Design tab).
19. Press F4 to view the row properties. ( via Menu ‘View’…’Properties Window’)
This should open the properties window (usually in the lower-right corner of Visual Studio).
20. Find the BackgroundColor property, click the down arrow, and click ‘Expression’.
21. Type the following in expression
=IIF(Fields!closeprobability.Value > 80 ,”Green”,”Transparent”)
22. Click OK.
23. To see the report preview, click the Preview tab. Depending on your data, your will see opportunities with probability > 80 in a green background color.
24. Save your project.
25. Now upload the RDL to CRM. Select the RDL in Solution Explorer, and from the Properties window, copy the ‘Full Path’.
26. Navigate to CRM. Select the report you had created in Step 1, and click ‘Edit’ on the Ribbon menu.
27. In the Report Wizard, change Report Type to ‘Existing File’, and then click ‘Browse’.
28. In the File dialog, paste the full path (copied in step 25), and click ‘Open’.
29. In the Report Wizard, on the Action toolbar, click ‘Save’ to upload the new RDL.
CRM will confirm the action as shown below (you can also upload the report as a new report, if needed)
30. Click Ok.
31. Now, let’s run the report by clicking ‘Run Report’. You should see the newly added Logo as well as the conditional formatting.
The report is now available for use.
Create custom Fetch-based reports
The steps in the earlier section demonstrated how you can take a Report Wizard report, enhance it in Business Intelligence Development Studio, and upload it back to CRM. What if you wanted to create a report from scratch in BIDS?
The initial steps vary a little. Let’s use the same example.
First you need a Fetch XML query that will retrieve the data for the report.
1. Navigate to CRM Advanced Find, and specify a query.
2. You can pick the individual columns by selecting ‘Edit Columns’
3. After you are satisfied with your query, click ‘Download Fetch XML’.
4. Save the Fetch XML to your local drive.
5. Install Microsoft Dynamics CRM 2011 Fetch Extension, if needed.
6. Launch Business Intelligence Development Studio.
7. Create a new Report Server Project.
8. In Solution Explorer, right-click Reports folder, and click ‘Add New Report’.
The Report Wizard will launch.
9. Click Next on the first page.
10. In the ‘Select the Data Source’ page, specify a name for the data source, select ‘Microsoft Dynamics CRM Fetch’ as Type, and type in the connection string.
Connection String should be in the following format:
CRM Server URL;[Org Unique Name];[Home Realm URL]
Only the CRM Server URL is mandatory. If Org Name is not specified and the user belongs to multiple orgs, then the first org returned by CRM is used. For most users, you will not need to worry about Home Realm. Home Realm URL is the Identity Provider used by your organization and is needed when your organization uses Federation for identity management. Contact your network administrator to know the URL.
For CRM Online, You will need to specify your Live ID. And, if you belong to more than one org, you will need to specify your Org Name.
11. Next, click ‘Credentials’, and select ‘Use a specific user name and password’.
12. Click OK. Click Next.
13. In the ‘Design the Query’ dialog, paste the contents of Fetch XML query you had saved in Step 4.
(Optionally, you can see the results of query by going to Query Builder, and clicking ‘Run’)
14. Click Next.
15. On the Select Report Type page, with tabular choice selected, click Next.
16. On the Design Table page, select the fields to show in the details.
Note: Attributes in Microsoft Dynamics CRM appear more than once in results. attributeName is formatted value and attributeNameValue is the value of the fields. For e.g estimatedvalue will be $10,000.00 and estimatedvalueValue will be numeric 10000.0000
17. Click ‘Finish >>|’.
18. Give a name to the report, and click Finish.
19. Click the Preview tab to view the report.
You can format the report before uploading it to CRM. The steps remain the same as explained in earlier example.
We just saw how easy it is to create a Fetch-based report via the Report Wizard or a new custom report using BIDS. You can also leverage native capabilities offered by BIDS to render scorecards, maps, sales funnels etc.
Note: In case you are experiencing issues with Microsoft Dynamics CRM 2011 Beta, please leverage the Beta Forums.
Please let us know, in comments, what topics you would like to see about Reporting in the future blogs.
Cheers,