Custom Code and Variables for users in SSRS 2008

Posted on 2012-09-19
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
    LVL 22

    Accepted Solution

    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

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now