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
>
>

My own report parameters

Hi there,
Can I add my own report parameters on report parameter area of a sql server
report 2005.
I have a label, textbox followed by a button. Open a aspx search page
onclicking the button.
Thanks,
RaviI not sure I really understand your question..
However one of the things you can do , create and upload an aspx page to
Reporting Services, which you have programmed to collect any parameter
information you wish. It could also contain a button, that calls your report
URL, passing in the parameter values you have collected.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ravi Manda" wrote:
> Hi there,
> Can I add my own report parameters on report parameter area of a sql server
> report 2005.
> I have a label, textbox followed by a button. Open a aspx search page
> onclicking the button.
> Thanks,
> Ravi|||My requirement is something like this, I want to have a label, textbox and
button, on clicking the button I will open a page and the page returns a some
value which should be populted in the textbox. But we don't have control on
report parameter area of a report.
As per my understadning from your reply is I need to have a aspx page with
my own parameters and a reportviewer control pass these parameters with the
URL to the report. (I'm ok with this answer).
Please confirm me that we cann't add our own controls on the report
parameter area.
(We don't want to have a dropdownlist incase the items cross's more then
100. In this case we add a text box and a button on the report parameter
area. Onclicking the button I will open a search page in a popup window).
Search page contains all search fields and a grid. Selecting on a particular
row on the grid I need to pass the value to the textbox of the report).
Thanks
"Wayne Snyder" wrote:
> I not sure I really understand your question..
> However one of the things you can do , create and upload an aspx page to
> Reporting Services, which you have programmed to collect any parameter
> information you wish. It could also contain a button, that calls your report
> URL, passing in the parameter values you have collected.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Ravi Manda" wrote:
> > Hi there,
> >
> > Can I add my own report parameters on report parameter area of a sql server
> > report 2005.
> >
> > I have a label, textbox followed by a button. Open a aspx search page
> > onclicking the button.
> >
> > Thanks,
> > Ravi

My Own - Multi Select Parameter Passing Implementation

Hi,
I would like to share how I implemented in my application by passing multi
select parameters into the report.
1. In my .net file, I have created a list box which I load all the multi
values that the user wants to report on(for Ex: Equipment Id = 100,200,300,400 and so on).
2. When the user submits the page, I load the above list into a table.
3. In my stored procedure, I go after this table and build the SELECT
statement.
4. Since the report has to refreshed everytime they add another value into
the list, the report has to know that it has to refresh. For this what I have
done is on the table that i load all the values, I extract the SEQUENCE # and
pass it to the report(it is a unique # everytime when you refresh the page).
U can also pass a Random # but the uniqueness is not guaranteed and the
report may not refresh.
In the Report Designer:
1. I create a parameter called RefreshReport. This value that gets passed
into the parameter is the SEQUENCE value.
I poured my head into 3 books for some clue and couldn't find one hint.
I will be more than happy to answer any questions regarding this
implementation..hi, can you be more detailing? I've been following a guide from a book,
there's this query:
="SELECT * FROM vProductProfitability WHERE (Year = @.Year) AND (Mon
thNumberOfYear = @.Month) AND (Category IN (â'+Parameters!CategoryStrin
g.Value+â'))â'
yes, like what you said, you created a new parameter, in here, the the param
is category string, I dn't know where to put this query since i'm using a
stored procedure.. the only way we can use this query is by using text, so
about your stored procedure that you changed.. what is it?
what i can still select is 1 item, although i'm using checkboxlist, i can
select all of them, but w/ an error of course.
can you please discuss how you manage to enable multi select, since you've
posted a helping memo in here already.. advance thanks!
--
~SiMPLe~
"Suresh" wrote:
> Hi,
> I would like to share how I implemented in my application by passing multi
> select parameters into the report.
> 1. In my .net file, I have created a list box which I load all the multi
> values that the user wants to report on(for Ex: Equipment Id => 100,200,300,400 and so on).
> 2. When the user submits the page, I load the above list into a table.
> 3. In my stored procedure, I go after this table and build the SELECT
> statement.
> 4. Since the report has to refreshed everytime they add another value into
> the list, the report has to know that it has to refresh. For this what I have
> done is on the table that i load all the values, I extract the SEQUENCE # and
> pass it to the report(it is a unique # everytime when you refresh the page).
> U can also pass a Random # but the uniqueness is not guaranteed and the
> report may not refresh.
> In the Report Designer:
> 1. I create a parameter called RefreshReport. This value that gets passed
> into the parameter is the SEQUENCE value.
> I poured my head into 3 books for some clue and couldn't find one hint.
> I will be more than happy to answer any questions regarding this
> implementation..