Custom Fetchxml based Report in CRM 2011 using SSRS

As you all know there are mainly two types of Custom reports could be developed by using SSRS ( SQL Server Reporting Services ) 

•  SQL- based ( Microsoft MSDN says "For security reasons, you cannot deploy custom SQL-based reports to Microsoft Dynamics CRM Online") 

•  Fetch - based. ( Works in both On-Premise and Online versions)

Fetchxml basics could be found here --http://crmdm.blogspot.com/2011/02/fetch-xml-in-ms-dynamics-crm-2011.html

This post is to demonstrate how to do a very simple fetch based ( Fetchxml based ) report using SSRS.

Scenario : There are different kinds of system Jobs in CRM 2011 ( For instance Workflow, Bulk delete Bulk email etc ). The user would like to see an overview of these system jobs type and the count of each of these system jobs in a chart. So how many workflow system jobs, how many bulk delete jobs etc in CRM.

In simple words just imagine a graph for which one axis represents System job type ( Horizontal ) and other axis (Vertical ) represents the Count.

So We have SQL Server Business Intelligence Development Studio ( version 2008 ) installed and also Microsoft Dynamics CRM 2011 Report Authoring Extension installed on top of that.

Its good understand the importance of Microsoft Dynamics CRM 2011 Report Authoring Extension. This plug-in provides us the option to use Fetchxml queries from  SSRS. If we don't have this plug-in then the "Microsoft Dynamics CRM Fetch' option will not be available in the SSRS Data source.

Here we are using a CRM On-Premise version to demonstrate this report.

Now its time for kick off.

1. Select the SQL Server Business Intelligence Development Studio ( BIDS ) as shown below.

2. File --> New --> Project.

3. Business Intelligence Projects--> Report Server Project. Lets call it as "DEMO Project". Say okay to them.

4. View solution explorer and Reports right click Add--> New item.

5. Select Report and lets call the report SystemJobsCountBasedonType.dll and say 'Add'.

6. So we could see the Design area by default as shown below.

7. The next step is to define a Data Source. Right click on the Data Sources and Add Data Source..

8. Lets call the Data source as DSourceDEV. Please note the option Microsoft Dynamics CRM Fetch is available because of the plug-in Microsoft Dynamics CRM 2011 Report Authoring Extension as mentioned earlier. Connection String format is http://ServerName/Orgname and for Online version, the corresponding connection string to be used.

9. In the Credentials tab its possible to choose the credentials as shown below. For online version, we could provide username and password. For now, we choose Windows Authentication

9.  The next step is to choose a Data Set. Right click on the Datasets and Add Dataset.

10. Lets call the Dataset as DSetSystemJobsCountBasedonType. And choose the option Use a dataset embedded in my report. Click on the Query Designer button. We are going to design the Fetchxml query in the Query Designer

11. Regarding System Jobs .  "A system job, also known as an asynchronous operation, is used to define and track the execution of an asynchronous operation, for example an asynchronous registered plug-in, workflow, or other background system operation. asyncoperation record is created in the database by the platform whenever an asynchronous plug-in or a workflow is to execute, or any one of other asynchronous system operations is to be performed" - ref: MSDN

Fetchxml basics could be found here --http://crmdm.blogspot.com/2011/02/fetch-xml-in-ms-dynamics-crm-2011.html

So we are going to write to the fetchxml for the enity System Job (name for this entity is AsyncOperation)

Here is a simple Fetchxml to retrieve all the Attributes of AsyncOperation entity. This is not our final query but just to give you a helicopter view.

12. Now lets modify the fetchxml for our scenario.

Count of each System Job Types.

So need the count of System jobs and grouped by System Job type.

This means Aggregate of System Jobs based on count and  grouped by its type.

Please note a few things in this fetchxml. 

aggregate = 'true' -- This indicates the we are going to use an aggregate function in the fetchxml.

aggregate ='count' -- Here we need the aggregate function count. Because we need count of system jobs.

groupby='true' -- Group by System job type. And its based on count.

13. So our dataset looks like this. Say Okay and press Okay.

14. Please note that the selected fields are available as show below.

15. From the tool box, select the Chart and drag it to the Design Area.

16. Select the chart type. Its possible to change the chart type later. For now lets just select the column type.

17.  You could see a chart inserted. Double click on the chart.

18 Now drag the CountValue to the Aggregate section and System job type to the Category groups. Please note that its possible to resize the report. Also possible to change the title if you double click on it. Axis titles also could be changed. So please feel to play around it.

19. You could see that The Countvalue added in Aggregation section and Type in Category group. Also change the axis title accordingly. We could see the preview easily which is the tab next to Design.

14.  Here is the preview. Please note this is a very simple report.  The principle is "First things first".

15. In order to deploy this report, please refer the following link. 

How to Deploy a custom Fetchxml Report in CRM 2011

http://crmdm.blogspot.com/2013/09/how-to-deploy-custom-fetchxml-report-in.html