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 :

?

1 2 3 4 5 6

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)

?

1

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

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.

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

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

?

1 2 3 4 5 6 7 8 9

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.

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

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

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.

9. A Series Properties window pops-up. Select Action from left pane.Then selectGo 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.

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

Your results looks like below :

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

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