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:
Select the second option:
This will be what left of your report when you delete all the columns from the grouped section:
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:
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 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! ツ