Search code examples
jasper-reportsjasperserver

Ignore nulls from multi-select input controls?

I have a report with several input controls that are used to populate another input control.

My input controls:

  1. GPI - a multi-select input control that has product codes. The parameter is a collection.
  2. NAME_LOOKUP - a single-value text box where the user can type a product name. The parameter is a string.
  3. NDC - a list of drug codes that obtains values based on what is input in GPI and NAME_LOOKUP.

The query that populates NDC has this WHERE clause:

 WHERE (
   REGEXP_LIKE(DESCRIPTION, $P{name_lookup}, 'i')
   OR REGEXP_LIKE(NDC, $P{name_lookup}, 'i')
       )
   OR ($X{IN, GPI, gpi})

It works as the $X syntax is designed to -- if the user doesn't select a GPI value from the list, my NDC input control shows all drug codes. However, I only want to show drug codes when a value is actually selected from the GPI input control.

When I try

 WHERE (
   REGEXP_LIKE(DESCRIPTION, $P{name_lookup}, 'i')
   OR REGEXP_LIKE(NDC, $P{name_lookup}, 'i')
       )
   OR ($X{IN, GPI, gpi} AND $P{gpi} IS NOT NULL)

I get an invalid column type in JasperReports Server for the NDC input control.

When I try

 WHERE (
   REGEXP_LIKE(DESCRIPTION, $P{name_lookup}, 'i')
   OR REGEXP_LIKE(NDC, $P{name_lookup}, 'i')
       )
   OR ($X{IN, GPI, gpi} AND $P!{gpi} IS NOT NULL)

I get a missing expression error.

What can I do to limit the NDC input control's results to just the selected GPI, and not all results?

Solution

  • I just found this while experiencing the same problem, I finally solved it using a second parameter holding the lenght of the list:

    <parameter name="potentially_empty_list" class="java.util.Collection"/>
<parameter name="num_of_list_elements" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression>
<![CDATA[$P{potentially_empty_list}.size()]]>
</defaultValueExpression>
</parameter>

    And the SQL query would then be:

    [... select etc...]
WHERE $X{IN,mysql_column_name, ist}
AND $P{num_of_list_elements} > 0

    which for an empty list would be sent to mysql as:

    WHERE 0 = 0 #(that's how jasper parses $X{IN..} for an empty collection
AND 0 > 0   # don't match anything :)