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.

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

Steps to enhance the report

Step 1. Add Parameter

Step 2. Set available values

Step 3. Set default values

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

Step 4. Add new columns

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.

Step 5. Change visibility

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

Step 6. Change grouping

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 theIncludeItemDetailsparameter is Yes (Y). I did this using anIIF expressionsetting it toIFIncludeItemDetails=Y then grouped using field value else don’t (0). Again, I did this for both fields.

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.