Eclipse Community Forums: BIRT » How do I use multiple parameters in BITR?

Home » Archived » BIRT » How do I use multiple parameters in BITR?

How do I use multiple parameters in BITR? [message #46236]

Wed, 15 June 2005 06: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 07: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 08: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 09: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 #46416 is a reply to message #46236]

Wed, 15 June 2005 10:35

Eclipse User

Hi Eirik,

You can try this simple sql query to solve your problem...

SELECT * FROM company
WHERE comp_id >= ? and comp_id <= ?

Then add two report parameters to your report....e.g...100 and 102
....this will also include 101 record from you table....

Goodluck
Wins

Re: How do I use multiple parameters in BITR? [message #46615 is a reply to message #46236]

Wed, 15 June 2005 16: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 09: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 #47397 is a reply to message #46236]

Thu, 16 June 2005 16:17

Eclipse User

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

Re: How do I use multiple parameters in BITR? [message #47428 is a reply to message #46615]

Fri, 17 June 2005 01: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 #47486 is a reply to message #47397]

Fri, 17 June 2005 06:04

Eclipse User

Originally posted by: myggen.gmail.com

This solves my problem. Thanks!

Eirik

Re: How do I use multiple parameters in BITR? [message #50530 is a reply to message #47397]

Thu, 23 June 2005 12: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 16:03

Eclipse User

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

Previous Topic:	master page margins
Next Topic:	Procedures

Goto Forum:

-=] Back to Top [=-

Current Time: Wed May 07 18:08:23 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter