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.

SSRS – How to toggle the details with Invisibility property for TableRow?

SSRS – Drill down or Drill through‏?

SSRS – Drill down or Drill through ‏ 2?

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.

SSRS #40 – Use Table Filter to Conditionally Show Rows

I recently had a need to show three different view in a employee productivity report with the same data set, two summary views and one account level detail view.

For the two summary views, employees are listed regardless they have data or not in the date range. This will allow the report to do “forced ranking. I’ll blog more about “forced ranking” later. Because I had to show all the FTEs in different grouping, I had to create some dummy rows. I’ve written a post, SSRS – Drill down or Drill through ‏ 2? discussing the approach.

Now if I use the same data set to show the data in account level details, there is no need for forced ranking any more. Now I want to hide those dummy rows.

This is a simple task in SSRS. There is no need for a new data set. Create a new table in SSRS, using the same data set, with a filter.

The Expression for the table filter is this:

=IIF(IsNothing(Fields!DECISION_DT.Value),"HideRow","ShowRow")

Note: IsNothing is VB’s equivalent to IsNull in T-SQL.

With the above filter, only rows that have a DECISION_DT will be shown in the account level detail view, and all the dummy rows will not.

SSRS #39 – Drill down or Drill through 2?

Continuing from the last blog.

Challenge

Show all FTEs under each supervisor regardless if the FTE has done anything or not in the date range.

Solution

Create a stored procedure to add fake data rows for the FTEs who have no production data for the the date range. No need to add those fake rows to the underneath SQL table though. We just need to trick the reporting services to show the FTEs’ names.

There are a few challenges here.

1) If you have groupings in your report, you will need to create those fake empty rows for each group.

2) We will need to use the Cartesian join or cross join to create those fake data rows.

3) You will not have the real data at the detail level for those fake data rows, but you do need to have the higher level data populated for them, so that your report will show the FTEs correctly under each group.

It’s a very straightforward procedure.

SSRS #38 – Drill down or Drill through?

This is a typical drill-down report. Users can click the plus sign to go from summary levels to detail levels.

On the other hand this is a typical drill-through report, where hyperlinks are provided for each row so users can drill through from summary to detail.

I’ve created both types of reports. But recently I’ve found myself preferring the drill-down reports over drill-through reports. Business users seem to like it. I find that it’s also easier in implementation too.

The primary reason business users like the drill-down reports is that it makes the data verification/reconciliation process so much easier for them. Traditionally summary level reports simply aggregate the data without providing the level of details for users to verify the data, or to reconcile the data between different sources. Allowing business users to verify/reconcile the data gives business users the confidence in the data they never had before.

It’s easier to implement and maintain, because

1) I do not need to maintain summary and detail level reports separately

2) I do not need to create separate SQL tables for summary and details. Only the detail level data needs to be stored.

However, there is one catch about not creating summary level data but needing to show summary level data. This means that we need to do what we can on the report designer front to aggregate the data. Aggregating data is straightforward in SSRS with the grouping feature. However, when business wants to see all FTEs under each supervisor regardless if the FTE has done anything or not in the date range, I had to create a stored procedure to do that. More to come on how I created the procedure in next blog.