Home > SSRS Expert > SSRS #39 – Drill down or Drill through 2?

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: