question

Clueless avatar image
Clueless asked

using global variable userID

Hi, I am painfully new to SSRS and have an assignment to create a report that will render based on the userID; I have the report built and the stored procedure that brings in all of the necessary data, etc. I added a text box to my report that pulls the USERID as an expression from the Global Variables.

But, what do I do with it? How will the report identify the user.. I'm assuming I need to add @UserID param to my stored procedure, but I am quite lost.. (mind not completely here this morning, if you know what I mean...)

I'm using SQL Server 2005 - the report will ultimately render in MOSS 2007..

ssrsvariable
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered

You could assign the expression User!UserID to the stored proc's parameter. That would give it a string in the format DOMAIN\username. It sounds like you have a table of users in your database so you just need to work out which user the string refers to.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

add a parameter to your sp that will be used to filter your data in the WHERE clause. something like:

WHERE tbl1.username = @UserID

then refresh the report file's data connection so it picks up the sp change. Once thats done create a hidden report parameter that has a non queried default value of user!userid.

that should be it...

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

initforfun avatar image
initforfun answered

Thanks David and Fatherjack... I ended up adding the parameter and using it in the Where clause; then assigned it group by non-query in the report params.. .Nice..

T

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.