I am using SSRS 2014, aka Report Builder 3, to develop reports at work. The one I'm currently working on requires a "user-level view" and a "supervisor-level view". The difference between the views is that non-supervisory users don't need to see the first three columns and should only see data related to themselves, not other users. Supervisors do need to see everything, but also want to be able to limit what's on-screen at any given time.
I can read the user's role and set visibility based on that. The problem is that I can't hide the columns because the option is not available for them. The option IS available for the columns I'm not grouping on, so I assume that this is because the rows are being grouped on those first three columns. Is there a way around this?
CodePudding user response：
As far as I know there is no nice way of doing this. You can actually hide columns that are not on the details rowgroup even though the right-click context menu does not show the option.
If you left click on the column head to select the entire row, then use the properties panel you can set the Hidden property to an expression.
In fact, what you are actually doing is setting all the textbox's hidden properties. Unfortunately this literally just hides the textboxes so it just leaves a blank area. This might not be too bad if its at the start or end column but if it was in the middle there would be a gap.
In my sample report with the column not hidden
and then with it hidden
Sometimes you just have to rethink how you present the data. In my example, for instance, I could move the data I want to hide (Year) to the next column across but still in the same row group, then delete the first column. Like this... (I left both version in place so you can see the differences)
Now when I run the report with everything visible it looks like this.
Now, I set the ROW visibility of the row with the [Year] field and when I run the report I get this...
CodePudding user response：
use a drop down option as a parameter and combine it with the column visibility event. use an iif to set column visiblity to true or false based on the parameter
use a table lookup in ssrs based on the user role to set the column visibility to on or off. Supervisors have certain column visibility set to off based on lookup set. The lookup set is like a subquery running in ssrs
in code you assign true or false to the hidden property by using an iif. It is a property so you don't assign true or false directly