| How do I use multiple parameters in BITR? [message #46236] |
Wed, 15 June 2005 10:47  |
Eclipse User |
|
|
|
Originally posted by: myggen.gmail.com
Lets say I have a company table, and I want the user to specify which
company to shown in the report. I add a report parameter, and the user can
enter a company id. Simple! But what if the user wants to enter an unknown
number of company ids? I have a query that looks something like this:
SELECT * FROM company
WHERE comp_id IN (?)
I have a report parameter called companyID. It works fine if I enter a
single comp_id. But I havent found any way to send a list of numbers. For
example, a comma separated list (100, 101, 102) gives the following error:
There are errors on the report page:
Error1:Cannot execute the statement.
Can this be done? Are there any other ways to achieve the same goal?
Thanks,
Eirik
|
|
|
| Re: How do I use multiple parameters in BITR? [message #46295 is a reply to message #46236] |
Wed, 15 June 2005 11:44  |
| Eclipse User |
|
|
|
Originally posted by: juergen.schwarz.gefasoft.de
Eirik Hansen schrieb:
> Let�s say I have a company table, and I want the user to specify which
> company to shown in the report. I add a report parameter, and the user
> can enter a company id. Simple! But what if the user wants to enter an
> unknown number of company ids? I have a query that looks something like
> this:
>
> SELECT * FROM company
> WHERE comp_id IN (?)
>
> I have a report parameter called companyID. It works fine if I enter a
> single comp_id. But I haven�t found any way to send a list of numbers.
> For example, a comma separated list (100, 101, 102) gives the following
> error:
>
> �There are errors on the report page: Error1:Cannot execute the
> statement. �
>
> Can this be done? Are there any other ways to achieve the same goal?
> Thanks,
> Eirik
Well , if the number of parameters is always the same, you can just
change your SQL statement like (for 3 parameters):
SELECT * FROM company WHERE comp_id IN (?,?,?)
If the number of parameters is variable, you have to do it another way,
which I don't know at the moment ;-)
>
|
|
|
| Re: How do I use multiple parameters in BITR? [message #46326 is a reply to message #46295] |
Wed, 15 June 2005 12:33 |
| Eclipse User |
|
|
|
Originally posted by: myggen.gmail.com
Juergen Schwarz wrote:
> If the number of parameters is variable, you have to do it another way,
> which I don't know at the moment ;-)
The numbers of parameters are variable.
I am planning to use BIRT in an application I am developing.
Unfortunately, BIRT is useless for me if am not able to have a variable
number of parameters. Most of the reports (which have been designed with
the users), rely on a variable amount of parameters.
Can it be done with the Report Engine API?
|
|
|
| Re: How do I use multiple parameters in BITR? [message #46356 is a reply to message #46326] |
Wed, 15 June 2005 13:20 |
| Eclipse User |
|
|
|
Originally posted by: juergen.schwarz.gefasoft.de
If the number of params is variable I think you have to somehow build
the dataset on your own using the script that can be attached to a
dataset or report or so.
Then you build a dynamic SQL query where you add so many question marks
as you have report parameters.
But: I just began with BIRT and don't really know a lot about, there
must be a way to do thius.
I would appreciate if anyone has an example how to connect and select
from a database using report scripting
Eirik Hansen schrieb:
> Juergen Schwarz wrote:
>
>> If the number of parameters is variable, you have to do it another way,
>> which I don't know at the moment ;-)
>
>
> The numbers of parameters are variable.
> I am planning to use BIRT in an application I am developing.
> Unfortunately, BIRT is useless for me if am not able to have a variable
> number of parameters. Most of the reports (which have been designed with
> the users), rely on a variable amount of parameters.
> Can it be done with the Report Engine API?
>
|
|
|
|
| Re: How do I use multiple parameters in BITR? [message #46615 is a reply to message #46236] |
Wed, 15 June 2005 20:37 |
| Eclipse User |
|
|
|
Originally posted by: ejo.USUNo2.pl
just pass as the parameter string a select statement:
SELECT * FROM company
WHERE comp_id IN (select comp_id from company where.....)
or
you can build the string dynamically:
long[] ids = ..............
String clause = "";
for(int i = 0; i < ids.lenght; i++) {
if(i > 0) {
clause += ",";
}
clause += ids[i];
}
SELECT * FROM company
WHERE comp_id IN (clause)
clause will be the report parameter
SELECT * FROM company
WHERE comp_id IN (?)
"Eirik Hansen" <myggen@gmail.com> wrote in message
news:ad04187074c6af9e4e5df065daed5157$1@www.eclipse.org...
> Let
|
|
|
| Re: How do I use multiple parameters in BITR? [message #47137 is a reply to message #46615] |
Thu, 16 June 2005 13:51 |
| Eclipse User |
|
|
|
Originally posted by: myggen.gmail.com
ejo wrote:
> you can build the string dynamically:
Where do you put that code?
I have tried to add a string to my report parameter. But naturally, I get
the following error:
A JDBC Exception occured: Syntax error converting the varchar value '1,
102' to a column of data type int.
I have also tried to change the datatype to any, but I only get one row in
my query result, the record corresponding to the last ID in the list.
|
|
|
|
| Re: How do I use multiple parameters in BITR? [message #47428 is a reply to message #46615] |
Fri, 17 June 2005 05:23 |
| Eclipse User |
|
|
|
Originally posted by: ejo.USUNo2.pl
this will not work...
I wrote it at hok without checking
sorry, my mistake... :)
the only solution I found (and checked) is using temporary table
as I understood you want to call report creation from code, with dynamic
parameters count
so, the report query would be:
SELECT * FROM company
WHERE comp_id IN (select comp_id from company where comp_id in (select
choosen_comp_id from report_temp))
before running report you must fill the rows in temp table
the temp table could be used in many cases, you just have to add a "key"
column
and pass the key as report parameter:
SELECT * FROM company
WHERE comp_id IN (select comp_id from company where comp_id in (select
choosen_id from report_temp where key = 'comp_id'))
I don't like such kind of solutions... work arounds... but I don't know any
others in this case...
is somebody knows a better solution please let us know...
"ejo" <ejo@USUNo2.pl> wrote in message news:d8q3in$r1i$1@news.eclipse.org...
> just pass as the parameter string a select statement:
>
> SELECT * FROM company
> WHERE comp_id IN (select comp_id from company where.....)
>
> or
>
> you can build the string dynamically:
> long[] ids = ..............
> String clause = "";
> for(int i = 0; i < ids.lenght; i++) {
> if(i > 0) {
> clause += ",";
> }
> clause += ids[i];
> }
>
> SELECT * FROM company
> WHERE comp_id IN (clause)
>
> clause will be the report parameter
>
> SELECT * FROM company
> WHERE comp_id IN (?)
>
> "Eirik Hansen" <myggen@gmail.com> wrote in message
> news:ad04187074c6af9e4e5df065daed5157$1@www.eclipse.org...
> > Let
|
|
|
|
| Re: How do I use multiple parameters in BITR? [message #50530 is a reply to message #47397] |
Thu, 23 June 2005 16:08 |
| Eclipse User |
|
|
|
Originally posted by: dsdas.yahoo.com
How I pass the variable 'queryText' to dataSet??
"Gary Xue" <gxue@actuate.com> escreveu na mensagem
news:d8smn5$qhu$1@news.eclipse.org...
> Eirik,
> You can't pass a list of values to this query using SQL parameters:
> SELECT * FROM compnay WHERE comp_id IN (?)
> This seems to be a limitation of SQL / JDBC rather than BIRT. In the 2 DB
> platforms that I tested (SQL Server, Oracle), JDBC driver identifies the
> single parameter as Integer type. This means that you can only pass a
> single
> integer value to this SQL query. It does not accept an array. (If someone
> on
> this newsgroup knows for sure whether you can pass a Java array as a SQL
> input parameter please share it with me - greatly appreciated).
>
> However you can use BIRT Scripting to compose a dynamic SQL to achieve
> what
> you want to do. Use the BIRT code editor to edit the "preOpen" method of
> you
> data set, and type in something like this:
>
> var valueList = " (100, 101, 102) "; // This list can be
> created
> dynamically based on user input
>
> // The next line alters the SQL query of the data set at runtime
> queryText = "SELECT * FROM company WHERE comp_id IN " + valueList;
>
> Try this out and let me know if this works for you.
>
> Gary Xue
> BIRT Committer
>
>
>
> "Eirik Hansen" <myggen@gmail.com> wrote in message
> news:ad04187074c6af9e4e5df065daed5157$1@www.eclipse.org...
>> Let's say I have a company table, and I want the user to specify which
>> company to shown in the report. I add a report parameter, and the user
>> can
>> enter a company id. Simple! But what if the user wants to enter an
>> unknown
>> number of company ids? I have a query that looks something like this:
>>
>> SELECT * FROM company
>> WHERE comp_id IN (?)
>>
>> I have a report parameter called companyID. It works fine if I enter a
>> single comp_id. But I haven't found any way to send a list of numbers.
>> For
>> example, a comma separated list (100, 101, 102) gives the following
>> error:
>>
>> "There are errors on the report page:
>> Error1:Cannot execute the statement. "
>>
>> Can this be done? Are there any other ways to achieve the same goal?
>>
>> Thanks,
>> Eirik
>>
>>
>>
>
>
|
|
|
| Re: How do I use multiple parameters in BITR? [message #50827 is a reply to message #50530] |
Thu, 23 June 2005 20:03 |
 Gary Xue
Messages: 193
Registered: July 2009 |
Senior Member |
|
|
queryText is not a variable. It's a property of the runtime DataSet
Javascript object. The "preOpen" script of a data set is executed using the
DataSet object as its scope (i.e., preOpen is executed as if it's a method
of the DataSet object), therefore within this scope you can directly use
"queryText" to refer to the property. "this.queryText" also works and has a
clearer semantic.
Gary
"Daniel" <dsdas@yahoo.com> wrote in message
news:d9emld$sjd$1@news.eclipse.org...
> How I pass the variable 'queryText' to dataSet??
>
> "Gary Xue" <gxue@actuate.com> escreveu na mensagem
> news:d8smn5$qhu$1@news.eclipse.org...
> > Eirik,
> > You can't pass a list of values to this query using SQL parameters:
> > SELECT * FROM compnay WHERE comp_id IN (?)
> > This seems to be a limitation of SQL / JDBC rather than BIRT. In the 2
DB
> > platforms that I tested (SQL Server, Oracle), JDBC driver identifies the
> > single parameter as Integer type. This means that you can only pass a
> > single
> > integer value to this SQL query. It does not accept an array. (If
someone
> > on
> > this newsgroup knows for sure whether you can pass a Java array as a SQL
> > input parameter please share it with me - greatly appreciated).
> >
> > However you can use BIRT Scripting to compose a dynamic SQL to achieve
> > what
> > you want to do. Use the BIRT code editor to edit the "preOpen" method of
> > you
> > data set, and type in something like this:
> >
> > var valueList = " (100, 101, 102) "; // This list can be
> > created
> > dynamically based on user input
> >
> > // The next line alters the SQL query of the data set at runtime
> > queryText = "SELECT * FROM company WHERE comp_id IN " + valueList;
> >
> > Try this out and let me know if this works for you.
> >
> > Gary Xue
> > BIRT Committer
> >
> >
> >
> > "Eirik Hansen" <myggen@gmail.com> wrote in message
> > news:ad04187074c6af9e4e5df065daed5157$1@www.eclipse.org...
> >> Let's say I have a company table, and I want the user to specify which
> >> company to shown in the report. I add a report parameter, and the user
> >> can
> >> enter a company id. Simple! But what if the user wants to enter an
> >> unknown
> >> number of company ids? I have a query that looks something like this:
> >>
> >> SELECT * FROM company
> >> WHERE comp_id IN (?)
> >>
> >> I have a report parameter called companyID. It works fine if I enter a
> >> single comp_id. But I haven't found any way to send a list of numbers.
> >> For
> >> example, a comma separated list (100, 101, 102) gives the following
> >> error:
> >>
> >> "There are errors on the report page:
> >> Error1:Cannot execute the statement. "
> >>
> >> Can this be done? Are there any other ways to achieve the same goal?
> >>
> >> Thanks,
> >> Eirik
> >>
> >>
> >>
> >
> >
>
>
|
|
|
Powered by
FUDForum. Page generated in 0.04258 seconds
] 
Search
Help
Register
Login
Home
