|
|
You're in the
sql server reporting services
group:Report Parameter Problem
sql server reporting services:
I have a report based on a stored procedure with three parameters: Start
Date, End Date, and Salesperson. The first two are required, and the Salesperson is not. In the report, the Salesperson is based on a query which provides a drop down for choosing a name. It is set to allow nulls and blanks. When I run the report in Visual Studio preview, if no Salesperson is chosen, and both dates are entered, no data is returned. Doing the same thing via debug in a browser, (the drop down is not blank, but says <Selelct a value>), I get prompted to supply the Salesperson parameter. Supplying a value for Salesperson in both cases works fine, but I want the user to have the option to get all the data, not just for a particular person. I've covered this case in the stored procedure, so I'm not sure what is getting passed in the previewer when nothing is entered and no data is returned. If I run the query (sp) via the Data tab, and provide exactly the same values for the parameters, I get the data I expect, and no prompt for the Salesperson. If I put a default value in for the Salesperson, (non-queried), e.g. "All", the preview in VS goes crazy, and locks up. I would have thought with allowing nulls and/or blanks, this should work. Am I missing something? Thanks for any help,
If Salesperson parameter uses available values from query, then the query
must return null or blank as one of available values. -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "TomT" <tomt@newsgroup.nospam> wrote in message news:87D78917-4603-4339-AD5B-C4C3377BA9AA@microsoft.com... >I have a report based on a stored procedure with three parameters: Start > Date, End Date, and Salesperson. > > The first two are required, and the Salesperson is not. In the report, the > Salesperson is based on a query which provides a drop down for choosing a > name. It is set to allow nulls and blanks. > > When I run the report in Visual Studio preview, if no Salesperson is > chosen, > and both dates are entered, no data is returned. Doing the same thing via > debug in a browser, (the drop down is not blank, but says <Selelct a > value>), > I get prompted to supply the Salesperson parameter. > > Supplying a value for Salesperson in both cases works fine, but I want the > user to have the option to get all the data, not just for a particular > person. I've covered this case in the stored procedure, so I'm not sure > what > is getting passed in the previewer when nothing is entered and no data is > returned. > > If I run the query (sp) via the Data tab, and provide exactly the same > values for the parameters, I get the data I expect, and no prompt for the > Salesperson. > > If I put a default value in for the Salesperson, (non-queried), e.g. > "All", > the preview in VS goes crazy, and locks up. > > I would have thought with allowing nulls and/or blanks, this should work. > Am > I missing something? > > Thanks for any help, > > Tom
Lev, thanks, I was assuming if no choice was made (drop down blank) a null
would be passed. I fixed it up to provide a value simulating a null situation, and it now works fine. Thanks for your quick response... Tom [quoted text, click to view] "Lev Semenets [MSFT]" wrote:
> If Salesperson parameter uses available values from query, then the query > must return null or blank as one of available values. > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > > > "TomT" <tomt@newsgroup.nospam> wrote in message > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@microsoft.com... > >I have a report based on a stored procedure with three parameters: Start > > Date, End Date, and Salesperson. > > > > The first two are required, and the Salesperson is not. In the report, the > > Salesperson is based on a query which provides a drop down for choosing a > > name. It is set to allow nulls and blanks. > > > > When I run the report in Visual Studio preview, if no Salesperson is > > chosen, > > and both dates are entered, no data is returned. Doing the same thing via > > debug in a browser, (the drop down is not blank, but says <Selelct a > > value>), > > I get prompted to supply the Salesperson parameter. > > > > Supplying a value for Salesperson in both cases works fine, but I want the > > user to have the option to get all the data, not just for a particular > > person. I've covered this case in the stored procedure, so I'm not sure > > what > > is getting passed in the previewer when nothing is entered and no data is > > returned. > > > > If I run the query (sp) via the Data tab, and provide exactly the same > > values for the parameters, I get the data I expect, and no prompt for the > > Salesperson. > > > > If I put a default value in for the Salesperson, (non-queried), e.g. > > "All", > > the preview in VS goes crazy, and locks up. > > > > I would have thought with allowing nulls and/or blanks, this should work. > > Am > > I missing something? > > > > Thanks for any help, > > > > Tom > >
TomT
Could you please tell me how did you simulate a value of null. I am running into exactly same problem as yours where I cant make paramter optional as Report Manager forces me to choose a value although I have defined null and blank as options for the parameter. TIA [quoted text, click to view] "TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null > would be passed. I fixed it up to provide a value simulating a null > situation, and it now works fine. > > Thanks for your quick response... > > Tom > > "Lev Semenets [MSFT]" wrote: > > > If Salesperson parameter uses available values from query, then the query > > must return null or blank as one of available values. > > > > -- > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > > > > "TomT" <tomt@newsgroup.nospam> wrote in message > > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@microsoft.com... > > >I have a report based on a stored procedure with three parameters: Start > > > Date, End Date, and Salesperson. > > > > > > The first two are required, and the Salesperson is not. In the report, the > > > Salesperson is based on a query which provides a drop down for choosing a > > > name. It is set to allow nulls and blanks. > > > > > > When I run the report in Visual Studio preview, if no Salesperson is > > > chosen, > > > and both dates are entered, no data is returned. Doing the same thing via > > > debug in a browser, (the drop down is not blank, but says <Selelct a > > > value>), > > > I get prompted to supply the Salesperson parameter. > > > > > > Supplying a value for Salesperson in both cases works fine, but I want the > > > user to have the option to get all the data, not just for a particular > > > person. I've covered this case in the stored procedure, so I'm not sure > > > what > > > is getting passed in the previewer when nothing is entered and no data is > > > returned. > > > > > > If I run the query (sp) via the Data tab, and provide exactly the same > > > values for the parameters, I get the data I expect, and no prompt for the > > > Salesperson. > > > > > > If I put a default value in for the Salesperson, (non-queried), e.g. > > > "All", > > > the preview in VS goes crazy, and locks up. > > > > > > I would have thought with allowing nulls and/or blanks, this should work. > > > Am > > > I missing something? > > > > > > Thanks for any help, > > > > > > Tom > > > >
TomT
Could you please tell me how did you simulate a value of null. I am running into exactly same problem as yours where I cant make paramter optional as Report Manager forces me to choose a value although I have defined null and blank as options for the parameter. TIA [quoted text, click to view] "TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null > would be passed. I fixed it up to provide a value simulating a null > situation, and it now works fine. > > Thanks for your quick response... > > Tom > > "Lev Semenets [MSFT]" wrote: > > > If Salesperson parameter uses available values from query, then the query > > must return null or blank as one of available values. > > > > -- > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > > > > "TomT" <tomt@newsgroup.nospam> wrote in message > > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@microsoft.com... > > >I have a report based on a stored procedure with three parameters: Start > > > Date, End Date, and Salesperson. > > > > > > The first two are required, and the Salesperson is not. In the report, the > > > Salesperson is based on a query which provides a drop down for choosing a > > > name. It is set to allow nulls and blanks. > > > > > > When I run the report in Visual Studio preview, if no Salesperson is > > > chosen, > > > and both dates are entered, no data is returned. Doing the same thing via > > > debug in a browser, (the drop down is not blank, but says <Selelct a > > > value>), > > > I get prompted to supply the Salesperson parameter. > > > > > > Supplying a value for Salesperson in both cases works fine, but I want the > > > user to have the option to get all the data, not just for a particular > > > person. I've covered this case in the stored procedure, so I'm not sure > > > what > > > is getting passed in the previewer when nothing is entered and no data is > > > returned. > > > > > > If I run the query (sp) via the Data tab, and provide exactly the same > > > values for the parameters, I get the data I expect, and no prompt for the > > > Salesperson. > > > > > > If I put a default value in for the Salesperson, (non-queried), e.g. > > > "All", > > > the preview in VS goes crazy, and locks up. > > > > > > I would have thought with allowing nulls and/or blanks, this should work. > > > Am > > > I missing something? > > > > > > Thanks for any help, > > > > > > Tom > > > >
TomT Could you please tell me how did you simulate a value of null. I am running into exactly same problem as yours where I cant make paramter optional as Report Manager forces me to choose a value although I have defined null and blank as options for the parameter. TIA [quoted text, click to view] "TomT" wrote:
> I have a report based on a stored procedure with three parameters: Start > Date, End Date, and Salesperson. > > The first two are required, and the Salesperson is not. In the report, the > Salesperson is based on a query which provides a drop down for choosing a > name. It is set to allow nulls and blanks. > > When I run the report in Visual Studio preview, if no Salesperson is chosen, > and both dates are entered, no data is returned. Doing the same thing via > debug in a browser, (the drop down is not blank, but says <Selelct a value>), > I get prompted to supply the Salesperson parameter. > > Supplying a value for Salesperson in both cases works fine, but I want the > user to have the option to get all the data, not just for a particular > person. I've covered this case in the stored procedure, so I'm not sure what > is getting passed in the previewer when nothing is entered and no data is > returned. > > If I run the query (sp) via the Data tab, and provide exactly the same > values for the parameters, I get the data I expect, and no prompt for the > Salesperson. > > If I put a default value in for the Salesperson, (non-queried), e.g. "All", > the preview in VS goes crazy, and locks up. > > I would have thought with allowing nulls and/or blanks, this should work. Am > I missing something? > > Thanks for any help, >
Did you set default value for the parameter?
-- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "tiwanap" <tiwanap@discussions.microsoft.com> wrote in message news:7C84F377-9ECA-4A87-AC8F-E936353B917A@microsoft.com... > > TomT > Could you please tell me how did you simulate a value of null. I am > running > into exactly same problem as yours where I cant make paramter optional as > Report Manager forces me to choose a value although I have defined null > and > blank as options for the parameter. > > TIA > > "TomT" wrote: > >> I have a report based on a stored procedure with three parameters: Start >> Date, End Date, and Salesperson. >> >> The first two are required, and the Salesperson is not. In the report, >> the >> Salesperson is based on a query which provides a drop down for choosing a >> name. It is set to allow nulls and blanks. >> >> When I run the report in Visual Studio preview, if no Salesperson is >> chosen, >> and both dates are entered, no data is returned. Doing the same thing via >> debug in a browser, (the drop down is not blank, but says <Selelct a >> value>), >> I get prompted to supply the Salesperson parameter. >> >> Supplying a value for Salesperson in both cases works fine, but I want >> the >> user to have the option to get all the data, not just for a particular >> person. I've covered this case in the stored procedure, so I'm not sure >> what >> is getting passed in the previewer when nothing is entered and no data is >> returned. >> >> If I run the query (sp) via the Data tab, and provide exactly the same >> values for the parameters, I get the data I expect, and no prompt for the >> Salesperson. >> >> If I put a default value in for the Salesperson, (non-queried), e.g. >> "All", >> the preview in VS goes crazy, and locks up. >> >> I would have thought with allowing nulls and/or blanks, this should work. >> Am >> I missing something? >> >> Thanks for any help, >> >> Tom
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 |