|
|
You're in the
sql server reporting services
group:UserID doesnt really work
sql server reporting services:
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
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 EmpPreferredFirstName ='' THEN RTRIM(EmpFirstName) ELSE RTRIM(EmpPreferredFirstName) END + ' ' + EMPLASTNAME AS NAMES FROM [Server].[Database].DBO.EMPLOYEE WHERE NETWORKUSERID = @USER [quoted text, click to view] "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
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? [quoted text, click to view] "EsWallace" wrote:
> 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 EmpPreferredFirstName ='' > THEN RTRIM(EmpFirstName) > ELSE RTRIM(EmpPreferredFirstName) > 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
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 [quoted text, click to view] "Marvin" <Marvin@discussions.microsoft.com> wrote in message news:9BC3ED30-2EFF-4C18-AD39-748081D34DC2@microsoft.com... > 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: > >> 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 EmpPreferredFirstName ='' >> THEN RTRIM(EmpFirstName) >> ELSE RTRIM(EmpPreferredFirstName) >> 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?
thank you for that. a lot better solution
[quoted text, click to view] "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@microsoft.com... > > 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: > > > >> 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 EmpPreferredFirstName ='' > >> THEN RTRIM(EmpFirstName) > >> ELSE RTRIM(EmpPreferredFirstName) > >> 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? > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |