I need help using $X for java.util.Collection parameters

hal_1 · April 18, 2018

I have setup a parameter as a java.util.collection, let's call it var1

I use this in my where clause so if nothing is selected for the parameter, I will get a match. It looks like this:

and ($X{IN,null,var1} or $X{IN, column_1, var1})

I've been doing this for over 5 years and now it doesn't want to work when I don't select anything for the parameter. Any ideas why?

mlopez_1 · April 18, 2018

You can use var1.size() > 0 to know if some value was added, but if no values was added.

For better understanding you must check out this URL: http://jasperreports.sourceforge.net/sample.reference/query/

Mariano

joseng62 · April 18, 2018

What is the deafult value in the expression for the given parameter ?
You need to instantiate an empty array in the expression : new ArrayList(Arrays.asList()) which will give you the all effect. Select all.
You could create another parameter that does a check if array is empty (there should be is empty method) and then use that array in your sql query to either pass a empty array or the array as is with values selected already.

hal_1 · April 22, 2018

I created a small report to demonstrate how I understand the java.collection to work. The parameters

term_select and SelectForm_Status should both work finding any item the user selects and if they don't enter anything for the parameter, all records are returned.

If I don't enter a valid option for the java.collection parameters, I don't find any records.

Here's the code:

<?xml version="1.0" encoding="UTF-8"?>

</parameter>

</parameter>

<![CDATA[-- value_counts subquery

select --

form_submission_items.`form_item_id` as form_item_id,

-- case when $Xx{IN,null,term_select} then 1 else 0 end as term_null,

"" as term_null,

form_submission_items.value as value,

case when form_submission_items.value like "%|%" then form_submission_items.value_label else form_item_values.label end as value_label,

form_submission_items.value_label as label,

count(distinct (case when form_submission_items.value in ("N/A","N/R","N/O") then null else form_submission_items.id end)) as value_count

from form_submission_items

left join form_submission_sections on form_submission_items.`form_submission_section_id` = form_submission_sections.id

left join form_submissions on form_submission_sections.`form_submission_id` = form_submissions.id

left join practicums on practicums.id = form_submissions.practicum_id

left join form_item_values on form_submission_items.`form_item_id` = form_item_values.`form_item_id` and form_submission_items.value = form_item_values.value

left join forms on form_submissions.form_id = forms.id

where forms.school_id = $P{school_id}

and forms.id = $P{Select_Form}

and ($X{IN,null,Select_Form_Status} or $X{IN, form_submissions.status, Select_Form_Status}) >>>>> This is where I'm having a problem

and ($X{IN,null,term_select} or $X{IN, practicums.term_id,term_select}) >>>>> This is where I'm having a problem I want to find all records if nothing is entered for term_select

-- and (case when $Xx{IN,null,term_select} then 1 else 0 end = 0 or $Xx{IN, practicums.term_id, term_select})

and ($P{submission_start_date} is null or date(form_submissions.requested_at) between $P{submission_start_date} and $P{submission_end_date})

and form_submission_items.`input_type` in ("radio", "checkbox","select","text_area")

group by form_item_id,value

order by form_item_id,value]]>

</queryString>

</field>

</field>

</field>

</field>

</field>

</background>

<title>

</title>

</pageHeader>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</band>

</columnHeader>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</band>

</detail>

</columnFooter>

</pageFooter>

</summary>

</jasperReport>

Thanks,

Hal

sfitzsim · September 22, 2020

Per community user,

Cliff Cowperthwait

I got it to work by using a subquery(highlighted):

It appears to be the only way to use the Collection Type in a $X{IN }

Select * from (

select distinct fgbencd_orgn_code "ORGN",

O.ftvorgn_title "DEPARTMENT",

fgbencd_fund_code "FUND",

ftvfund_ftyp_code "TYPE",

fgbencd_acct_code "ACCT",

fgbench_num "PO",

spriden_id "VENDORID",

spriden_last_name "NAME",

fgbench_estab_date "ESTABLISHED",

sum(nvl(fgbencp_orig_encb_amt,0)) "ORIGINALENC",

sum(nvl(fgbencp_orig_encb_amt,0)+

nvl(fgbencp_sum_encb_adjt,0)+

nvl(fgbencp_sum_encb_liq,0)) "ROLLOVERAMT"

FROM fimsmgr.fgbench inner join fimsmgr.fgbencd on fgbencd_num = fgbench_num

inner join fimsmgr.fgbencp on fgbencp_num = fgbencd_num and fgbencp_item = fgbencd_item and fgbencp_seq_num = fgbencd_seq_num

inner join saturn.spriden on spriden_pidm = fgbench_vendor_pidm and spriden_change_ind is null

inner join fimsmgr.ftvfund on ftvfund_fund_code = fgbencd_fund_code and ftvfund_eff_date = (select max(ftvfund_eff_date) from FTVFUND I where ftvfund_fund_code = fgbencd_fund_code)

inner join fimsmgr.ftvorgn O on O.ftvorgn_orgn_code = fgbencd_orgn_code

and O.ftvorgn_eff_date = (select max(ftvorgn_eff_date)

from ftvorgn

where ftvorgn_orgn_code = O.ftvorgn_orgn_code

)

WHERE Substr(ltrim(fgbench_num),1,1) NOT IN ('R','E')

and FGBENCH_STATUS_IND <> 'C'

and FGBENCD_STATUS <> 'C'

AND fgbench_estab_date >= '01-oct-2000'

AND fgbench_estab_date <= '03-mar-2099'

AND fgbencp_fsyr_code = (SELECT substr(to_char(MAX(to_number(fofycnv(fgbencp_fsyr_code)))),3,2)

FROM fgbencp

WHERE fgbencp_num = fgbencd_num

AND fgbencp_item = fgbencd_item

AND fgbencp_seq_num = fgbencd_seq_num )

and 0 < (SELECT sum(nvl(fgbencp_orig_encb_amt,0)+

nvl(fgbencp_sum_encb_adjt,0)+

nvl(fgbencp_sum_encb_liq,0))

FROM fgbencp

WHERE fgbencp_num = fgbench_num

AND fgbencp_item = fgbencd_item

AND fgbencp_seq_num = fgbencd_seq_num

AND fgbencp_fsyr_code = (SELECT substr(to_char(MAX(to_number(fofycnv(fgbencp_fsyr_code)))),3,2)

FROM fgbencp

WHERE fgbencp_num = fgbencd_num

AND fgbencp_item = fgbencd_item

AND fgbencp_seq_num = fgbencd_seq_num ))

and

1= CASE

When $P{VENNUM} is null

then 1

When spriden_id like concat(concat('%' , $P{VENNUM}) , '%' )

then 1

else 0

END

GROUP BY fgbencd_orgn_code ,

ftvorgn_title,

fgbencd_fund_code ,

ftvfund_ftyp_code ,

fgbencd_acct_code ,

fgbench_num,

spriden_id ,

spriden_last_name ,

fgbench_estab_date

)

where $X{IN, ORGN, DEPT}

and $X{IN, ACCT, ACCTS}

and $X{IN, FUND, FUNDS}

Hit “READ FIELDS” and save it. The create a query List Of Values on the server to match.

Sign In

I need help using $X for java.util.Collection parameters

Recommended Posts

hal_1

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

mlopez_1

Link to comment

Share on other sites

joseng62

Link to comment

Share on other sites

hal_1

Link to comment

Share on other sites

sfitzsim

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore