Drill Down Chart Report in SSRS

Drill Down Chart Report in SSRS

In this article I will show you how to create Drill Down Chart Report in SSRS.

1. First of all open Visual Studio 2010 and open your SSRS project. Then add report named as YearWiseSales in your project.

Now create a Dataset. In the dataset, we will use following query :

SELECT      DATEPART(Year, SOH.OrderDate) AS OrderYear,
            ROUND(SUM(SOD.LineTotal),2) AS Sales
FROM        Sales.SalesOrderHeader SOH 
INNER JOIN  Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
GROUP BY    DATEPART(Year, SOH.OrderDate)
ORDER BY    DATEPART(Year, SOH.OrderDate)

2. right click on report area and go to Insert–>Chart.& select Column from window. Then click on OK button.

2-Drill Down Chart Report in SSRS

3. Now in Chart Data select OrderYear in Category Groups & Sales in Values.To enable 3D effects, right click on chart and Check checkbox of Enable 3D.Then click on OK button.

3-Drill Down Chart Report in SSRS

Also give proper description to Chart Title & Axis Titles. Now your report design looks like below :

3-Drill Down Chart Report in SSRS-1

4. Now create another report named as ProductWiseSales. then create a Dataset. In the dataset, we will used following query :

SELECT      PC.Name AS ProdCat, SUM(SOD.LineTotal) AS Sales
FROM        Sales.SalesOrderHeader AS SOH 
INNER JOIN  Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID 
INNER JOIN  Production.Product AS P ON SOD.ProductID = P.ProductID 
INNER JOIN  Production.ProductSubcategory AS PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID 
INNER JOIN  Production.ProductCategory AS PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE       DATEPART(Year, SOH.OrderDate) = @YEAR
GROUP BY    PC.Name
ORDER BY    PC.Name

5. Now right click on report area and go to Insert–>Chart.& select 3-D Expleded Pie from window. Then click on OK button.

5-Drill Down Chart Report in SSRS

6. Now in Chart Data select ProdCat in Category Groups & Sales in Values. Now your report design looks like below :

6-Drill Down Chart Report in SSRS

7. If you want to add title on Legend, Right click on legend and select Show Legend Title.

7-Drill Down Chart Report in SSRS

Give Title like Product Wise Sales (In Millions). Also give Proper Chart Title.

8. Now Open first report i.e. YearWiseSales. Right click on Series & select Series Properties.

8-Drill Down Chart Report in SSRS

9. A Series Properties window pops-up. Select Action from left pane.Then select Go to report. Then select report from dropdown list of Specify a report (i.e.ProductWiseSales).

Then Add parameter & select value from dropdown list of value. then click on Ok button.

9-Drill Down Chart Report in SSRS

10. So we have done with our drill down chart report. To see the results click on preview tab.

Your results looks like below :

10-Drill Down Chart Report in SSRS

To see the year wise result click on the data bar of year.

10-Drill Down Chart Report in SSRS-1

Congratulations! We successfully completed Drill Down Chart Report in SSRS.

2 thoughts on “Drill Down Chart Report in SSRS

  1. Thanks for this informative post. Do you know if it is possible to create a drill down chart report that would display the underlying dataset as a simple table rather than another chart when an area of the chart is clicked? Similar to how an Excel pivot chart works.

Leave a Reply