Can I Restrict Values in a Parameter Dropdown based on the User?

I've got a puzzler here. I have a report with 4 different dropdown lists for
parameters. One of them is Department Id. For some users, I want them to be
able to only run the report against their own department id but still be able
to select any of the values for the other parameters. Other users will be
able to select any value for department id and the other parameters.
I can figure out how to write the query to populate the Department ID
dropdown but cannot figure how to access the current userid programatically.
Is there a way to access the user ID in order to restrict the values in a
parameter dropdown?
I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
Thanks,
LizThis is really quite easy. There is a global parameter called User!UserID it
returns the domain and user ID of the user running the report. To see the
format of this create a report with a single textbox (no datasets). Set the
text box to an expression and set it to this (case sensitive):
=User!UserID
You might want to strip the domain off.
OK, the second part of this is that you need to realize that query parameter
and report parameter are two different things. You do not have to have a
query parameter mapped to a report parameter. It can be mapped to an
expression instead.
So, let's say you have this query
Select department from mydepartmentlist where userID = @.UserID
RS will create a report parameter for this called UserID. You will not use
it. You will delete it. Instead in the dataset tab click on the ... go to
the parameters tab, click on the dropdown box where the query parameter is
being mapped to report parameter and pick expression instead. Then set it to
the expression above. If you don't want the domain then use something like
this:
=mid(User!UserID, LengthToCutOffTheFront)
Of course for all of this to work you need a table somewhere that maps the
userid to the departments.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> I've got a puzzler here. I have a report with 4 different dropdown lists
> for
> parameters. One of them is Department Id. For some users, I want them to
> be
> able to only run the report against their own department id but still be
> able
> to select any of the values for the other parameters. Other users will be
> able to select any value for department id and the other parameters.
> I can figure out how to write the query to populate the Department ID
> dropdown but cannot figure how to access the current userid
> programatically.
> Is there a way to access the user ID in order to restrict the values in a
> parameter dropdown?
> I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> Thanks,
> Liz
>|||Thanks! That's very helpful and I've already succeeded at stripping off the
domain name.
I know you can pass parameter values through the URL and query string to the
report. If I wanted to pass the department ID from the calling application
to populate the dropdown, is there a way to pass that one parameter value and
let the user select the other three?
Thanks for your help,
Liz
"Bruce L-C [MVP]" wrote:
> This is really quite easy. There is a global parameter called User!UserID it
> returns the domain and user ID of the user running the report. To see the
> format of this create a report with a single textbox (no datasets). Set the
> text box to an expression and set it to this (case sensitive):
> =User!UserID
> You might want to strip the domain off.
> OK, the second part of this is that you need to realize that query parameter
> and report parameter are two different things. You do not have to have a
> query parameter mapped to a report parameter. It can be mapped to an
> expression instead.
> So, let's say you have this query
> Select department from mydepartmentlist where userID = @.UserID
> RS will create a report parameter for this called UserID. You will not use
> it. You will delete it. Instead in the dataset tab click on the ... go to
> the parameters tab, click on the dropdown box where the query parameter is
> being mapped to report parameter and pick expression instead. Then set it to
> the expression above. If you don't want the domain then use something like
> this:
> =mid(User!UserID, LengthToCutOffTheFront)
> Of course for all of this to work you need a table somewhere that maps the
> userid to the departments.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Liz" <Liz@.discussions.microsoft.com> wrote in message
> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> > I've got a puzzler here. I have a report with 4 different dropdown lists
> > for
> > parameters. One of them is Department Id. For some users, I want them to
> > be
> > able to only run the report against their own department id but still be
> > able
> > to select any of the values for the other parameters. Other users will be
> > able to select any value for department id and the other parameters.
> >
> > I can figure out how to write the query to populate the Department ID
> > dropdown but cannot figure how to access the current userid
> > programatically.
> > Is there a way to access the user ID in order to restrict the values in a
> > parameter dropdown?
> >
> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> >
> > Thanks,
> > Liz
> >
> >
>
>|||Sure. You have have a hidden parameter that gets filled in by the calling
application. The user only selects from the list you provide. Look at hidden
parameters in the books online.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:D0F372BF-DA85-4BD6-A01C-ED2708F14BAC@.microsoft.com...
> Thanks! That's very helpful and I've already succeeded at stripping off
> the
> domain name.
> I know you can pass parameter values through the URL and query string to
> the
> report. If I wanted to pass the department ID from the calling
> application
> to populate the dropdown, is there a way to pass that one parameter value
> and
> let the user select the other three?
> Thanks for your help,
> Liz
>
> "Bruce L-C [MVP]" wrote:
>> This is really quite easy. There is a global parameter called User!UserID
>> it
>> returns the domain and user ID of the user running the report. To see the
>> format of this create a report with a single textbox (no datasets). Set
>> the
>> text box to an expression and set it to this (case sensitive):
>> =User!UserID
>> You might want to strip the domain off.
>> OK, the second part of this is that you need to realize that query
>> parameter
>> and report parameter are two different things. You do not have to have a
>> query parameter mapped to a report parameter. It can be mapped to an
>> expression instead.
>> So, let's say you have this query
>> Select department from mydepartmentlist where userID = @.UserID
>> RS will create a report parameter for this called UserID. You will not
>> use
>> it. You will delete it. Instead in the dataset tab click on the ... go to
>> the parameters tab, click on the dropdown box where the query parameter
>> is
>> being mapped to report parameter and pick expression instead. Then set it
>> to
>> the expression above. If you don't want the domain then use something
>> like
>> this:
>> =mid(User!UserID, LengthToCutOffTheFront)
>> Of course for all of this to work you need a table somewhere that maps
>> the
>> userid to the departments.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Liz" <Liz@.discussions.microsoft.com> wrote in message
>> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
>> > I've got a puzzler here. I have a report with 4 different dropdown
>> > lists
>> > for
>> > parameters. One of them is Department Id. For some users, I want them
>> > to
>> > be
>> > able to only run the report against their own department id but still
>> > be
>> > able
>> > to select any of the values for the other parameters. Other users will
>> > be
>> > able to select any value for department id and the other parameters.
>> >
>> > I can figure out how to write the query to populate the Department ID
>> > dropdown but cannot figure how to access the current userid
>> > programatically.
>> > Is there a way to access the user ID in order to restrict the values in
>> > a
>> > parameter dropdown?
>> >
>> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
>> >
>> > Thanks,
>> > Liz
>> >
>> >
>>