Creating Drill Down Reports in SQL Server Reporting Services

The wait finally ended! The Okauchee boat launch, after being closed for remodeling for most of the year, finally opened back up. I had an opportunity to try it out last weekend, and I was pretty happy with it. They left the existing launch ramp from the previous configuration, and the pitch is absolutely perfect. They cleared out a bunch of trees and a disgusting bathroom, and replaced them with extra parking places, some grass, and a permanent port-a-john. I felt like they probably could have fit some extra parking places if they had made less of it grass. My main complaint is there is no way to stop and clear weeds after pulling out of the lake without blocking other parked trailers or rigs trying to drive out. In terms of fishing, I spent about three hours on the lake fishing for muskies with a buddy. We managed three fairly aggressive follows, but the fish were all pretty small. All in all, it was a great morning and it was awesome being back on Okauchee.

Okauchee boat launch
Today I had to learn to make a slightly more complex report in SQL Server Reporting Services (SSRS) than I’m used to. In order to mimic the functionality of an Excel pivot table, I needed to make use of the SSRS drilldown functionality. The first step is to create a dataset that you want to display. For my purposes, I am using two predefined business intelligence views, one that has invoice data and one that has product data:

SELECT  p.ProductClass ,
        p.ProductDepartment ,
        p.ProductNumber ,
        SUM(i.[Total Amount]) AS TotalAmount ,
        SUM(i.[Ship Qty]) AS ShippedQty
FROM    BI.dbo.FACT_Invoices i
        INNER JOIN BI.dbo.DIM_Products p ON p.[ITM Number] = i.[ITM Number]
                                            AND p.[SKU Code] = i.[SKU Code]
WHERE   i.[Inv Date] BETWEEN '2015-09-03'
                     AND     '2015-09-04'
GROUP BY p.ProductClass ,
        p.ProductDepartment ,
        p.ProductNumber
ORDER BY 4 DESC;

The next step is to set up a new report in SSRS and define the Data Source and your Data Set.
20151016 SSRS data defined Next add a tablix to the report. Put your metric data in the right columns and your highest level category in the left column.
20151016 SSRS tablix part 1 You need to add a child group for any fields that you want to drill into. I’ll start by adding the Product Department.
20151016 SSRS add child group20151016 SSRS add department This will automatically add the group to your tablix.
20151016 SSRS tablix part 2 Next pull up the properties for the row group you just added.
20151016 SSRS child row group properties Go to Visibility, and choose Hide. Toggle the checkbox to Display can be toggled by this report item:. Choose the row set of data that is the parent.
20151016 SSRS child group by visibility Next I add the Product Number data set and change the properties in the same way. This time I make the Department the toggled by report item. I format the report to make it look polished:
20151016 SSRS final design After deploying, you can now see there are plus signs before any unexpanded Classes and Departments, and minus signs after any that I have expanded.
20151016 SSRS final report I hope this has provided a simple example of how to create drilldown reports in SSRS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s