|
|
You're in the
sql server reporting services
group:Problem with user prompt
sql server reporting services:
I want to prompt the user for a specific ID number or use a * for all
records at the time of running the report So in my criteria for the employee ID field I put "= @empid" In the report the input box is there. If I put in an ID number it works great. However if I put in an asterisk "*" nothing shows up. Any ideas?
Ok you lost me a little bit.
Here is my current where clause. WHERE (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT (pshstj.trn = 'RO1' OR pshstj.trn = 'WO1' OR pshstj.trn = 'XXX')) AND (pshstj.empid = @ClockNo) The "@clockno" section is where I prompt them for a clock number. If I put a * it doesn't work. If I put a good number in it works fine. Where does your 'where' clause fit into this? [quoted text, click to view] Andrei wrote:
> Your where clause should be written like this: > > where > (isnull(@EmpID, '*') = '*') > or ((isnull(@EmpID, 0) <> 0) and (EmpID = @EmpID)) > > this lets your users enter a value for EmpID (then the second part of the > where clause will deal with it) > or leave the parameter field empty - then all their records will be selected > > You could also replace the '*' with a '' (empty string) - this way, if the > users leave the parameter field blank, it will behave like a '*'. > > Andrei. > > > "Bruce Lawrence" <BL32375@gmail.com> wrote in message > news:1164824835.673840.167860@80g2000cwy.googlegroups.com... > >I want to prompt the user for a specific ID number or use a * for all > > records at the time of running the report > > > > So in my criteria for the employee ID field I put "= @empid" > > > > In the report the input box is there. If I put in an ID number it > > works great. However if I put in an asterisk "*" nothing shows up. > > > > Any ideas? > >
I'd write an IIF statement (either in a stored procedure, or in the
Report Query): IF @clockno = '*' BEGIN select .... from ... where (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT (pshstj.trn = 'RO1' OR pshstj.trn = 'WO1' OR pshstj.trn = 'XXX')) END ELSE BEGIN select .. from .. where (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT (pshstj.trn = 'RO1' OR pshstj.trn = 'WO1' OR pshstj.trn = 'XXX')) AND (pshstj.empid = @ClockNo) END [quoted text, click to view] Bruce Lawrence wrote:
> Ok you lost me a little bit. > > Here is my current where clause. > > WHERE (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT > (pshstj.trn = 'RO1' OR > pshstj.trn = 'WO1' OR > pshstj.trn = 'XXX')) AND (pshstj.empid = > @ClockNo) > > The "@clockno" section is where I prompt them for a clock number. > If I put a * it doesn't work. If I put a good number in it works fine. > > Where does your 'where' clause fit into this? > > > Andrei wrote: > > Your where clause should be written like this: > > > > where > > (isnull(@EmpID, '*') = '*') > > or ((isnull(@EmpID, 0) <> 0) and (EmpID = @EmpID)) > > > > this lets your users enter a value for EmpID (then the second part of the > > where clause will deal with it) > > or leave the parameter field empty - then all their records will be selected > > > > You could also replace the '*' with a '' (empty string) - this way, if the > > users leave the parameter field blank, it will behave like a '*'. > > > > Andrei. > > > > > > "Bruce Lawrence" <BL32375@gmail.com> wrote in message > > news:1164824835.673840.167860@80g2000cwy.googlegroups.com... > > >I want to prompt the user for a specific ID number or use a * for all > > > records at the time of running the report > > > > > > So in my criteria for the employee ID field I put "= @empid" > > > > > > In the report the input box is there. If I put in an ID number it > > > works great. However if I put in an asterisk "*" nothing shows up. > > > > > > Any ideas? > > >
Your where clause should be written like this:
where (isnull(@EmpID, '*') = '*') or ((isnull(@EmpID, 0) <> 0) and (EmpID = @EmpID)) this lets your users enter a value for EmpID (then the second part of the where clause will deal with it) or leave the parameter field empty - then all their records will be selected You could also replace the '*' with a '' (empty string) - this way, if the users leave the parameter field blank, it will behave like a '*'. Andrei. [quoted text, click to view] "Bruce Lawrence" <BL32375@gmail.com> wrote in message news:1164824835.673840.167860@80g2000cwy.googlegroups.com... >I want to prompt the user for a specific ID number or use a * for all > records at the time of running the report > > So in my criteria for the employee ID field I put "= @empid" > > In the report the input box is there. If I put in an ID number it > works great. However if I put in an asterisk "*" nothing shows up. > > Any ideas? >
WHERE (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT (pshstj.trn = 'RO1' OR pshstj.trn = 'WO1' OR pshstj.trn = 'XXX')) AND ( (isnull(@ClockNo, '*') = '*') or ( (isnull(@ClockNo, '*') <> '*') and (pshstj.empid = lockNo) ) ) [quoted text, click to view] "Bruce Lawrence" <BL32375@gmail.com> wrote in message news:1164828482.158109.84050@j44g2000cwa.googlegroups.com... > Ok you lost me a little bit. > > Here is my current where clause. > > WHERE (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT > (pshstj.trn = 'RO1' OR > pshstj.trn = 'WO1' OR > pshstj.trn = 'XXX')) AND (pshstj.empid = > @ClockNo) > > The "@clockno" section is where I prompt them for a clock number. > If I put a * it doesn't work. If I put a good number in it works fine. > > Where does your 'where' clause fit into this? > > > Andrei wrote: >> Your where clause should be written like this: >> >> where >> (isnull(@EmpID, '*') = '*') >> or ((isnull(@EmpID, 0) <> 0) and (EmpID = @EmpID)) >> >> this lets your users enter a value for EmpID (then the second part of the >> where clause will deal with it) >> or leave the parameter field empty - then all their records will be >> selected >> >> You could also replace the '*' with a '' (empty string) - this way, if >> the >> users leave the parameter field blank, it will behave like a '*'. >> >> Andrei. >> >> >> "Bruce Lawrence" <BL32375@gmail.com> wrote in message >> news:1164824835.673840.167860@80g2000cwy.googlegroups.com... >> >I want to prompt the user for a specific ID number or use a * for all >> > records at the time of running the report >> > >> > So in my criteria for the employee ID field I put "= @empid" >> > >> > In the report the input box is there. If I put in an ID number it >> > works great. However if I put in an asterisk "*" nothing shows up. >> > >> > Any ideas? >> > >
Andrei,
I'm not sure how... but it works. I love you
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 |