SSRS reports for parameters for admin vs users

HSI_guelph used Ask the Experts™
I've got an employee summary report running that we send each week to the team leader and administration and will be rolling it out to employees as well.  My boss wants lots of extra options and customizations that we would allow someone of his rank but we would restrict the general users options to what is specific to them alone.  I'm wondering what would be the best way to handle this situation?

I can get the user's id from the Built-in Field User ID and I know I can restrict datasets and such but would it be better to have seperate reports as opposed to one report with restrictions?  If I use one report then I only need to change one file if I update it but the parameters my boss wants should not be available to individual employees (who should only see themselves and their team).  Is there a way to make a parameter visible if a certain condition is met and not visible as a default so that team members can't choose a different team in the drop down?

I've created webpages with Admin and regular User options in JSP before in external include files and headers, should I try to do the same thing in SSRS?  Is there a standard practice that is preferred by report designers that optimizes report generation while maintaining security for confidential data?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ValentinoVBI Consultant
Most Valuable Expert 2011
Parameters cannot be hidden based on a condition so you'd either need to implement two versions of the report or develop some custom code to handle the parameter part yourself, similar to what you mentioned in your last paragraph.

If you go for the custom code, have a look at the ReportViewer .NET control:


What would be the best practice?  Two seperate reports or customizing one report?  My boss seems to want a lot of functionality that we don't want available to everyone in the company.  Would it be possible, and feasible, to create one report that can be called within container-type reports?  Where I'd have an admin page and a user page and they would both call the same report to populate the center/body of the report which would be displayed according to parameters or variables defined in the container page?  That is sort of what we did at the college.  The container page would be persistent throughout the user's session and they could click links within the body to refresh the page with new inner report data.  I might need more than one container page, one for each group linked to User IDs, or I could have a container page related to reports that tie together, like team client reports or employee reports.

Does this sound like a good approach?
Project Leader

Choosing approach depends on level of customizations.

1. If the layout (visibility of columns/rows, formulae used in few columns) changes minimally based on the report parameters, then you should customize same report to suit your changing needs by using a parameter.

2. If you find that you will need to put lots of formulae (expressions) to control the behaviour of the report based on the parameter, then it is better to make different reports for different conditions.

3. If it is easier to alter the layuot of first report by deleting/adding few columns to create second report, than setting all columns in first report and then trying to control the layout, then it is better to create different reports.

In your scenario, if your requirement is to control the values available in your parameter based on the user's team, this can be easily achieved as below:

1. Create one parameter (pSecurityLevel) and mark it as "Internal". Use an expression to find the level of the logged in user (based on UserID as you said) like Admin, Manager, Team Member, etc. as required.

2. Create a dataset that uses this parameter to restrict the list of available values for Team Name drop down.

3. Use above dataset as the source for your Team Name parameter. This will restirct the list of teams available based on user's level. Optionally, you can also assign default values to this parameter based on pSecurityLevel parameter created earlier.

Hope this helps.



Thank you both for your input!!  I truly appreciate it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial