Saturday, 20 April 2013

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):
relational model
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): image
    • 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 tableimage
    • The subreport properties we must deal with are the general properties and the parameters properties:image
    • In the ‘General’ properties, you must choose the report to be used as a subreport (see #4 hereunder)image
    • 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:image
  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:image
  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:image
  6. Once you test the report, this is the result you get:Untitled