Hi, I have a report which uses uer!username for filtering the DS. HOwever I would like management users to be able to view the full report off all users. What would be the best practice for this?Namnami

Hi, I have a report which uses uer!username for filtering the DS. HOwever I would like management users to be able to view the full report off all users. What would be the best practice for this? I mcurrently added a boolean parameter for user filtered or all users report. The filter depends on this parameter too. Then I made a linked report with the above default hidden parameter of user filtered (so that one report would be available for management and one for specific users). I don't know why but when I run it in VS it runs correctly (according to this parameter) but on the reporter site no matter what value I give the parameter (all/ user specific) in the report parameters properties page it runs as user specific. Any idea why? Namnami

same question http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/fdec7f0d-4178-44b4-8541-990d4e982778 There are 10 type of people. Those who understand binary and those who do not. My Blog | Hire Me

You could use an AD query to see whether a user is in a certain group, or a have a hard coded list of values etc WHERE Data.Owner = @Username or @Username in (/* Your ad query here that returns a list of manager user names */) Josh Ash

Hi Namnami, Thanks for your posting. Based on your requirement, we can user the built-in "User!UserID" field to control the available values for the parameter. That is to say, when the user is a manager, the parameter has two available values -- "True' and "False"; however, if the user is a common user, the parameter has only one available value -- "False". In this way, you can set the filter of the report based on the parameter value easily. To create such a parameter, please follow the steps below:From the Report Data pane, create a "Visible" "Text" type parameter named "PM" for example. On the "Available Values" tab, select "Specify values", and add values like below:Label: False Value: False Label: =IIF(User!UserID=Domain\Manager1 or User!UserID=Domain\Manager2 or User!UserID=Domain\Manager3, True, Nothing) Value: =IIF(User!UserID=Domain\Manager1 or User!UserID=Domain\Manager2 or User!UserID=Domain\Manager3, True, Nothing) On the "Default Values" tab, select "Specify values" and add "False" as the default value. If there are many manager users and it is impractical to input each managers' UserID in the expression, we can add a "Hidden" multi-value parameter (MUser) which has all the managers UserID as default value, and then replace the above Label and Value with the following expression: =IIF(InStr(Join(Parameters!MUser.Value, ",") User!UserID), True, Nothing) For the default value of the "MUser", we can configure it to retrieve values from a query that is generated from a table which contains all the mangers' UserID information. Hope this helps. Regards, Mike Yin Mike Yin TechNet Community Support

Thanks, that's a great idea. What's really the best practice for limiting report data to be user specific (by username)? I feel it a bit unsafe to depend on parameter value.Namnami

Hi Mike Yin, Regarding your suggestion above I'm trying to make the hidden multivalue parameter be internal so it could not be changed by any user in the url. But it doesn't seem to accept this. This could be a risk. Any idea?Namnami

Hi Namnami, Thanks for your posting. It should also work if we set the multi-value parameter (MUser) to be "Internal". If you have create the mutli-value parameter which displays all the manager user names, then, we don't need to create a second parameter to filter the report data. Instead, we can simply add a filter to the data region or a group like below: Expression: [Field Operator: = Value: =IIF(InStr(Join(Parameters!MUser.Value, ","), User!UserID), Fields!FieldName.Value, ValueA) In this condition, there is no potential security risk caused by the parameters passed within the URL. Hope this helps. Regards, Mike YinMike Yin TechNet Community Support

That's right, thanks. But for some reason I cannot check the Muser parameter as multivalue AND internal (so that leaves me with security risk if I set it to hidden). It can be checked as internal or as multivalue. If I check both and save then only the multivalue gets saved and it is not saved as internal. (I'm using VS2005 with SSRS 2008 R2) What can be the issue?Namnami

I got it now. Changed the MUser parameter to be internal and not multivalue parameter, and changed the query behing to return concatenated rows from the table. (I have a user defined aggregate for concatenating string from rows) Thanks a lot! Namnami