Reporting through BIDS in CRM Online 2011

In the CRM 2011 online if you want to create your custom SSRS report then here is the trouble, you cannot create the report directly using the SQL Queries as you do not have access to SQL Server. For this reason MS has provided BIDS tool for Dynamics CRM 2011. You can download this tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=64a09b75-8376-4b9d-aea3-8a83a1837c4d

Using the BIDS tool, you can connect to Dynamics CRM Online service and use the CRM FetchXML queries to generate the queries required for the report.

To create the SSRS report for the CRM 2011 Online first you need to run the tool “CRM2011-Bids-ENU-i386” on the machine where you have installed the SQL Server Business Intelligence Development Studio.

After installing this, a new Data Source type gets added in the “Embedded connection type” option named “Microsoft Dynamics CRM Fetch”. To create the new report with fetch XML for the CRM 2011 you need to select this data type as shown in the below screenshot.

In the Connection string you need to provide your CRM 2011 online url and the org unique name, where the org unique name is optional and in the permissions section you need to provide the credentials of the CRM user.

In the SSRS report the main things are the Parameters, Query/Dataset and Design. Below is the explanation.

Parameters: You can simply defined the parameter as it is, but if you want to use the CRM filtered parameter and show this reports on the CRM grid or on the selected record, then you need to use the enableprefiltering in the fetchXML of this parameter. You can write the default fetchXML for this as follows.

Query/Dataset: Main thing in the Query is to pass the condition from the parameters. Generally in report the structure is that you have main-report and a sub-report, where you pass the parameter values of the main-report to the parameter of the sub-report.

Here we take an example to understand how it works in this scenario. For example: suppose we want to see all the contacts that is modified by a certain user, we want to run this report on the CRM user form and see the list of contacts.

For this purpose, we have created a Main report where we have a parameter named “@CRM_FilteredSystemuser” which is passed from CRM and it creates a dataset from this Parameter with three columns.
Fullname
Internamemailaddress
Systemuserid

The query for this dataset is shown in the above screenshot.
Note: If you have used the CRM parameter then it should be hidden.

Now we need to create the sub-report which contains the list of the contacts, in the sub-report we have a parameter named @Userid, and the value for this parameter is passed from the Main Report. Now the Query for sub-report with condition is as shown below.


Once you execute the Query, the dataset will be generated. From there on you can go ahead and design the report like any other normal SSRS report.

Once designed, you can upload this report to CRM and run it through the CRM User entity form.

Through BIDS it is now really easy to design reports for CRM Online 2011. Good knowledge of FetchXML is required. However there are tools available that would let you convert/design FetchXML queries through Query designer interface.