Drill Down Chart Report in SSRS

Đây là 1 kỹ thuật trình bày report trong SSRS mà tôi đang tìm hiểu. Ý tưởng ở đây là từ 1 report cha dạng biểu đồ cột, ta double click vào cột, nó sẽ ra 1 biểu đồ chi tiết, như biểu đồ tròn chẳng hạn.

Để làm được ví dụ dưới đây bạn cần download và cấu hình database AdventureWork. Bạn vào link sau để download:
http://msftdbprodsamples.codeplex.com/releases/view/55330
Để làm được ví dụ như bài viết bên dưới chúng ta cần cài đặt Database AdventureWork. Tôi đã dùng AdventureWork2012 và đã làm thành công.

Xin đăng lại bài viết để tiện tham khảo sau này.

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)

?

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.

Nguồn: http://bhushan.extreme-advice.com/drill-down-chart-report-in-ssrs/