SQLTeam.com | Weblogs | Forums

Make default parameters selectable

sql2008r2

#1

Hi all,

SQL 2008 R2 and Report Builder 3

We make heavy use of linked reports, this saves us creating multiple rdl for different teams.

In linked report parameter settings page we make use of 'default values' a lot.

This works fine but when the linked report is run the user is unable to choose from the passed in multiple parameter values, the report just runs with them. They appear in drop down box ok but are not selectable, they just appear as a list.

So if I had a parameter called 'fruit' and set the default values as Apples, Oranges, Bananas, Strawberries then the report runs for these ok but there appears to be no way for the user to actually select within the parameter so that they wish to run it for Apples only.

I could place them in available values to make them selectable but this is done at rdl level only and the range of values may contain values that they should not see (medical application). The only way around it appears to be to create a rdl for each team with the parameter populated by available values which sort of defeats the purpose of linked reports.

Any ideas?

thanks


#2

is there a table in your databases that specifies which user has what permissions? are you in a windows environment with an active directory?


#3

Windows 7 with Active directory.

Permissions for reports are based on Groups and permissions are granted to each folder on SSRS.

Thanks


#4

in ssrs one of the cool things you can capture is the currently logged in user --> Built-in FIelds: User ID. this might be overkill but this is what I do in some cases there might be a better approach I am sure, this was a quick hack I needed to do with the plan to fix it sometime in the future. dump all active directory users to sql database, also create userGroups tables that maps to what user has what permissions or groups they belong to

then your stored procedure that populates the drop down, depending on which user is using the report and what group they belong will see only list of items they have permissions to see.

User
1, Buju Banton
2, Derry Coder

Groups
1, Medical Info
2, Super sensitive info
3, General info

UserGroup
1, 3
2, 1
2, 2
2,3