SSRS - Implementing Drill Through action in the same RDL

Scenario:

           User wants a SSRS Chart report which will display the Product Category wise Sales in the initial view. On clicking of the bar chart, the report should display the Product wise Sales of that selected Category.

          We can enable this drill through action in a single RDL as given below.

Note :  I haven't given the SSRS chart creation steps detaisl by considering that the reader knows how to create SSRS Chart reports. 

To implement the above said requirement, we can do some tweaks in the SQL query if the report data set and achieve the same.

The given below sample query can be used in the data set, which will return the Category wise sales if the in parameter (@ProdCategory) value is not available in the Category table.

if exists(

select 'x' from dbo.Dim_Category  where Category=@ProdCategory

    )

begin

select ProductName as Item ,SalesAmt as Sales_Amt from dbo.Dim_Category where Category=@ProdCategory

end

else

begin

select Category as Item,sum(SalesAmt) Sales_Amt from dbo.Dim_Category group by Category

end

Follow the given below steps:

-In the above created Dataset, @ProdCategory is parameter and its default value is 'a' initially.

-Create a Chart report and drag the field Item in to the category field and Sales_Amt into the Data field of the chart.

-In the Action pane write an expression to Go to this same report in the Chart series

-Pass the Field ‘Item’ as a parameter to the report

-On clicking on the Category that Category will be taken as a new parameter. And new result set for the selected Category (Products under the selected Category) will be produced in the same report.

-Now, when the report initially gets opened, the default parameter will be passed to the data set and the value will not be available in the Dim_category table, and the report will generate Category wise Sales Amount in the Chart.

-If the user clicks on the Category series, then the selected Category will be passed as an in parameter to eth data set and the selected categories Products will be returned by the data set and the chart will provide the Product wise Sales amount.