Please refer the following links for basic steps.
Custom Fetchxml based Report in
CRM 2011 using SSRS - http://crmdm.blogspot.com/2013/09/custom-fetchxml-based-report-in-crm.html
How to Deploy a custom Fetchxml Report in CRM 2011
This post is regarding a custom fetchxml report which uses the report control ' matrix' OR a grid report in simple words.
"Matrix is a report tool component which displays aggregated data in a grid that has a variable number of columns and variable number of rows"
Lets understand grid report with a sample scenario.
Scenario 1: 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. So System jobs should be displayed in a grid. The grid should have the Column group as Year and System Job type as row group. The count of System jobs should be displayed in the grid for the corresponding system job type and year.
Scenario 2: 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. So System jobs should be displayed in a grid. The grid should have the Column group as Year and System Job type as row group. The grid should have a further column grouping based on the status of the system job. The count of System jobs should be displayed in the grid for the corresponding system job type,year and status of system job.
So lets get the ball rolling.
Scenario 1:
1. Here is the fetchxml Query. The System Job entity is called 'asyncoperation'
It has an aggregate function to find the count of system jobs. It has multiple grouping. One grouping is based on System Job type and other one based on Year.
<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='asyncoperation'>
<attribute name='asyncoperationid' alias='COUNT' aggregate='count'/>
<attribute name='operationtype' alias='SystemJobType' groupby='true' />
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />
</entity>
</fetch>
2. Dataset is shown here.
3. We could see the selected fields in the Data set as shown below.
4. Select the matrix control from the tool box and drag it to the Design area as shown below.
5. Its possible to re-size the matrix. And then double click on it.
6. Lets put the row group first. So in our case its System job type. This can be done in 2 ways.
Either we could drag System job type to the Rows OR select the Row cell of the matrix and then a tiny box will appear in the top right corner of the Rows as shown below. So click on it. Lets select System Job Type.
7. Secondly we could put the column group. In our case its Year. Lets drag year value to the Columns as shown below.
8. And the next item in the Matrix is Data. Lets put System Jobs count value into that.
9. Lets change the first cell description as System Job Type Vs Year. (Optional)
10. Here comes the end of fist scenario.
To deploy this report in CRM 2011 please refer the following.
How to Deploy a custom Fetchxml Report in CRM 2011
Scenario 2 :
1 Lets alter the fetchxml query as per the requirement in scenario 2. It should have an additional column group for status of the system job. So we need one more grouping based on the status.
2. So now we have status field added to our dataset as shown below.
3. We need to select the column group and then right click and then Add Group --> Child group.
And choose Status. In this case first level column grouping is based on Year and the next one is based on status.
4. So now our matrix includes the status. Please note that the status is added to column groups.
So first group it by year and then status on the column level.
5. We could put a description for the status as shown below.
6. And here is our apple pie.
7. To deploy this report in CRM 2011 please refer the following.
No comments:
Post a Comment