Named and UnNamed parameters

While creating a report i am running into a problem with parameters.
When building the query i would like to use the same parameter more then
once.
for instance :
select *
from table
where field = @.parameter1
and field = @.Parameter1
and field = @.parameter1
I can runs this Query successfully in the Data tab of the report .however
when i go to the layout or preview tab i get the following error:
The data extension ODBC does not support named parameters. use unnamed
parameters instead.
If i run the query using ? as the parameter i dont have any problems.However
for each ? parameter the user is prompted to supply a value.This is
unacceptable as the user would have to enter the same value numerous times.
select *
from table
where field = ?
and field = ?
and field = ?
Any help would be appreciated.
TIA,
VinnyThere are query parameters and report parameters. RS automatically creates
the report parameters for you. It will create a report parameter for each
unnammed parameter. What you need to do is map each query parameter to a
single report parameter. In the data tab click on the ..., parameters tab.
Map each ? on the left to the same report parameter on the right. Next go to
the layout, report->report parameters and remove the extra unused report
parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vinny Vinn" <VinnyVinn@.discussions.microsoft.com> wrote in message
news:2F9A3C8B-72DA-4CAD-BE4B-5D4D07CB1C4F@.microsoft.com...
> While creating a report i am running into a problem with parameters.
> When building the query i would like to use the same parameter more then
> once.
> for instance :
> select *
> from table
> where field = @.parameter1
> and field = @.Parameter1
> and field = @.parameter1
> I can runs this Query successfully in the Data tab of the report .however
> when i go to the layout or preview tab i get the following error:
> The data extension ODBC does not support named parameters. use unnamed
> parameters instead.
> If i run the query using ? as the parameter i dont have any
> problems.However
> for each ? parameter the user is prompted to supply a value.This is
> unacceptable as the user would have to enter the same value numerous
> times.
> select *
> from table
> where field = ?
> and field = ?
> and field = ?
> Any help would be appreciated.
> TIA,
> Vinny|||Thank you ,that what i needed.
"Bruce L-C [MVP]" wrote:
> There are query parameters and report parameters. RS automatically creates
> the report parameters for you. It will create a report parameter for each
> unnammed parameter. What you need to do is map each query parameter to a
> single report parameter. In the data tab click on the ..., parameters tab.
> Map each ? on the left to the same report parameter on the right. Next go to
> the layout, report->report parameters and remove the extra unused report
> parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Vinny Vinn" <VinnyVinn@.discussions.microsoft.com> wrote in message
> news:2F9A3C8B-72DA-4CAD-BE4B-5D4D07CB1C4F@.microsoft.com...
> > While creating a report i am running into a problem with parameters.
> > When building the query i would like to use the same parameter more then
> > once.
> > for instance :
> >
> > select *
> > from table
> > where field = @.parameter1
> > and field = @.Parameter1
> > and field = @.parameter1
> >
> > I can runs this Query successfully in the Data tab of the report .however
> > when i go to the layout or preview tab i get the following error:
> > The data extension ODBC does not support named parameters. use unnamed
> > parameters instead.
> >
> > If i run the query using ? as the parameter i dont have any
> > problems.However
> > for each ? parameter the user is prompted to supply a value.This is
> > unacceptable as the user would have to enter the same value numerous
> > times.
> >
> > select *
> > from table
> > where field = ?
> > and field = ?
> > and field = ?
> >
> > Any help would be appreciated.
> >
> > TIA,
> > Vinny
>
>|||Bruce,
I am using DB2 so I also have to use unnamed parms in my queries. I have
used your solution for parms that need entries on the report. However - that
doesnt solve the problem that the query on the data tab still has 10 question
marks in it and it would be really helpful to those that have to modify
"others" reports to not have to try and play "match the parameter" ... Is
there a way to place a comment in the query tab? that might be helpful
enough. Thanks! I am posting a much larger ISSUE about dealing with parms
syntax when I need to code the sql as an expression ... please see Parm
Syntax for SQL Expression
"Bruce L-C [MVP]" wrote:
> There are query parameters and report parameters. RS automatically creates
> the report parameters for you. It will create a report parameter for each
> unnammed parameter. What you need to do is map each query parameter to a
> single report parameter. In the data tab click on the ..., parameters tab.
> Map each ? on the left to the same report parameter on the right. Next go to
> the layout, report->report parameters and remove the extra unused report
> parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Vinny Vinn" <VinnyVinn@.discussions.microsoft.com> wrote in message
> news:2F9A3C8B-72DA-4CAD-BE4B-5D4D07CB1C4F@.microsoft.com...
> > While creating a report i am running into a problem with parameters.
> > When building the query i would like to use the same parameter more then
> > once.
> > for instance :
> >
> > select *
> > from table
> > where field = @.parameter1
> > and field = @.Parameter1
> > and field = @.parameter1
> >
> > I can runs this Query successfully in the Data tab of the report .however
> > when i go to the layout or preview tab i get the following error:
> > The data extension ODBC does not support named parameters. use unnamed
> > parameters instead.
> >
> > If i run the query using ? as the parameter i dont have any
> > problems.However
> > for each ? parameter the user is prompted to supply a value.This is
> > unacceptable as the user would have to enter the same value numerous
> > times.
> >
> > select *
> > from table
> > where field = ?
> > and field = ?
> > and field = ?
> >
> > Any help would be appreciated.
> >
> > TIA,
> > Vinny
>
>