Eclipse Community Forums: BIRT » Report parameter mapping to jdbc data set IN query paramete

Home » Archived » BIRT » Report parameter mapping to jdbc data set IN query paramete(Report parameter mapping to jdbc data set IN query paramete)

Report parameter mapping to jdbc data set IN query paramete [message #646785]

Fri, 31 December 2010 02:11

Eclipse User

I created 2 data set from Data soure (JDBC)

Data_Set_1 query:
SELECT * FROM gen_table WHERE NAME IN (?)

Data_Set_2 query:
select distinct(gen_table.name) from gen_table

Create one report parameter : nameselection
This report parameter is created from Data_Set_2 query.
Data type : string
Display type : ListBox
Dynamic type with value bind to Data_Set_2 query.
Allow multiple value : true

Data set Data_Set_1 query parameter bind to report parameter "nameselection"

Problem I am facing :

if I am selected more than 1 value for report parameter nameselection say "V1" and "V2", only one value is considered in Data_Set_1 query

Means :

SELECT * FROM gen_table WHERE NAME IN ('V2')

But expected is

SELECT * FROM gen_table WHERE NAME IN ('V1', 'V2')

Re: Report parameter mapping to jdbc data set IN query paramete [message #646817 is a reply to message #646785]

Fri, 31 December 2010 09:37

Eclipse User

Have you searched through the newsgroup or the BIRT Exchange forums? This is a reasonably common question. The JDBC driver doesn't have any way of translating an array or collection object (the output of data set 2) into a comma-separated list (what you want for data set 1 parameter).

Fortunately, there's a cute workaround. The ListBox parameter object, when passed through the .toString() method, converts its contents to a comma separated list. You can actually rewrite data set 1 from "WHERE NAME IN (?)" to "WHERE NAME IN (0)", then go into the beforeOpen script of data set 1 and do a replace on the queryText of "(0)" with ListBoxParameter.toString()

EXAMPLE:
var valuelist = params["Limit_Method"].toString();
this.queryText = this.queryText.replace("(0)", "(" + valuelist + ")");

I'm doing this from memory, so I'll add the caveat that I've only used this for numeric listbox entries. I can't say for sure that it will work the same way for string entries but it should be worth trying.

Re: Report parameter mapping to jdbc data set IN query paramete [message #647035 is a reply to message #646817]

Tue, 04 January 2011 03:59

Eclipse User

Richard,

Thanks for giving me the right direction.

The way I done that

I add the script at beforeOpen event of dataset

*****************
var parameterList ="";
for (x = 0; x < params["NewParameter"].value.length; x++)
{
if (x > 0)
{
parameterList = parameterList + ", ";
}
parameterList = parameterList + "'"+ params["NewParameter"].value[x] + "'";
}

this.queryText = this.queryText.replace("(0)", "(" + parameterList + ")");

***************************************

Change the dataset query to :-

SELECT * FROM gen_table where WHERE NAME IN (0)

Previous Topic:	BIRT + RAP + WebViewer
Next Topic:	BIRT & gzip problem?

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 01:30:06 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter