SSRS: Column groups visibility

Related to: http://www.ienablemuch.com/2012/11/ssrs-grouping-dynamically.html

Given the type of report below, the e-mail information belongs to the Person(grouped entity) not on the Sales section(detail entity). Perhaps you've seen the following scenario already and wanted to set the e-mail promotion visibility conditionally:

There's a limitation in SSRS, even on 2012 version, you cannot set the visibility of the columns that are on grouped section programmatically:

That option is available on details section only:

What we can do is to move those grouped columns to details section, and then adjust things accordingly, merging similar vertical values in particular. There's no mechanism to move columns in SSRS though. Just delete the columns from grouped section one by one:

Then when you are deleting the last column from the group section, you will be prompted with this confirmation:

Select the second option:

This will be what left of your report when you delete all the columns from the grouped section:

Then re-insert the deleted columns, right click the column, then select Insert Column > Left :

Then select the fields need to be re-added:

The Column Visibility option for columns that are on grouping entity(Person) is now an available option, that means we can hide columns programatically instead of deleting the columns permanently.

You can also override the sorting on your grouped entity(Person), right click SalesPersonID on Row Groups, and then select Group Properties...

You can also override the sorting of your grouped entity, by default if you don't override the sorting, the rows will be sorted by SalesPersonID

This is how your report shall look like:

Note the entity (Person) you are grouping on are repeated. To prevent the grouped entity on repeating, highlight all the cells from SalesPersonID to EmailPromotion columns, then on Properties(you can press F4 if Properties toolbox is not visible on your IDE), then select HideDuplicates, then select SalesPersonID:

This is how your report shall look like:

Now to give the illusion of vertical cell merging similar to the columns that are in group section, highlight the columns from SalesPersonId to Email Promotion:

From the Properties toolbox, select the BorderStyle's Top, then select <Expression...>, then put this expression:  =IIf(Previous(Fields!SalesPersonID.Value) = Fields!SalesPersonID.Value, "None", "Solid"):

Then on their BorderStyle's Bottom property, select the <Expression...> too:

Then enter this: =IIf(COUNT(Fields!SalesPersonID.Value,"SalesPersonID") = RowNumber("SalesPersonID"),"Solid", "None")

That expression basically detects if we need to put a border on the cell, we just need to put one if it is the last row of the group(the grouping entity is Person(facilitated by the "SalesPersonID" column))

This is now the final output as intended:

This is the Stored Proc used in the report:

CREATE procedure [dbo].[GetTheSalesOfSalesPerson]
as

begin

 select 

  h.SalesPersonID, p.LastName, p.MiddleName, p.FirstName, p.EmailPromotion

  , h.SalesOrderNumber
  , h.OrderDate
  , s.Name as CustomerName
 from Person.Person p
 join Sales.SalesOrderHeader h on h.SalesPersonID = p.BusinessEntityID
 join Sales.Customer c on c.CustomerID = h.CustomerID
 join Sales.Store s on s.BusinessEntityID = c.StoreID


 where h.OrderDate >= convert(date, '2008-01-01')


 order by h.SalesPersonID;

end;

Download the database from: http://msftdbprodsamples.codeplex.com/releases/view/55330


Happy Computing! ツ