Custom Code and Variables for users in SSRS 2008

Posted on 2012-09-19
Medium Priority
Last Modified: 2016-02-18
I am trying to implement a system we had at another place of work where I have a master/shell report that sets permissions for users for reports that will be included as subreports.  I figured I can grab the username and give them a group identifier that will restrict parameter visiblity and such that whoever is logged in can only see the information that pertains to them or their team or their projects.  

Right now I have multiple copies of the same reports based on if they are team leaders, project managers or executive staff and I'm having to try and go around to each and make changes and its not effecient so I want one master report.  In JSP we had headers that defined what people could see.  I'd like something like that.  Executive should have free access to change parameters but others should only see their stuff.  

Our database/sharepoint was setup by outsiders and within we are not well versed enough to implement some of the features that would make my life easier such as stored procedures and SSAS and since I'm working with LIVE data (no sandbox yet) what I do now has to be limited until we have a setup where I can experiment without fear of destroying what is already in place.  So I wanted to keep it to everything I can do in Bids 2008.  

I've read about using parameters to store UserID but I thought a variable might be better.  Any suggestion on the method I should follow?  I don't need how-to's as much as 'this is the systematic approach to follow' suggestions.  Any help is greatly appreciated!!!!
Question by:HSI_guelph
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 1000 total points
ID: 38416669
LVL 22

Accepted Solution

Nico Bontenbal earned 1000 total points
ID: 38416778
You can use the user!UserID expression to get the user opening the report. And for the Dataset of a report an expression can be used (with the Fx button next to the query box on the Dataset Properties window). So I suppose something like this should be possible:
=iif(user!UserID="UserA","Select * from project","Select * from project where user = " & user!UserID)

Open in new window

This will return a different query based on the user. If you got this example working you can improve it. With some custom code you might be able to check if a user is in the team leaders or executive group and return the level of authorization based on this group membership. When you use this technique for all the Datasets in your reports you'll be pretty sure users only see the data they are allowed to.
You can't display or hide parameters on a report based on the user. So all your parameters will be visible. But for some users some parameters will have less values available.

Author Closing Comment

ID: 38418249
Thank you both for the replies!!  I've got an idea of how to move forward now.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Screencast - Getting to Know the Pipeline
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question