SSRS #41 – Show 3 Different Report Views
I’ve written three posts about how to create a drill down report that allows users start from a summary level and drill down to account details on the same report, without creating separate reports for summary and account level detail.
While showing summary view is great for managers who are interested in stats only, it’s not always a good news for users who need to reconcile/validate the data.
To satisfy business users whose primary interests in the report are slightly different, I’ve decided to use the same drill down report, but allow users to choose a report view that is most convenient for them. By that way, because I am not creating more data set, the report performance will remain the same no matter how many views I have in the report, at least in theory. Although I didn’t do benchmark test, I didn’t notice performance deterioration.
Here are a few simple steps to create multiple views on the same drill down report (using the same data set). In my example, I’ve created 3 different views, one is summary at supervisor level, one is at the team member level, one is at the account detail level.
Step 1: create a report parameter REPORT_VIEW, with 3 options below, and a default value.
Step 2: create 3 tables/matrixes that use the same data set. Each table/matrix will show your data at the different level.
Step 3: to conditionally show only the user selected view, I use the Visibility property for the Table with an expression, in which False means to show the table.
This is how the report looks like when you run it.