Ever tried to write a SSRS report which shows all cases for all accounts and for the contacts associated with those accounts, grouped by the products associated with each case?
CRM 2013 Cases and Its Relationship To Other Entities
In Dynamics CRM, a case record (entity name “Incident”) has a customer field which can be set to either an account or a contact record.
Under the hood, this field is a “customer” type which can be set to either the contact id or the account id.
Those familiar with CRM will also know that a contact entity can be associated with an account record via the “Parent Customer” lookup:
This means that you can have the scenario where one case was raised by “Amy Barlow” of account “Fancy Dress Ltd”, while another case was logged directly for the account “Fancy Dress Ltd”.
At the same time, a user can associate a product record with a case. In the screenshots above, both cases are associated with a product called “Professional Services”.
So when users start logging cases for contacts and accounts that are related to each other, it is only natural that someone asks for a report that slices and dices data in very specific ways …
Along Came Those Reporting Requirements
Given cases that are associated with both accounts and contacts (themselves associated with accounts), I was asked the other day to produce “something” that lists out the following:
- Show all cases associated with all accounts and the contacts of those accounts;
- Users must be able to filter the report by the date when the cases were created and their origin;
- The cases must display relevant information such as Case Number, Case Title, Case Status Reason, and Customer Name;
- If a case is related to a contact associated with an account, display the contact name as well as the company they are associated with;
- All cases should be grouped by the associated products;
- The reporting must work for both CRM on premise as well as CRM online.
Here is my thought process for this one. I am definitely no SSRS / FetchXml expert and there is probably a better way to achieve this (if you know, please mention it in the comments). In the meantime, here is my take on the above reporting requriements:
- The report needs to be a FetchXml-based report otherwise it won’t work online.
- FetchXml can’t handle “unions” (as in SQL) so I’ll likely need two FetchXml queries: one query for cases associated with accounts and a specific product, and another query for cases associated with contacts of those accounts, and a specific product.
- An additional FetchXml query for products is also required, to find all the “productid” in CRM. The above two FetchXml queries are then filtered for each “productid”.
- The first two FetchXml queries (“cases by accounts” and “cases by contacts associated with accounts”) needs to also filter by case “createdon” date and by the case “origin”, as specified by the user at report runtime.
- Once the “cases by accounts” and “cases by contacts associated with accounts” FetchXml queries are run, both datasets need to be grouped by products on the report.
- If I use “tables” in SSRS, each table can only reference one dataset. To group cases associated with accounts and group cases associated with contacts, under the same “product” record, I will need to write 2 sub-reports: one sub-report for the dataset from “cases by accounts” FetchXml, and another sub-report for the dataset from “cases by contacts associated with accounts” FetchXml. The master report shall pass in the product id as a parameter value to each of the sub-reports.
Oh crap, it’s getting complicated. 🙂
FetchXml Queries Fun
So we need three queries: one for cases associated with accounts, one for cases associated with contacts of those accounts, and one for products. Here are the FetchXML queries.
Firstly, the query for cases where customers are set to accounts, with the following filter conditions:
- The case must have been created in the time period specified by the “@FromDate” and the “@ToDate” parameters;
- The case origin must be as specified by the “@CaseOrigin” parameter;
- The case product must not be null.
Secondly, the query for cases where customers are set to contacts associated with accounts, with the same set of filter conditions:
Finally, we need a third FetchXml query to pull out all products and their GUIDs:
Now that we have the three FetchXml queries, we are ready to create the sub-reports which will be embedded in the main SSRS report.
Creating the “Cases By Accounts” SSRS Sub-Report in BIDs
First create a data source and connect it to your CRM organization. Once that is done, copy and paste code for the first FetchXml query (cases by accounts) to create the following dataset:
Notice that we need four parameters for this sub-report: “CaseOrigin”, “FromDate” and “ToDate” (which correspond to the parameters in the FetchXml) and “ProductGuid” (which will be discussed below). To ensure that a user can choose the case origin from a dropdown list, we set the “CaseOrigin” parameter to type “integer” and specific integer values which match those in CRM:
Similarly we set default Date/Time values for “FromDate” and “ToDate” to guide users when running the report:
The 4th parameter we require for this sub-report is “ProductGuid”, whose value we use to filter the “Cases By Accounts” dataset of this sub-report. We first define “ProductGuid” to be of “text” type, and leave all other options as default.
We now go back to the dataset and set a filter to only show cases whose “Case_ProductValue” column matches the product id given by the “@ProductGuid” parameter of the sub-report:
Also, we format the date/time parameter values in the dataset to ensure they are passed in a certain format e.g. “yyyy-MM-dd”:
In the SSRS report designer, drop in a table item and the required columns from the dataset e.g. “Case Title”, “Case number” etc. Furthermore, we group the rows by product name:
We have now completed the first sub-report with dataset “Cases By Accounts”.
Creating the “Cases By Contacts Associated With Accounts” SSRS Sub-Report in BIDs
Similarly, the cases by contacts sub-report is created by first setting the dataset with the FetchXml, create the four parameters, set the dataset to filter by the “ProductGuid” parameter, and drop the table with the relevant columns on the report designer. Finally we group the rows by product name.
Creating The “Cases By Product” Main SSRS Report in BIDs
Once the above two sub-reports are created, we can create the main SSRS report that contains the dataset of all the products in CRM, and embed the two sub-reports in the main report.
Note that the main report has only three parameters: “FromDate”, “ToDate” and “CaseOrigin”, all of which have available and default values set in the same way as those in the two sub-reports.
In the report designer, we can now format the main report by e.g. creating a header and dropping a table item to hold the product name from the product dataset. Insert two new rows inside the product name grouping, and drag-and-drop the “cases by accounts” sub-report and the “cases by contacts associated with accounts” sub-report in those two rows.
The last thing we need to do is to specify how the parameters of the sub-reports should be set. Right click on each of the sub-reports in the main report designer and choose “Sub-report Properties”.
For both sub-report properties, we need to set the 4 parameters of the sub-report (shown by the left hand column in the screenshot below) to the relevant parameters and fields of the main report. For example, “CaseOrigin” parameter in the account sub-report is set to the parameter value “@CaseOrigin” of the main report. The “ProductGuid” parameter in the account sub-report, is set to the “Product_Id” field value of the main report dataset.
Further formatting of the main report can be done, e.g. change font-size of the product name header row and fill in background colours etc.
Once all three reports are formatted, we can now preview the main report in BIDs by setting the parameter values before running the main report:
While running the same report with Case Origin set to “Twitter” returns the appropriate case records:
Once you are happy with the way the reports look, it’s time to upload them to CRM.
Upload reports RDL files to CRM
In BIDs, build the reports and find the RDL files from the relevant build location:
Open CRM and point the browser to the unmanaged solution you wish to add your reports to. For each report, create a report record and upload the existing RDL files to the solution. For the main report, make sure you set the “Related Record Types” to “Cases” to ensure that users can see the report when they are looking at a list of cases in their case views and run the main report:
Save and publish the changes. Users of CRM who have enough privileges to run reports should now see the main report (Cases By Products) available to run when they view a list of cases in CRM:
And we are done! Phew!