Creating a Drill Down Report with SQL Server 2008

Tweet

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

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: