CODEWITHSUNDEEP
t-sqlreporting-services
BattlFrog
BattlFrog

Reputation: 3397

How to put an SSRS built in variable into SQL variable

Building a report in SSRS 2008. I need to capture the built-in User!UserId variable so I can use it in my dataset query. It is part of my report security. I have tried a couple ways, but niether worked:

DECLARE @currentUser varchar(30) =User!UserID

and inline:

SELECT @retVal = COUNT(*)
FROM MySecurityTable
WHERE ExpirationTime > GETDATE() AND UserName =User!UserId

Upvotes: 1

Views: 5287

Answers (1)

Anup Agrawal
Anup Agrawal

Reputation: 6669

Create a Hidden report parameter @UserID and set its default value as =User!UserID.

Use the parameter @UserID in your dataset. 

SELECT @retVal = COUNT(*)
FROM MySecurityTable
WHERE ExpirationTime > GETDATE() AND UserName = @UserID

Upvotes: 9

Related Questions