Dashboard In SSRS

Drilldown Chart in SSRS without using Sub Reports.

In SSRS as we develop drilldown reports in the same way we can develop drill down charts. Once you click on the main chart the corresponding detail level chart should display on the same report without using Sub report.

Let’s develop a drilldown chart

I have shared Dashboard.rdl with respect to this post you need to set your data source (AdventureWorksLT) Download

We have one bar chart showing total sale yearly. Once you click on the any year bar the corresponding pie chart should display with product wise sale.

Steps to create a drilldown chart.

1. Create a new report and name it dashboard with your shared data source (I am going to use sample Adventure works database)
2. Create a dataset with the following query
SELECT   Floor (SalesLT.SalesOrderDetail.LineTotal) as Sale, DATEPART (yyyy, SalesLT.Product.SellStartDate) AS Year FROM  SalesLT.Product INNER JOIN                         SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID = SalesLT.SalesOrderDetail.ProductID
3. Now you need to add a bar chart to your report
4. Right click on the chart and set the chart properties

 

Now our first bar chart is ready to preview.

Our main task is to create a sub chart on clicking on the year bar(Bar Chart) corresponding pie chart should display with information.

5. Now create another data set for the second chart and name it Product.

SELECT   top 5    SalesLT.ProductCategory.Name, Floor(Sum(SalesLT.SalesOrderDetail.LineTotal)) as LineTotal FROM    SalesLT.Product INNER JOIN  SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID=SalesLT.SalesOrderDetail.ProductID INNER JOIN  SalesLT.ProductCategory ON SalesLT.Product.ProductCategoryID= SalesLT.ProductCategory.ProductCategoryID WHERE        (DATEPART(yyyy, SalesLT.Product.SellStartDate) = @year)

Group by SalesLT.ProductCategory.Name

 The above query has parameter named year

6. Now drag another pie chart on the report and map it with the new dataset.

7. Right click on the chart and set its properties

8    8. Pass the parameter from main chart(bar) to pie chart

   How to pass the parameter from main chart to detail chart (pie)

    Now right click on the bar and then click on the bar chart series properties.

8.1   Go to Action tab and click on option Go to report.
8.2   Choose the same report i.e. Dashboard
8.3   Click on Add button and add parameters the report

9. Set some default parameter to your report so when you preview your report it should not ask you for        parameter it should render with    some set of parameter.
10.Go to the year parameter and right click on it and set default value to it as -1

 

11. Now we need to set some visibility to second chart so when you run your report first time only bar chart  should display on clicking     on the any bar of the first chart than only second chart should display.
12. Now you right click on the Pie chart and then click on the chart properties.
13. Then you click on visibility.
14. On show and hide based expression place this 

=IIF(Parameters!year.Value>0,false,true) 

Now you report is ready to preview
For the look and feel of the charts format your charts as per your requirements.
Preview your report  first you will see this chart in pic. Below

 Click on any year bar  you will have your pie chart along with bar chart