SSRS 2008 Similar Users but Similar Reports
I have groups of users from five different areas (Area1, Area2, Area3, Area4 & Area5) that should not be able to see each others reports. The source of data is all from the same database so all the areas information is combined in the same table. The reports contain the same information except for the AreaOwner field. I could create five different folders and assign permissions to each folder. Then create five different reports and place one in each folder. This might work fine if it stayed to just five areas but when it gets to be 15 - 20 reports for each area with the only difference in the report being AreaOwner that is an awful lot of duplicate report creation/maintenance. Is there a way to have a single "template" to maintain each report type. I was thinking if the template could leverage some sort of variable based on who the user logged on as, the report would only show data for that users area. For example, JohnDoe is member of DomainGroupArea2 so when he logs on to the report site any report he runs would only show data that has Area2 listed as the owner in the data tables. I expect this to take a few replies to fully explain what I am trying to do but am hoping to get a discussion started.
February 17th, 2011 1:21am
There are two ways you can approach this , 1. You can filter the data at the dataset level by using WHERE clause for the Areaowner , by identifying the logged in user. 2. You can filter the data at the table or control level by setting filter criteria in the properties window. In this the first solution will the best , as the data retrieved from the database itself will be less and it will not load all the data and then filter in the front end as the second option does.
February 17th, 2011 1:29am
Hi, Do you mean you need to filter the data in the report based on user? If so, we can refer to the built-in field User.UserID to identify the user who runs the report, and then filter data based on the user ID. However, you may notice that we can only refer to the user ID but not user group in the report. In order to filter report data based on user group, I would suggest you refer to the similar thread at: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/094cd39c-5e06-4a41-ba4a-3d0bad2970e1 For more information about Filtering Data in a Report, please refer to: http://msdn.microsoft.com/en-us/library/ms157307.aspx Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.
February 22nd, 2011 4:12am