Search code examples
sql-serverexcelreporting-servicespivot-table

Emulate pivot table double click to show detail in SSRS

I have an excel report that basically is a SQL query to get a table of data, then there is another page that shows a pivot table to summarize the data. When I double click a cell in the pivot table, I get a new sheet that shows the data for that row/column of the pivot table only.

Is there a way to emulate this in SSRS? I know you can emulate pivot table with matrix but I want to emulate the double click to show detail.

I was thinking best thing would be make a separate parameter for the data and another for the matrix in SSRS but seems cumbersome.

Solution

  • You can do this with a single click using a "go to report" action. It's not the most elegant thing in the world but it will work.

    Let's say you have a simple table, CustomerSales by date

    CustomerID Year Date Amount
    1 2019 2019-01-01 10
    1 2019 2019-02-28 11
    1 2019 2019-03-15 12
    1 2020 2020-01-20 13
    1 2020 2020-06-30 14
    1 2020 2020-07-31 15
    2 2019 2019-01-01 20
    2 2019 2019-02-28 21
    2 2019 2019-03-15 22
    2 2020 2020-01-20 23
    2 2020 2020-06-30 24
    2 2020 2020-07-31 25

    Now we want to aggregate this in a matrix in SSRS by year so we create a column group that groups by Year and end up with a report that looks like this.

    CustomerID 2019 2020
    1 33 42
    2 63 72

    Now if we create a new report that accepts a CustomerID and a Year as parameters.

    The report can be a simple table over the same sales table and the dataset query would look something like.

    SELECT * FROM CustomerSales WHERE CustomerID = @CustomerID and [Year] = @Year

    Back in your main report, in the matrix, we can set an action on the data textbox to "Go to Report", set the report to be the name of the details report you created above and the parameters for CustomerID and Year will simply be the fields from the main dataset which should be visible from the drop down list.

    Now when you click on a number, it will open the new report showing the data that makes up the data in the matrix cell.

    So, in the example above, if you clicked 72, it would automatically pass 2 as the CustomerID and 2020 as the year. When the drill down report appears it will how the 3 matching records.

    Just be sure to use the "Back to Parent" button in the toolbar, not the browser's back button or you'll have to run the main report again.