Drilldown Chart (Dashboard)in SSRS without using Sub Reports.
Let’s develop a drilldown chart
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
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. 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
It is true that a picture is worth a thousand words – especially when business is trying to find relationships and understand in their data, which could include thousands or even millions of variables and representation of data visualization through the chart reports become very urgent to take the right action for the business. A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will provide the additional details of that particular category.
ReplyDeleteSSRS - Charts based drill-down Report