A long, long time ago in a blog far away, I discussed how to report on reports using the FilteredReports entity. Today’s blog takes that further in order to show how to use SSRS to show the relationships amongst parent reports and sub-reports using, gulp, recursion. The intended audience for this blog is as follows:
1. They understand the television reference in the title. If not, please put down your Surface and go turn on the TV.
2. They have experience with report writing and maintenance.
3. They saw the word recursion but still pressed on.
Why Is This Useful?
The story behind the story here is that I walked into a situation where I needed to know what reports related to what. There were a lot of custom reports, and these custom reports had parents and children and grand-children. It was a very fruitful set of reports, and they were left unattended. And as the saying goes from report developer to business person: “Reports beget reports”. But I digress.
In addition to knowing these relationships to solve a particular problem, the other reason why this is useful is because it is an easy way to document reports. For turning over the keys to the reports which you might create but others will maintain, it is a first step in understanding the report package. It answers questions like “ what report does this jump to? “.
Cart Then Horse
Since a picture is worth a thousand words, this is the finished product:
What I’m showing here primarily by indentation and pretty colors (thank you for that compliment, khaki isn’t just for pants), are the family of reports starting with the top level. I’ve highlighted using the Campaign Performance report which goes 4 levels deep. If this were a custom report with drilldown after drilldown and I needed to understand what reports are needed to build a project in Visual Studio, this is invaluable. The standard view of reports in CRM will show you Name and Parent Report, but not the hierarchy. Nor will it show you the filename field. So by necessity I had to use a report to bring this all together. From a documentation perspective this shows everything that a report developer might need to update reports, and understand the relationships. And as these reports are updated, this report can be re-run to keep system documentation current.
There’s a best practice here which applies to fields, entities, and reports which everyone should follow: put a description on any of these. This description should tell the purpose of the any of those so that people can answer the question, “So why is that in here?” Plus documenting field and entity descriptions helps in exercises such as documenting your data dictionary. I speak from experience where in using CRM in a large multi-national conglomerate going through this, CRM looked like a rock star over the myriad of custom applications because so much of this was part of the metadata. And also because it was so much easier to extract as well.
And Now for the Horse
So how did this all come together? I started by binging “SSRS recursion” as I do whenever I need to write a report of this nature, which isn’t all that frequently. This is not a new problem for one, in this case the application was new to me. This solution is typically applied to the problem of displaying manager-employee relationships, but it is very relevant here.
The dataset was simple. There are no joins or even common table expressions. This was all a combination of FetchXML to get the data, and the built-in features of SSRS. (Note that FetchXML is merely a detail. This same solution could have just as easily been done with SQL, but I wrote it using FetchXML so that it would work in either online or on-premise Microsoft CRM environments. In SQL-speak I did the equivalent of SELECT field a, field b FROM FilteredReports). In SSRS, I used a simple table with one grouping level. Here’s a screenshot from Visual Studio 2008 as to where the magic happens:
And that’s it. SSRS does the heavy lifting, which I don’t always recommend by any means, but it was sufficient given the simplicity of the dataset. The hierarchical relationship between all the records is established within the group. Now it’s a matter of properly displaying the data.
Magic Part Deux
The three pieces behind displaying the data are dynamically changing the colors, adjusting the indent based on the level in the hierarchy, and toggling the display (the nifty plus sign you see).
- To dynamically change the colors, I used the SWITCH function from my Microsoft Access days on the Background Color property: =Switch(Level()=0,"White",Level()=1,"LightGrey",Level()=2,"Khaki",Level()=3,"Pink",True,"LightSteelBlue").
- The indentation was effected by adding a formula to the Padding (Left) property: =20 * Level() & “pt”
- To collapse the hierarchy, I merely toggled Visibility of the group based on the name field. I prefer to have only the top level show initially.
A Final Note
I’ll be posting the RDL file of this report separately. If you plan to use it, test first. This worked for my specific situation but I don’t pretend to think it is a silver bullet or that its stress tested. This same principle could be applied to business units as well, which can become very nested in their own right. Look for a future blog on that front after I take this for a spin.
If you have other reporting questions or are in need CRM assistance please don’t hesitate to drop us a line at info@customereffective.com.
by Customer Effective
