September 19, 2013 at 12:15 pm
HI,
I need to create a report in which I can show all the column to my manager but if supervisor want view the same report then I need to hide 1 column from that report.
Any suggestion or help would be much appreciated
Thanks
September 19, 2013 at 12:43 pm
http://sqlmag.com/sql-server-reporting-services/ssrs-how-create-reports-user-protect-sensitive-data
There seems to be some important security considerations here.
I would attempt to pass the userid as a parameter to a stored procedure and NULL the column there rather than setting a columns visibility property in a report.
September 19, 2013 at 1:33 pm
OPTION 1
The down and dirty way (not recommended) would be to set the column visibility based on an expression like so:
=User!UserID <> "Manager #1" AND User!UserID <> "Manager #2"
OPTION 2
1) Create a table for the authorized users.
2) Create a proc that takes the built-in SSRS parameter [&UserID] and returns a true/false based on if there is a match in the authorized users table.
3) set the column visibility based on that
The query
-- your proc
DECLARE @userid varchar(100)='blah/bob' --This would come from the built-in SSRS param: [&UserID] as a parameter
DECLARE @authorized bit=0;
IF EXISTS (SELECT * FROM authorized_users WHERE au_name=@userid) SET @authorized=1;
SELECT @authorized AS something
The SSRS expression for column visibility:
=Parameters!authorized.Value=0
OPTION 3
The best way I can think of might be to combine Option2 with Chrissy's suggestion (NULL the columns out); the query that feeds your report would look something like this:
...
IF EXISTS (SELECT * FROM authorized_users WHERE au_name=@userid) SET @authorized=1;
SELECT col1, CASE WHEN @authorized=1 THEN col2 END AS col2
FROM sometable
-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply