Recently one of customer had below requirement:
“Based on User or User Group Membership some columns, Column Groups or Row Groups of a report should be hidden. If condition is User Based only then it could be achieved in SQL Server 2005 or SQL Server 2008 Reporting Services out of the box with out any customization or custom code. “
A column Hidden could mean
- Column is not visible in End Report (Both Column Headings and Column (domain) content is not visible.
- Column is visible and content with “No Access” or “NA” to indicate user does not have access to that column.
SQL Server 2008 Reporting Services can deal with both options but with certain limitations . I will deal with both these conditions at the end.
Here is how we thought it would be achievable
To achieve this below are steps:
- Add a New Report Item
- Create a Data Source Connecting to SQL Server instance that host AdventureWorksDW database
- A Data Set with query ( SELECT DimEmployee.*
- A Simple Table Report with No Grouping.
- Drag EmployeeKey for Employee ID
- For Employee Name write expression in value (=Fields!FirstName.Value + " " + Fields!MiddleName.Value + " " + Fields!LastName.Value)
- DepartmentName for Department
- BaseRate for BaseRate Columns
- Right Click on BaseRate Column and go to properties (press F4 function key)
- In Hidden Properties of Column write below expression
- Based on above condition columns and their corresponding rows would either be visible or hidden and this above expression can be expanded further to include more complex conditions.
Hope you got idea how to hide columns, but there is a major issue with afore mentioned steps:
- What if column to be hidden is not at the end of report but somewhere in middle i.e report has some column after column that is hidden based on expression. If such cases persists (as I reckon) it would be frequent, the report aesthetics would go for a toss .
- To get this behavior add a column after base rate and run report (ensure hidden property evaluates to true). See below Base Rate is blank
If we write expression at Cell Level instead of Column Level (which may be true for most of cases, Column heading would be present but cells under it would be blank)
The top one is worse off than subsequent one but more elegant approach compare to both of them would be if we columns are hidden Report automatically renders itself as if not column existed there in first place.
Also I have checked with column Width and Column Height (Under Size), Height and Width, but there is no place were one could write an expression. If it were to be present there we could have make column width to 0 (analogous hiding columns in excel sheet)
- One workaround would be to put hidden column at end but there would lot of if and but conditions where this would fail and also it is not always practically possible to put all such columns at end for readability purposes (for those users who have access)
- Generate 2 reports one with column and others without columns and give access at report level. (Then there is not point of this post at all)
- The other method is instead of hiding columns and their related data, if we can have expression written in value property of cell that if it evaluates to true then show data else show “NA”. It would also solve business problem but again users who have no permission would get “NA” . If this works then remove expression in hidden property of columns and instead write expression as below:
This is one of the methods I have found for hiding columns not sure if there are other methods though.
Though above method works for users, it is not always users are directly given access to reports unless of course if it is for small set of users. Typically SSRS deployments are for entire enterprise reporting, in such scenarios it becomes difficult to manage individual users instead permissions are controlled through group membership i.e Users are added to Windows user Groups and Windows User groups are in turn given access to Reports and Report Folders.
That would be the objective of next blog where based on user information we would need to get Group Membership either from AD or from a locally stored Database. But once membership is identified, columns are hidden / visible based on expression as explained in this blog.
Any other ideas please feel free to send mail to firstname.lastname@example.org