Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Referencing parameter by name
Referencing parameter by name [message #495840] Fri, 06 November 2009 11:46 Go to next message
Missing name Missing name is currently offline Missing name Missing nameFriend
Messages: 94
Registered: November 2009
Member
Hi,
Im using the BIRT report designed with a query of the form
select * from myTable where col1 = ? and col2 = ?



where I have defined two input parameters

param1 and param2 which are integers.

This works fine but if I want to do something along the lines of
select * from myTable where col1 = ? and col2 = ? and col3 < ?



it complains about binding, as I assume its confused about which parameter I am comparing against col3. Is it possible to use a parameter more than once in a query, like I want to do?

Can I reference the parameters by name rather than ? such as

select * from myTable where col1 = param1 and col2 = param2 and col3 < param2


I tried various combinations of params["param2"].value and :param2 but cant get it to work

thanks

Tom

[Updated on: Fri, 06 November 2009 11:47]

Report message to a moderator

Re: Referencing parameter by name [message #495949 is a reply to message #495840] Fri, 06 November 2009 18:18 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Tom,

Either just create a 3rd parameter and set it to the same thing as the
second or drop the dataset parameters and do the query in the before
open script

this.queryText = "select * from myTable where col1 = " +
jsvar/reportparm + ....


Jason

TREVOR.HIGGINS@FINEOS.COM wrote:
> Hi,
> Im using the BIRT report designed with a query of the form
>
> select * from myTable where col1 = ? and col2 = ?
>
> where I have defined two input parameters
>
> param1 and param2 which are integers.
>
> This works fine but if I want to do something along the lines of
> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>
> it complains about binding, as I assume its confused about which
> parameter I am comparing against col3. Is it possible to use a parameter
> more than once in a query, like I want to do?
>
> Can I reference the parameters by name rather than ? such as
>
> select * from myTable where col1 = param1 and col2 = param2 and col3 <
> param2
>
> I tried various combinations of params["param2"].value and :param2 but
> cant get it to work
>
> thanks
>
> Tom
Re: Referencing parameter by name [message #496079 is a reply to message #495949] Sun, 08 November 2009 04:44 Go to previous messageGo to next message
Colin Sutton is currently offline Colin SuttonFriend
Messages: 72
Registered: July 2009
Member
Hi jason,

This sounds like it could be very useful for me. Could you be a bit more explicit?
What would the code be to pick up param1, param2 and param3?

Regards,
Colin

Jason Weathersby wrote:
> Tom,
>
> Either just create a 3rd parameter and set it to the same thing as the
> second or drop the dataset parameters and do the query in the before
> open script
>
> this.queryText = "select * from myTable where col1 = " +
> jsvar/reportparm + ....
>
>
> Jason
>
> TREVOR.HIGGINS@FINEOS.COM wrote:
>> Hi,
>> Im using the BIRT report designed with a query of the form
>>
>> select * from myTable where col1 = ? and col2 = ?
>>
>> where I have defined two input parameters
>>
>> param1 and param2 which are integers.
>>
>> This works fine but if I want to do something along the lines of
>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>
>> it complains about binding, as I assume its confused about which
>> parameter I am comparing against col3. Is it possible to use a
>> parameter more than once in a query, like I want to do?
>>
>> Can I reference the parameters by name rather than ? such as
>>
>> select * from myTable where col1 = param1 and col2 = param2 and col3 <
>> param2
>>
>> I tried various combinations of params["param2"].value and :param2 but
>> cant get it to work
>>
>> thanks
>>
>> Tom
Re: Referencing parameter by name [message #496173 is a reply to message #495840] Mon, 09 November 2009 09:30 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing nameFriend
Messages: 94
Registered: November 2009
Member
Thanks Jason - I created a third dataset parameter and bound it to an existing report parameter and this worked perfectly (not exactly the most intuitive way of doing it but it works!)

So basically I have the following binding:

Dataset Report
param1 -> param1
param2 -> param2
param3 -> param2


thanks for the help
Re: Referencing parameter by name [message #496336 is a reply to message #496079] Mon, 09 November 2009 18:04 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Colin

What code do you want?

Jason

Colin wrote:
> Hi jason,
>
> This sounds like it could be very useful for me. Could you be a bit more
> explicit?
> What would the code be to pick up param1, param2 and param3?
>
> Regards,
> Colin
>
> Jason Weathersby wrote:
>> Tom,
>>
>> Either just create a 3rd parameter and set it to the same thing as the
>> second or drop the dataset parameters and do the query in the before
>> open script
>>
>> this.queryText = "select * from myTable where col1 = " +
>> jsvar/reportparm + ....
>>
>>
>> Jason
>>
>> TREVOR.HIGGINS@FINEOS.COM wrote:
>>> Hi,
>>> Im using the BIRT report designed with a query of the form
>>>
>>> select * from myTable where col1 = ? and col2 = ?
>>>
>>> where I have defined two input parameters
>>>
>>> param1 and param2 which are integers.
>>>
>>> This works fine but if I want to do something along the lines of
>>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>>
>>> it complains about binding, as I assume its confused about which
>>> parameter I am comparing against col3. Is it possible to use a
>>> parameter more than once in a query, like I want to do?
>>>
>>> Can I reference the parameters by name rather than ? such as
>>>
>>> select * from myTable where col1 = param1 and col2 = param2 and col3
>>> < param2
>>>
>>> I tried various combinations of params["param2"].value and :param2
>>> but cant get it to work
>>>
>>> thanks
>>>
>>> Tom
Re: Referencing parameter by name [message #496391 is a reply to message #496336] Mon, 09 November 2009 20:09 Go to previous messageGo to next message
Colin Sutton is currently offline Colin SuttonFriend
Messages: 72
Registered: July 2009
Member
I have a report with two parameters which I match dozens of times in a query on multiple database
sources.
It's tedious matching all the ? and setting dataset param1, param2, ... all to the same report
parameter.
In the example below would the complete before open script be:
this.queryText = "select * from myTable where col1 = " + jsvar/reportparam1 + " and col2 = "
jsvar/reportparam2 " and col3 < " jsvar/reportparam1

Regards,
Colin

Jason Weathersby wrote:
> Colin
>
> What code do you want?
>
> Jason
>
> Colin wrote:
>> Hi jason,
>>
>> This sounds like it could be very useful for me. Could you be a bit
>> more explicit?
>> What would the code be to pick up param1, param2 and param3?
>>
>> Regards,
>> Colin
>>
>> Jason Weathersby wrote:
>>> Tom,
>>>
>>> Either just create a 3rd parameter and set it to the same thing as
>>> the second or drop the dataset parameters and do the query in the
>>> before open script
>>>
>>> this.queryText = "select * from myTable where col1 = " +
>>> jsvar/reportparm + ....
>>>
>>>
>>> Jason
>>>
>>> TREVOR.HIGGINS@FINEOS.COM wrote:
>>>> Hi,
>>>> Im using the BIRT report designed with a query of the form
>>>>
>>>> select * from myTable where col1 = ? and col2 = ?
>>>>
>>>> where I have defined two input parameters
>>>>
>>>> param1 and param2 which are integers.
>>>>
>>>> This works fine but if I want to do something along the lines of
>>>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>>>
>>>> it complains about binding, as I assume its confused about which
>>>> parameter I am comparing against col3. Is it possible to use a
>>>> parameter more than once in a query, like I want to do?
>>>>
>>>> Can I reference the parameters by name rather than ? such as
>>>>
>>>> select * from myTable where col1 = param1 and col2 = param2 and col3
>>>> < param2
>>>>
>>>> I tried various combinations of params["param2"].value and :param2
>>>> but cant get it to work
>>>>
>>>> thanks
>>>>
>>>> Tom
Re: Referencing parameter by name [message #496404 is a reply to message #496079] Mon, 09 November 2009 21:57 Go to previous messageGo to next message
Scott Rosenbaum is currently offline Scott RosenbaumFriend
Messages: 425
Registered: July 2009
Senior Member
One other approach is to do your DataBinding after the report runs. We have
created a Script Function that does just that.

Have a look at the project where we are hosting this here. http://code.google.com/p/birt-functions-lib/

The specific function you are looking for is http://code.google.com/p/birt-functions-lib/wiki/BindParamet ers.

Using this function your SQL would like this:

select *
from myTable
where 0 = 0
/* BIND AND col1 = $param1 */
/* BIND AND col2 = $param2 */
/* BIND AND col3 < $param2 */

Where param1 and param2 are the names of the parameters. All you have to
do is install the plugin using the update manager, and you should be able
to use these functions.

Works great for multi-select parameters too.

Scott Rosenbaum

> Hi jason,
>
> This sounds like it could be very useful for me. Could you be a bit
> more explicit? What would the code be to pick up param1, param2 and
> param3?
>
> Regards,
> Colin
> Jason Weathersby wrote:
>
>> Tom,
>>
>> Either just create a 3rd parameter and set it to the same thing as
>> the second or drop the dataset parameters and do the query in the
>> before open script
>>
>> this.queryText = "select * from myTable where col1 = " +
>> jsvar/reportparm + ....
>>
>> Jason
>>
>> TREVOR.HIGGINS@FINEOS.COM wrote:
>>
>>> Hi,
>>> Im using the BIRT report designed with a query of the form
>>> select * from myTable where col1 = ? and col2 = ?
>>>
>>> where I have defined two input parameters
>>>
>>> param1 and param2 which are integers.
>>>
>>> This works fine but if I want to do something along the lines of
>>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>>
>>> it complains about binding, as I assume its confused about which
>>> parameter I am comparing against col3. Is it possible to use a
>>> parameter more than once in a query, like I want to do?
>>>
>>> Can I reference the parameters by name rather than ? such as
>>>
>>> select * from myTable where col1 = param1 and col2 = param2 and col3
>>> < param2
>>>
>>> I tried various combinations of params["param2"].value and :param2
>>> but cant get it to work
>>>
>>> thanks
>>>
>>> Tom
>>>
Re: Referencing parameter by name [message #496602 is a reply to message #496391] Tue, 10 November 2009 16:07 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Colin,

That is correct. Reference report parameters like
params["myreportname"].value

Jason

Colin wrote:
> I have a report with two parameters which I match dozens of times in a
> query on multiple database sources.
> It's tedious matching all the ? and setting dataset param1, param2, ...
> all to the same report parameter.
> In the example below would the complete before open script be:
> this.queryText = "select * from myTable where col1 = " +
> jsvar/reportparam1 + " and col2 = " jsvar/reportparam2 " and col3 < "
> jsvar/reportparam1
>
> Regards,
> Colin
>
> Jason Weathersby wrote:
>> Colin
>>
>> What code do you want?
>>
>> Jason
>>
>> Colin wrote:
>>> Hi jason,
>>>
>>> This sounds like it could be very useful for me. Could you be a bit
>>> more explicit?
>>> What would the code be to pick up param1, param2 and param3?
>>>
>>> Regards,
>>> Colin
>>>
>>> Jason Weathersby wrote:
>>>> Tom,
>>>>
>>>> Either just create a 3rd parameter and set it to the same thing as
>>>> the second or drop the dataset parameters and do the query in the
>>>> before open script
>>>>
>>>> this.queryText = "select * from myTable where col1 = " +
>>>> jsvar/reportparm + ....
>>>>
>>>>
>>>> Jason
>>>>
>>>> TREVOR.HIGGINS@FINEOS.COM wrote:
>>>>> Hi,
>>>>> Im using the BIRT report designed with a query of the form
>>>>>
>>>>> select * from myTable where col1 = ? and col2 = ?
>>>>>
>>>>> where I have defined two input parameters
>>>>>
>>>>> param1 and param2 which are integers.
>>>>>
>>>>> This works fine but if I want to do something along the lines of
>>>>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>>>>
>>>>> it complains about binding, as I assume its confused about which
>>>>> parameter I am comparing against col3. Is it possible to use a
>>>>> parameter more than once in a query, like I want to do?
>>>>>
>>>>> Can I reference the parameters by name rather than ? such as
>>>>>
>>>>> select * from myTable where col1 = param1 and col2 = param2 and
>>>>> col3 < param2
>>>>>
>>>>> I tried various combinations of params["param2"].value and :param2
>>>>> but cant get it to work
>>>>>
>>>>> thanks
>>>>>
>>>>> Tom
Previous Topic:Birt view in an eclipse project
Next Topic:Reset page number for each master page
Goto Forum:
  


Current Time: Mon Dec 22 02:17:17 GMT 2014

Powered by FUDForum. Page generated in 0.14077 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software