ReportServer Forum

ralex

Member

Registered: 2015-04-20

User Variable explanation

Hi,
I never used the user variable so far, but I'm wondering if someone can easily explain their use by doing an example.
Thanks.

Alex

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: User Variable explanation

Hi Alex,

user variables allow you to have report parameters that depend on the currently logged-in user. Consider a multi-client system and a database table MY_TABLE

Att1 | Att2 | Att3 | ClientId
a    | b    | c    | C1
d    | e    | f    | C1
h    | e    | c    | C2
x    | y    | z    | C2
a    | b    | c    | C3
...

Now, consider that you want to provide a report on that table but each client should only see their own data. So what you could do is to have three (number of clients) separate reports each having a query such as

SELECT * FROM MY_TABLE WHERE ClientId = 'C1'

This is, of course, rather annoying, because why should you duplicate the report three times? Or consider you have 100 clients. With user variables you can have a single report. What you store at the user (or at an organizational unit somewhere above the user) is the information to which client a user belongs. So, for example, we have a user variable called CLIENT_ID. Now, when creating our report we add a parameter (probably a hidden one) of type user variable and select the CLIENT_ID variable. Now we can use the following query

SELECT * FROM MY_TABLE WHERE ClientId = ${CLIENT_ID}

I hope this helps.

Cheers,
Arno

ralex

Member

Registered: 2015-04-20

Re: User Variable explanation

Thanks a million!
Great explanation.
Cheers,
Alex