Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 Go to next message
Gaurav  is currently offline Gaurav
Messages: 7
Registered: December 2010
Junior Member
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 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. Polunsky
Messages: 197
Registered: July 2009
Location: Houston TX
Senior Member

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.
icon14.gif  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 Go to previous message
Gaurav  is currently offline Gaurav
Messages: 7
Registered: December 2010
Junior Member
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:
  


Current Time: Fri Aug 01 06:04:05 EDT 2014

Powered by FUDForum. Page generated in 0.02962 seconds