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

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

You’ve seen reports that look like this:

image

When the plus sign is fully expanded, you will see another level of details.

I’ve created reports like this, with summary at the top level, and details being expanded every time the plus sign is clicked. The plus signs can be nested.

To create  a report like this, you do not need to have summary data in your data set. As a matter of fact, your data set can be a direct pull of all the data at the lowest detail level.

For the above report, there are 3 levels:

  1. highest level: site, manager, supervisor
  2. next level: team member
  3. lowest level: account level detail

The data set is at the lowest account level. No summary at the team member and supervisor level is needed to create the report.

To create a SSRS report like this is very straightforward.

What prompted me to write this blog is what I see how other developers wrote tons of unnecessary SQL code, and created two RDL files, one for the summary and one for the details, with very messy navigational links between them.

Here are some simple steps to create a report with nested summary/detail levels. Remember your data set is always at the lowest detail level. The “secrete” is a TableRow property called “Invisibility”.

I set the property “Invisibility” like this  for the TableRow3:

image

For TableRow4:

image

This is my TableRow3, which is the my second level detail at the team member level.

image

This is my TableRow4, which is the lowest level detail at the account level.

image

The above snapshots showed you what you need to do.

Now here are the steps of how you are going to do it.

1. Step 1: create a table with only one detail group.

2. Step 2: create a top level group at the supervisor level.

image

This is “group 1”. Name the supervisor textbox “textbox_SUP_NM”.

image

3. Step 3: create another group under your first group.

image

This is “group 2”. Name the team member textbox “textbox_LAS_NM”.

image

  4. Now highlight your entire “group 2”, or TableRow3, and set the “Invisibility” property to “True” and “textbox_SUP_NM”.

5. Do the same for the detail group, or TableRow4, and set the “Invisibility” property to “True” and “textbox_LAS_NM”.

You are done!

Categories: SSRS Expert
  1. Supriya
    January 18, 2012 at 7:15 am

    In the scenario I am facing I will be expanding only one particular field(as per the parameter selection) while everything else will be in collapsed state. Here for the expanded value I want the drilldown button to be invisible. In short in must remain expanded at all times. Can you please look into this and see if this can be done.

    Like

  1. February 22, 2011 at 11:57 pm
  2. April 4, 2011 at 4:28 pm
  3. May 18, 2011 at 6:16 am

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: