Reporting Services 2008: Using user!userid in Parameters

Question

I have a report with a parameter that is populated by a list of sales representatives returned by a query. I want to filter that list based on security rights of the user running the report.

In order to make the query work I need to pass the user!userID to the db. I tried something like this:

...where UserName = user!UserID...

But it doesn't like the syntax.

Solution 1

Change your query to:

where UserName = @user

... and in the "Parameters" tab, assign "User!UserID" to the "@user" parameter.

Solution 2

Just want to share my experiance if some poor soul wandering around like me without any clue. I discovered that my @user parameter had to be first in the list in order for my dropdown to get populated which was dependant on that. I have no clue why is that.

Comments

[Jeff]

When I try that I get the following error. The report parameter 'UserName' has a DefaultValue or a ValidValue that depends on the report parameter "user". Forward dependencies are not valid.

[Matt Hamilton]

Really? I do exactly what I describe here in a couple of my own reports. Are you doing anything other than the steps described above?

[Jeff]

Finally figured it out. Moved the "user" parameter up the list so it is above my "UserName" parameter. That way it's in memory when the dataset for "userName" is being populated. Thanks for the assurance that this would work. I really appreciate the help.

[Matt Hamilton]

Oh, you had a "UserName" parameter as well? Glad I could help anyway.

[neblinc1]

Yes, I've seen this behavior too. When you add expressions to the parameter values (like =User!UserID), SSRS is treating that as cascading parameter. If it is not first in the list, then it will populate like how cascading parameter populate. Others have seen this too: blog.summitcloud.com/2009/12/fix-refresh-of-parameters-in-ss‌​rs

[Valentino Vranken]

@Jeff: as far as I can tell, you shouldn't need those two "user..." parameters. What purpose would they serve? Also keep in mind that smart users would be able to figure out how to pass someone else's username into the report, if you'd be using a report parameter. For security you should rely on using the User!UserID value.

[LittleTreeX]

I spent a good hour comparing 2 reports wondering why my user info was not being brought in appropriately. In the original report, the user parameter was NOT first, yet it worked fine. In the second report, I was banging my head against the wall until I came here and decided to move it to the top of the list... this worked like a charm. It's very frustrating when issues like this are not consistent. Thank you for posting this here!