Creating a Drill Down Report with SQL Server 2008

This report is based on the AdventureWorks sample database

Step 1: Create a new shared data source for AdventureWorks database

Step 2: Create following query

SELECT    
Production.ProductCategory.Name AS Category,
Production.ProductSubcategory.Name AS [Sub Category],
Production.Product.Name AS Product,
Purchasing.ProductVendor.StandardPrice,
Purchasing.Vendor.Name AS VendorName
FROM  Production.Product INNER JOIN
Production.ProductSubcategory ON
Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
Production.ProductCategory ON
Production.ProductSubcategory.ProductCategoryID =
Production.ProductCategory.ProductCategoryID INNER JOIN
Purchasing.ProductVendor ON
Production.Product.ProductID =
Purchasing.ProductVendor.ProductID INNER JOIN
Purchasing.Vendor ON
Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID AND
Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID AND
Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID

Step 3: Now create a simple report in the table in the following manner

The report preview will look like

Step 4: Let us add a group for Vendor

Step 5: After giving the name to the group as ‘VendorName’, making the font bold and inserting a row in the group as shown above, the preview looks like

Step 6: From Row groups > select VendorGroup details (group Details and not group header) > go to Group Properties > select Visibility tab > select Hide > click on Display can be toggled by this group item and select the name of the group

Step 7: We can also change the drill down and make it reverse by setting ‘InitialToggleState’ to True and by selecting the group properties (header), visibility tab and click Show