Eclipse Community Forums: BIRT » How to search for multiple values when a particular parameter is selected

Home » Archived » BIRT » How to search for multiple values when a particular parameter is selected(When a value is selected, I need the query to search on that value plus 1-3 other pre-defined values)

Show: Today's Messages :: Show Polls :: Message Navigator

How to search for multiple values when a particular parameter is selected [message #1709611]

Tue, 29 September 2015 14:36

Scott Williams

Messages: 10
Registered: March 2014

Junior Member

I'm new to BIRT so please bear with me. I have a report where the user selects a string value for one of the parameters. The value of that parameter is then used in one of the data set queries. What I want to accomplish is, when a particular value is selected, I need the report to also search for 1-3 other pre-defined values in the query. For example, if the user selects F-MAC, I want the data set to return all results where the category is F-MAC, as well as C-CDB, F-FMB, and M-MAC. If they select F-DAMAGE the data set should return all results where the category is F-DAMAGE as well as M-DAMAGE.

The parameter, Categories, is Static, with 4 values available.

Here is the query that uses the value of that parameter:

select
WO_WORKORDER,
WO_STATUS,
WO_DATE_COMPLETED,
PH_PHASE,
PH_STATUS,
WO_CATEGORY,
WO_CATEGORY_DESC,
WO_DEPT_DESC,
WO_DEPT_ID,
WO_FACILITY_DESC,
WO_BUILDING_DESC,
FTD_TRAN_TYPE,
FTD_AMOUNT,
FTD_TM_TYPE
from VW_WO_BILLING
where
WO_TYPE = 'F'
and WO_STATUS = 'CLOSED'
and PH_STATUS = 'CLOSED'
and FTD_TRAN_TYPE in ('CW','WX','TW','TP')
and WO_CATEGORY = ?
and
(wo_dept_id = ? or ? = 'ALL')
and TRUNC(WO_DATE_COMPLETED) >= ?
and TRUNC(WO_DATE_COMPLETED) <= ?
and FTD_AMOUNT <> 0

My back-up plan is to change the parameter to "Allow Multiple Values" and add all of the Categories. The user would then have to know which to select for each case. This is undesirable, but if there's no way to accomplish what I'm trying to do then so be it. Thanks in advance.

Report message to a moderator

Re: How to search for multiple values when a particular parameter is selected [message #1709615 is a reply to message #1709611]

Tue, 29 September 2015 15:22

Jerry Sheets

Messages: 76
Registered: July 2009
Location: Salt Lake City, UT

Member

Here is how I handle it: Instead of the 'and WO_CATEGORY = ? ' select the dataset, then click on the 'Script' tab and enter something like the following in the beforeOpen method. (The if syntax may not be exactly right, but you get the idea.)

// Append Where Clause for WO_CATEGORY
if (params["parmCategory"].value == "F-MAX"){
this.queryText = this.queryText + " and wo_category in('F-MAC','C-CDB','F-FMB','M-MAC')"
}else{
if (params["parmCategory"].value == "F-DAMAGE"){
this.queryText = this.queryText + " and wo_category in('F-DAMAGE','M-DAMAGE')"
}else{
this.queryText = this.queryText + " and wo_category = '" + params["parmCategory"]+"'"
}

Report message to a moderator

Re: How to search for multiple values when a particular parameter is selected [message #1709626 is a reply to message #1709615]

Tue, 29 September 2015 16:24

Scott Williams

Messages: 10
Registered: March 2014

Junior Member

Thanks for the response! That seems like the perfect way to accomplish this! I've been trying to implement it but I keep getting error messages. They've varied, but they usually seem to be saying that a ; is missing. I've tweaked the script several times but can't seem to get it to work. Any ideas?

// Append Where Clause for WO_CATEGORY
if (params['Categories'].value == 'F-MAC') 
{this.queryText = this.queryText + ' and wo_category in('F-MAC','C-CDB','F-FMB','M-MAC')'}
else if (params['Categories'].value == 'F-DAMAGE')  
{this.queryText = this.queryText + ' and wo_category in('F-DAMAGE','M-DAMAGE')'}
else if (params['Categories'].value == 'F-DISASTER')  
{this.queryText = this.queryText + ' and wo_category in('F-DISASTER','M-DISASTER')'}
else if (params['Categories'].value == 'F-PMB')  
{this.queryText = this.queryText + ' and wo_category in('F-PMB','M-PMB')'}
else {this.queryText = this.queryText + " and wo_category = '" + params["parmCategory"]+"'"}
End if;

Report message to a moderator

Re: How to search for multiple values when a particular parameter is selected [message #1709628 is a reply to message #1709626]

Tue, 29 September 2015 16:33

Scott Williams

Messages: 10
Registered: March 2014

Junior Member

The last line before the End if seems a little strange to me. I've also tried the following but it doesn't work either:

else {this.queryText = this.queryText + ' and wo_category = ?'}

Report message to a moderator

Re: How to search for multiple values when a particular parameter is selected [message #1709637 is a reply to message #1709628]

Tue, 29 September 2015 18:53

Scott Williams

Messages: 10
Registered: March 2014

Junior Member

I ended up using this.queryText = this.queryText.replace instead. I think the problem above was the end if, because once I took that off for my new script, it worked right away. Thanks for your help!

Report message to a moderator