Hide And Group Columns In SSRS Using A Parameter

Ever had users come to you and request another version of a report just to add another field and group data differently? Today was such the day for me. I really don’t like to have multiple versions of the same report out there. So, I got a little fancy with the current version of the report and added a parameter then used expressions to group the data differently and hide columns. For those new to SSRS, I've embedded some links to MSDN to help you along the way.

Current Report

The report gives summarized counts by invoice date. It currently has a ROW group using date_invoiced and the detail row is hidden from the user.

SSRS

SSRS

SSRS

New Version

To complete the user request to have Item Codes and Descriptions added to the report, I need to find a way to group the data by Item and show Item columns without disturbing the current report that is currently used by many consumers.

To Do

  • Add Parameter
  • Set Available Values
  • Set Default Values
  • Add New Columns
  • Change Visibility
  • Change Grouping to group data using parameter

Step 1

Add Parameter

SSRS

Step 2

Set Available Values

SSRS

Step 3

Set Default Values – I want to make sure that my current users get their version of the report simply, so I set it to No (N).

SSRS

Step 4

Next, Add Columns. I was lucky that the fields (Item Code, Item Desc) the user requested to be added were already part of the dataset used, so no additional coding was needed for the stored procedure.

SSRS

Step 5

Next, change the Visibility attributes. You would want to HIDE the column when the IncludeItemDetails parameter is NOT YES (Y). I did this for both item columns.

SSRS


SSRS

Step 6

Next, I needed to change the grouping. The report is currently grouped by date_invoiced only. To make the data now total by Item, I need to group it by Item only when the IncludeItemDetails parameter is Yes (Y). I did this using an IIF expression setting it to IF IncludeItemDetails=Y then grouped using field value else don’t (0). Again, I did this for both fields.

SSRS


SSRS


SSRS

 

You will see it’s relatively simple to do and prevents a whole new report version from being created. For you beginners out there, it's a very easy way to start to minimize the number of reports you have to maintain. Try it.