MS SQL Server Reporting Services - UserID doesnt really work

Author

UserID doesnt really work

Marvin

2005-08-26, 8:24 pm

I use the User.UserID to populate a parameter. When calling the report the
first time a list of parameters are prompted, at this time the field that
uses user.userID is disabled. However after filling the parameters and
submitting the request the field that uses user.useriD is now enabled for a
second request. Did i configure something wrong?

EsWallace

2005-08-29, 8:24 pm

Here is What I did

Reporting Services has the ability to pull in the Current User ID of the
person that is viewing the Report. You can create a hidden parameter and use
that parameter in the Dataset.
After you have built your Report
 Create your user Parameter. Name it USER.
 Keep the Prompt Blank. By doing this it will prevent the Parameter from
viewing in the parameter box.
 Leave the Available values as Non-queried and the values blank.
 Make the Default value as Non-queried
 Input the Default formula as: [= REPLACE(User!UserID,
"[domain]\","")]
 Create you Dataset
 Here is an Example Query used in a Dataset

SELECT CASE WHEN EmpPreferredFirstNam
e =''
THEN RTRIM(EmpFirstName)
ELSE RTRIM(EmpPreferredFi
rstName)
END + ' ' + EMPLASTNAME AS NAMES
FROM [Server].[Database].DBO.EMPLOYEE
WHERE NETWORKUSERID = @USER

"Marvin" wrote:

> I use the User.UserID to populate a parameter. When calling the report the
> first time a list of parameters are prompted, at this time the field that
> uses user.userID is disabled. However after filling the parameters and
> submitting the request the field that uses user.useriD is now enabled for a
> second request. Did i configure something wrong?

Marvin

2005-08-29, 8:24 pm

thank you, i'll try that. But looks like its going to work. But one question.
If i have a field that by default gets the data from user.userID, should a
user still be able to enter their own value. Shouldn't it always get its
value from that global variable no matter what a user inputs?

"EsWallace" wrote:
[color=darkred]
> Here is What I did
>
> Reporting Services has the ability to pull in the Current User ID of the
> person that is viewing the Report. You can create a hidden parameter and use
> that parameter in the Dataset.
> After you have built your Report
>  Create your user Parameter. Name it USER.
>  Keep the Prompt Blank. By doing this it will prevent the Parameter from
> viewing in the parameter box.
>  Leave the Available values as Non-queried and the values blank.
>  Make the Default value as Non-queried
>  Input the Default formula as: [= REPLACE(User!UserID,
"[domain]\","")]
>  Create you Dataset
>  Here is an Example Query used in a Dataset
>
> SELECT CASE WHEN EmpPreferredFirstNam
e =''
> THEN RTRIM(EmpFirstName)
> ELSE RTRIM(EmpPreferredFi
rstName)
> END + ' ' + EMPLASTNAME AS NAMES
> FROM [Server].[Database].DBO.EMPLOYEE
> WHERE NETWORKUSERID = @USER
>
>
> "Marvin" wrote:
>

Bruce L-C [MVP]

2005-08-29, 8:24 pm

I question whether you need to use a parameter. The query parameter can be
mapped directly to the userid without a parameter. For your dataset click on
the ..., go to the query parameter. On the right side select expression. In
the expression you can use the expression builder or you can just use same
formula suggested for the default:
= REPLACE(User!UserID,
"[domain]\","")

The problem with the parameter method is someone could create the URL by
hand and substitute in a different user. Being hidden they won't see it in
Report Manager but you can still fill it in via the URL.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Marvin" <Marvin@discussions.microsoft.com> wrote in message
news:9BC3ED30-2EFF-4C18-AD39- 748081D34DC2@microso
ft.com...[color=darkred]
> thank you, i'll try that. But looks like its going to work. But one
> question.
> If i have a field that by default gets the data from user.userID, should a
> user still be able to enter their own value. Shouldn't it always get its
> value from that global variable no matter what a user inputs?
>
> "EsWallace" wrote:
>

Marvin

2005-08-30, 8:25 pm

thank you for that. a lot better solution

"Bruce L-C [MVP]" wrote:

> I question whether you need to use a parameter. The query parameter can be
> mapped directly to the userid without a parameter. For your dataset click on
> the ..., go to the query parameter. On the right side select expression. In
> the expression you can use the expression builder or you can just use same
> formula suggested for the default:
> = REPLACE(User!UserID,
"[domain]\","")
>
> The problem with the parameter method is someone could create the URL by
> hand and substitute in a different user. Being hidden they won't see it in
> Report Manager but you can still fill it in via the URL.
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Marvin" <Marvin@discussions.microsoft.com> wrote in message
> news:9BC3ED30-2EFF-4C18-AD39- 748081D34DC2@microso
ft.com...
>
>
>

Sponsored Links