Applications Services Blog
Get the latest thought leadership and information about the role of Applications Services in an increasingly interconnected world at the HP Blog Hub.

Microsoft Dynamics CRM 2013 SSRS drill down reports – Part II

While the CRM 2013 out of the box report wizard will suffice in many cases, there are specific reasons to go the custom SSRS route. Frequently there is a need to have a summary report of your CRM data with the ability to drill down to the specific record in the database. While Part 1 of this blog series dealt with creating the base SSRS report, in today’s post I will walk you through the creation of a sub report and how to link it to the base SSRS summary table report.

 

Requirement:

 

Create a table of the list of incidents for a specific Subject and link it to the base SSRS summary report. The incident number should be a url which opens up the specific incident.

 

Design:

 

We will be creating a new SSRS report to show the incident list for a specific Subject with data from Dynamics CRM 2013.

 

Implementation Details:

 

1. Add a new report to the existing report server project created in the earlier post and connect to the CRM datasource. I am using MS SQL Server as the datasource type in this example.

 

p2.png

 

2. Enter the following sql query for the report data:

 

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

 

p6.png

 

 

3. Add subjectidname, customeridname, ticketnumber and incidentid to the details section of the report table.

 

p7.png

 

 

4. Once the report is complete, this is what the design of the Incident Sub-report should look like:

 

p09.png

 

 

5. Add a parameter called CRM_URL to the report, and set the properties per the screenshot below.

The parameter is explained in more detail here, under Hidden Parameters/ Special Parameters.

 

p8.png

 

6. 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.

 

p9.png

 

7. 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.

 

p10.png

 

Both the rdl's are now complete!

 

8. Import the 2 reports into the Dynamics CRM organization.

 

9. Run the "Incident Report" to get a breakdown of the incident based on the Subject value.

 

p13.png

 

10. The number "6" in the second row is a hyperlink, which when clicked takes you to the sub-report.

 

p14.png

 

11. Click on the ticketnumber value to view the incident record in Dynamics CRM 2013.

 

p15.png

 

Thanks for reading, and please leave your thoughts in the comments!

 

crm_logo.png

Leave a Comment

We encourage you to share your comments on this post. Comments are moderated and will be reviewed
and posted as promptly as possible during regular business hours

To ensure your comment is published, be sure to follow the community guidelines.

Be sure to enter a unique name. You can't reuse a name that's already in use.
Be sure to enter a unique email address. You can't reuse an email address that's already in use.
Type the characters you see in the picture above.Type the words you hear.
Search
Showing results for 
Search instead for 
Do you mean 
About the Author
CRM enthusiast, avid techie
Featured


Follow Us
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.