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.
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.
Next add a tablix to the report. Put your metric data in the right columns and your highest level category in the left column.
You need to add a child group for any fields that you want to drill into. I’ll start by adding the Product Department.
This will automatically add the group to your tablix.
Next pull up the properties for the row group you just added.
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.
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:
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.
I hope this has provided a simple example of how to create drilldown reports in SSRS