How to create a sub report

I’m sure you figured how to map these requirements in Microsoft CRM already, so let’s just put an image to illustrate the entities and their relationships (fields and entities not relevant to our example are omitted for easy understanding):

Now for the report design requirements:

• The report should be grouped by milestone category
• Each row should display only the milestone title and should allow for drill-down to the complete description and linked activities
• Each milestone should have an order number in the report (not in CRM)
• For each milestone displayed, the linked activities should be displayed under it

If you’ve already done some reports using Reporting Services, you know this is kind of basic stuff; for the others, I’ll explain the steps needed to create this kind of report since it involves the use of a subreport:

1. Create a new report and add the default datasets for use against CRM; my advice is to create a new report based on an existing CRM report
2. This report will contain:
   • 2 new datasets that will be used to populate our table
   • A report parameter (CRM_ParameterID) that will be used to store the milestone ID, this parameter being used in the subreport
   • A table that holds the deployment & milestone info, as well as a reference to the subreport which will display the activities linked to the milestones; the TSQL code for the dataset behind this table should look like this:

     DECLARE @sql NVARCHAR(MAX)
     
     SET @sql = '
         select 
             account0.name as accountname,
             contact0.fullname as contact_name,
             contact0.emailaddress1 as contact_email,
             contact0.telephone1 as contact_phone,
             deploy0.new_deploymentid,
             deploy0.new_deploymentsubject as subject,
             deploy0.new_deploymenttypename as deploy_type,
             miles0.new_milestoneid,
             ROW_NUMBER() OVER (ORDER BY miles0.new_milestonecategory) as milestone_nb,
             miles0.new_milestonecategoryname as category,
             miles0.new_milestonedate as planned_date,
             miles0.new_milestonetitle as milestone_title,
             miles0.new_milestonedescription as milestone_desc,
             miles0.new_milestonerealizeddate as realized_date,
             miles0.owneridname as responsible
         from (' + @CRM_FilteredAccount + ') as account0
         inner join Filterednew_deployment as deploy0
             on account0.accountid = deploy0.new_associatedaccountid
         inner join Filterednew_milestone as miles0
             on deploy0.new_deploymentid = miles0.new_associateddeployment
         left join FilteredContact as contact0
             on deploy0.new_associatedcontactid = contact0.contactid
         order by miles0.new_milestonecategory ASC'
     
     EXEC(@sql)

   NB: In this TSQL example, I used the ROW_NUMBER() OVER ([…]) function to generate some auto-numbers based on the milestones category; for more information on this SQL Server function (only available in SQL Server 2005 and up), please follow this article.

3. The general layout for this report should look like this (this is an example):
   
   • In this example, the element highlighted in yellow is the reference to the subreport: to reference a sub-report, just click on the subreport icon and select an empty cell in the table
   • The subreport properties we must deal with are the general properties and the parameters properties:
   • In the ‘General’ properties, you must choose the report to be used as a subreport (see #4 hereunder)
   • In the ‘Parameters’ properties, you must configure the parameter to use and the value that will be passed, in this case it is the CRM_ParameterID parameter, which gets the ‘new_milestoneid’ value:
4. After the first report is completed, create a new empty report with the following:
   • A report parameter (CRM_ParameterID) that will hold the milestone ID passed by the parent report
   • A dataset that returns the list of activities based on the parameter; here is an example of TSQL code:

     select 
         act0.activitytypecode,
         act0.description as activity_desc,
         act0.owneridname as activity_resp,
         act0.subject as activity_subject,
         act0.scheduledend as activity_time,
         act0.statuscodename as activity_status
     from FilteredActivityPointer as act0
     where act0.regardingobjectid = @CRM_ParameterID
     order by act0.scheduledend DESC

   • The design is really simple and could look like this:
5. Okay, time to deploy the reports!  First, you must deploy the parent report then the subreport; I will not explain the deployment of the first report, since this has been explained at the end of this post. The steps to deploy the subreport are exactly the same, and since everything was configured correctly in the parent report, the ‘Parent Report’ section is automatically filled for you:
   
6. Once you test the report, this is the result you get: