October 19, 2005 at 10:29 am
Hi ALL,
I am new to this forum.Hope I will get answers.
I have a requirement like this.
I have 3 tables:. county table(state table), City Table and voter table.I need to generate report like this.All the voter information from each city and each state.
I did like this: In the dropdown I populated all the counties like adams,bergen,phily.. I am getting the reports for what ever the state user selected.that is fine.
Problem Area:But say if Adams user come ,he has to see only adams county report not other reports like bergen,phily. How to filter the data based on user.how to give access to that user who belongs to same county but he can not see other counties data.
Questions:
1.
How to give security for users at parameter level in sql reporting services?I need to figure out the best way to set this up so that only certain users can see certain values in the drop-down?
2.I want to get the total voters in the city and I want to get the no of voters in each county.
3.How can I add 'ALL' parameter to other report parameters in dropdown.users want to print all counties data some time?
4.How to disable preview and print options for users.users should not see this report?
thanks
ravi
October 25, 2005 at 10:51 pm
...Also new to the forum, but trying to give back because I've gotten so much out of this site.
We have several RS reports that similarly have data driven security.
1. The easiest way, if possible, for you to manage the users/parameters is to have some kind of relationship in your data source between the users ID's and the counties that the UserID is able to see. In RS you have built-in access to the Windows Network Account name of the user running the report (User!UserID). This is returned in the format DOMAIN\USER_NAME (see books online). If in this user/county table you store this network account name, then on the query that you base your county drop down on, you can filter by a query parameter, say @user-id, that you set up to equal User!UserID in the properties of the dataset, thus returning only the counties the user has access to.
2. Use the RS aggregate functions in the group or table footer on yor report (like count, sum, countdistinct)
3. The common method for this is to UNION a select statement that has NULL for the value: see http://blogs.msdn.com/chrishays/archive/2004/07/27/AllParameterValue.aspx for a good walk through on this method. One consideration for your implementation is that you would still want to join to your County/UserID table on the dataset that the report is based off of to prevent users from seeing counties' data they are not allowed to see (when they select (All))
4. If you are using the Report Manager, you don't have control over the preview/print options. If you don't want a user to see a report at all, then you should manage security on your Report Server for the specific report.
Hope this is helpful.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply