August 05, 2009

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

Production.ProductCategory.Name AS Category,
Production.ProductSubcategory.Name AS [Sub Category],
Production.Product.Name AS Product,
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


Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter



4 Responses to "Creating a Drill Down Report with SQL Server 2008"
  1. ram said...
    December 16, 2010 at 9:04 AM

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

  2. Anonymous said...
    January 30, 2012 at 12:35 AM

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

  3. Anonymous said...
    May 31, 2012 at 10:41 AM

    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.

  4. Snowball said...
    March 15, 2013 at 1:50 AM

    Just a detail in Step 5:

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


Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions