I recently wrote a blog on how to use SSRS to show the hierarchy of reports and their sub-reports . I strongly recommend reading that blog first as that will show the mechanics of this exercise in more detail (I hope!). I actually used that same SSRS report as a template for this business unit report. I merely changed the FetchXML, updated the fields in the table, and updated the grouping in that report. It will probably take me longer to write this blog post than it did to make those changes. And I did add one feature which I think is important to show in a report on business units, the count of underlying business units at each level.
Why Would One Need This?
If you use business units, are thinking about changing your business units or just need to audit periodically how your business units are stacked up then this is important. And much like with reports, you can’t see this represented in the front end. This blog isn’t about how business units are used, but they are an integral part of CRM security.
Again, Cart First
Just like last time, we’ll show the finished product. Fans of my last blog (please stop with the letters) will note the same delightful color scheme, indentation, and collapsibility of the hierarchy. I’ve also highlighted a new column I added which as you will see is counting the number of underlying business units at each level. Go ahead and count for yourself. Note also that the business units at the bottom of the totem pole have zero underlying business units. Finally, I added a hyperlink to the business unit record in this report, which doesn’t make sense on my last example. I’ll be explaining how the count feature works in this blog, but not the hyperlink feature. Searching the Customer Effective/Hitachi blog will no doubt find an example of this, possibly even written by myself. More likely though, it was written by my esteemed colleague, Microsoft CRM MVP, and great dancer Joel Lindstrom. It’s best that you don’t ask.
Quickly Getting Back To The Subject…
I mentioned that it’s going to take longer to write this blog then to make the changes to the report. The first report took a bit, but this one was much faster as I could make a connection in terms of how to set up the grouping, the details, and the recursive parent. I merely swapped out businessunitid for reportid, and parentbusinessunitid for parentreportid. The two entities have one thing in common: the parent record is contained as an attribute of each entity (parentbusinessunitid, parentreportid). That is key in order for this to work. For good measure I added in a third column, if only to experiment, but that didn’t take all that much effort.
The Recursive Count
In order to make this work I added another column and used the Count expression as follows:
This is jibberish which essentially means: “Computer, count all the occurrences of the businessunitid within the Details group and within this level of the hierarchy. And then subtract one, so you don’t include the top level of whatever point in the hierarchy.” I derive two points from this: (1) It is fun to say this in the voice of Scotty from Star Trek, a la the scene from the Star Trek movie with the whales in it. Ahh, the eighties. (2) It took the computer a lot less characters to get the point across than it did for me.
The Takeaways Here…
First, this whole exercise was made tremendously easier by countless people I’ll never know who contributed on the web. Consider this a thanks and an homage to you. Second, this gets easier each time and the principles of one hierarchical dataset can translate pretty easily to another. Third, a lot of peculiar things came out of Hollywood in the eighties. Fourth, while I used the Count function in this case, other aggregate functions come in handy and bring to mind other applications, such as a report showing the total potential sales by opportunity by the accounts in a hierarchy. Finally, recursion, while scary as a theory and practice, seems pretty digestible with SSRS. Now in the grand scheme of things these are simple datasets, but the reporting mechanism made it simple to get out as well. I wanted to challenge myself to just use FetchXML for portability purposes, and unlike SQL one can’t build hierarchies natively in that language. Enter SSRS to save the day.
I hope you’ve enjoyed reading this and that you find it helpful. If you need help with your own Microsoft Dynamics CRM environment, please drop us a line at firstname.lastname@example.org.