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

clip_image002[4]

The report preview will look like

clip_image004[4]

Step 4: Let us add a group for Vendor

clip_image006[4]

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

clip_image008[4]

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

clip_image010[4]

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

clip_image012[4]


4 comments:

Unknown said...

I don't understand step7. I don't find the option initialToggleState...

Anonymous said...

I did not understand your article.Could you provide this in detail.

Anonymous said...

I don't see where that would handle "X" level of subcategories. Easy to do category - subcategory. Problem is that some may have 2 subs, some 3 subs, etc.

Snowball said...

Just a detail in Step 5:

The row we add is a total row of the group, placed above it...