While the CRM 2011 OOB report wizard will suffice in many cases, there are specific reasons to go the custom SSRS route. In this post, I will talk you through the creation of a drill down report using a summary table.
Requirement:
Create a summary table of the number of incidents categorized by Subject. The incident count should list the incidents, and the user should be able to open the individual incident by clicking on the ticket number
Design:
We will be creating two new SSRS reports - one report to show the summary table of the incident count by Subject, and a sub report that shows the incident list and also contains the hyperlink to the incident in Dynamics CRM.
Implementation Details:
1. Create a new Report Server Project in Visual Studio.
2. Add a new report and connect to the CRM datasource. I am using MS SQL Server as the datasource type in this example.
3. Enter the following sql query for the report data:
SELECT subjectidname, COUNT(*) AS incidentcount
FROM FilteredIncident
GROUP BY subjectidname
4. Add subjectidname and incidentcount to the details section of the report table.
5. Once the report is completed, the report preview should look like something from the screenshot below:
6. Now that the first summary report is complete, let us turn our focus to the sub-report. Using the same steps like before, create another report but use the following query for the report data. Notice the use of a parameter for the subject value
select statuscodename , prioritycodename , cast( incidentid as nvarchar(100) ) as incidentid , owneridname ,
subjectidname , productidname , customeridname, customerid, customeridtype,
caseorigincode , casetypecodename , customersatisfactioncodename , contractservicelevelcode ,
severitycode , title, ticketnumber from FilteredIncident
where subjectidname = @subject
7. Add subjectidname, customeridname, ticketnumber and incidentid to the details section of the report table.
8. Once the report is complete, this is what the design of the Incident Sub-report should look like:
9. Add a parameter called CRM_URL to both reports, and set the properties per the screenshot below.
The parameter is explained in more detail here, under Hidden Parameters/ Special Parameters.
10. Right click the ticketnumber cell in the sub-report and select Textbox properties. In the Action section, select "Go to URL", and add the following expression value:
Parameters!CRM_URL.Value & "?ID={"& Fields!incidentid.Value.ToString()&"}&OTC=112"
The details of the expression are explained here. The sub report is now complete.
11. In the main report, right click the incidentnumber cell and select Textbox properties. In the Action section, select "Go to Report", and select the subreport created. Add the subjectidname and CRM_URL as the parameters that will be passed from the main report to the subreport.
Both the rdl's are now complete!
12. Import the 2 reports into the Dynamics CRM organization.
13. Run the "Incident Report" to get a breakdown of the incident based on the Subject value.
14. The number "5" in the first row is a hyperlink, which when clicked takes you to the sub-report.
15. Click on the ticketnumber value to view the incident record in Dynamics CRM.
Hope this helps!
Requirement:
Create a summary table of the number of incidents categorized by Subject. The incident count should list the incidents, and the user should be able to open the individual incident by clicking on the ticket number
Design:
We will be creating two new SSRS reports - one report to show the summary table of the incident count by Subject, and a sub report that shows the incident list and also contains the hyperlink to the incident in Dynamics CRM.
Implementation Details:
1. Create a new Report Server Project in Visual Studio.
2. Add a new report and connect to the CRM datasource. I am using MS SQL Server as the datasource type in this example.
3. Enter the following sql query for the report data:
SELECT subjectidname, COUNT(*) AS incidentcount
FROM FilteredIncident
GROUP BY subjectidname
4. Add subjectidname and incidentcount to the details section of the report table.
5. Once the report is completed, the report preview should look like something from the screenshot below:
6. Now that the first summary report is complete, let us turn our focus to the sub-report. Using the same steps like before, create another report but use the following query for the report data. Notice the use of a parameter for the subject value
select statuscodename , prioritycodename , cast( incidentid as nvarchar(100) ) as incidentid , owneridname ,
subjectidname , productidname , customeridname, customerid, customeridtype,
caseorigincode , casetypecodename , customersatisfactioncodename , contractservicelevelcode ,
severitycode , title, ticketnumber from FilteredIncident
where subjectidname = @subject
7. Add subjectidname, customeridname, ticketnumber and incidentid to the details section of the report table.
8. Once the report is complete, this is what the design of the Incident Sub-report should look like:
9. Add a parameter called CRM_URL to both reports, and set the properties per the screenshot below.
The parameter is explained in more detail here, under Hidden Parameters/ Special Parameters.
10. Right click the ticketnumber cell in the sub-report and select Textbox properties. In the Action section, select "Go to URL", and add the following expression value:
Parameters!CRM_URL.Value & "?ID={"& Fields!incidentid.Value.ToString()&"}&OTC=112"
The details of the expression are explained here. The sub report is now complete.
11. In the main report, right click the incidentnumber cell and select Textbox properties. In the Action section, select "Go to Report", and select the subreport created. Add the subjectidname and CRM_URL as the parameters that will be passed from the main report to the subreport.
Both the rdl's are now complete!
12. Import the 2 reports into the Dynamics CRM organization.
13. Run the "Incident Report" to get a breakdown of the incident based on the Subject value.
14. The number "5" in the first row is a hyperlink, which when clicked takes you to the sub-report.
15. Click on the ticketnumber value to view the incident record in Dynamics CRM.
Hope this helps!
No comments:
Post a Comment