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

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]


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



 
  Feedback:

comments

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-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions