SSRS manipulation: Counting your groups

Wow! Now we're really rolling... almost talking about code!  It is important that we talk about using the tools around the code as well, so this tip involves Reporting Services. So I report a bunch, and I put together this awesome report with drill downs, but I wanted to show the count of each drill down. It took some trial and error, but I found using RunningValue with CountDistinct doing the trick! Let me show you some snaps.

Here's what the report looks like before expansion. Note that in the rectangle there is a basic roll up of the counties that returned data for the report:

And here again, more roll up for the zip codes that returned data:

This can be achieved as stated above with a running value using count distinct, especially on the zip code area that had multiple objects returned for each zip code to be counted.  I used the following code for the zip code counts as an expression:

="Total Zip Codes: " & RunningValue(Fields!PKUPZIP.Value, countdistinct, "table1_County")

Note that the grouping is actually the parent group in this case.  That was sticky to figure out, as I kept getting out of scope errors until I had the right grouping.

I hope to continue to do a code based update every Wednesday, and a Monday morning quarterback analysis post to start each week.  And don't forget, more poetry on Friday!

Woof, woof.

Jason