Cascading Input Controls

Overview

This implementation is designed to support cascading behavior based on query input controls. These controls can use parameter values from other named parameter input controls whose values can resolve to a string. These strings are substituted into the defined query for that cascading input control with the usage of tokens specified in the cascading input control's query.

For example, a query such as:

SELECT billing_address_state
  FROM accounts
WHERE billing_address_country = $P{country}
   AND billing_address_state = $P{state}

Detailed specification

Examples

Country - State - Name cascade

p_country input control - Single select query input control.

  SELECT distinct billing_address_country
    FROM accounts
ORDER BY billing_address_country

p_state input control - Single select query input control.

  SELECT distinct billing_address_state
    FROM accounts
   WHERE billing_address_country = $P{p_country}
ORDER BY billing_address_state

p_name input control Single select query input control.

  SELECT distinct name
    FROM accounts
   WHERE billing_address_country = $P{p_country}
     AND billing_address_state = $P{p_state}
ORDER BY name

JRXML

....
<parameter name="p_country">
    <defaultvalueexpression>
        <!--[CDATA["USA"]]-->
    </defaultvalueexpression>
</parameter>
<parameter name="p_state">
    <defaultvalueexpression>
        <!--[CDATA["CA"]]-->
    </defaultvalueexpression>
</parameter>
<parameter name="p_name">
    <defaultvalueexpression>
        <!--[CDATA["Alpha-Murraiin Communications, Inc"]]-->
    </defaultvalueexpression>
</parameter>
<querystring>
    <!--[CDATA[select * from accounts where name = $P{p_name}]]-->
</querystring>
....

Multi-select control example

Included with sample reports.

Country_multi_select control

Multi-select query control. billing_address_country is the visible and value column of the control.

SQL:

  SELECT DISTINCT billing_address_country
    FROM accounts
ORDER BY billing_address_country

Cascading_state_multi_select

Multi-select query control. billing_address_state is the value column. billing_address_state and billing_address_country are the visible columns.

SQL:

  SELECT DISTINCT billing_address_state, billing_address_country
    FROM accounts
   WHERE $X{IN, billing_address_country, Country_multi_select}
ORDER BY billing_address_country, billing_address_state

JRXML

<parameter class="java.util.Collection" name="Country_multi_select">
    <defaultvalueexpression>
        <!--[CDATA[new ArrayList(Arrays.asList(new String[] {"USA"}))]]-->
    </defaultvalueexpression>
</parameter>
<parameter class="java.util.Collection" name="Cascading_state_multi_select">
    <defaultvalueexpression>
        <!--[CDATA[new ArrayList(Arrays.asList(new String[] {"CA"}))]]-->
    </defaultvalueexpression>
</parameter>
<querystring>
    <!--[CDATA[
          select * from accounts
           where $X{IN, billing_address_state, Cascading_state_multi_select}
             and $X{IN, billing_address_country, Country_multi_select}
        order by billing_address_country, billing_address_state, name
    ]]-->
</querystring>

Setting up query input controls for cascading

Parameter substitution in query input controls follows the same approach as for JasperReports. It works for queries of all types of query connections. $P, $P! and $X (for SQL queries) parameters are supported.

As now, single select query input controls return single values to reports and queries, and multi-select query input controls return collections.

A number of parameters will always be available for query input controls. "Standard" controls will always be provided to reports (not queries), even if an input control is not defined for them.

Changes to parameters for reports in JasperServer

The above "always available" parameters are also provided for reports if they are defined as parameters in the JRXML. This adds to the current LoggedInUser and LoggedInUsername parameters that are currently available.

Extension point for introducing additional built-in parameters

Classes that implement BuiltInParameterProvider can be included to provide parameters which are available to reports and queries.

public interface BuiltInParameterProvider {
     /**
      * Generate a set of standard parameters
      *
      * Each element is a JRParameter, value
      *
      * @param context
      * @param jrParameters unchanged
      * @param parameters unchanged
      * @return List<object[]> [JRParameter, value]
      */
     public List<object[]> getParameters(ExecutionContext context, List jrParameters, Map parameters);

     /**
      * Generate parameters can be requested by name that are not part of the standard set
      *
      * @param context
      * @param jrParameters unchanged, can be null
      * @param parameters unchanged
      * @param name of parameter
      * @return List<object[]> [JRParameter, value] or null if the given name is not set by this generator
      */
     public Object[] getParameter(ExecutionContext context, List jrParameters, Map parameters, String name);
}

To include your custom parameter provider, put the class into the WAR (in WEB-INF/classes or WEB-INF/lib) and add an instance of your class into the builtInParameterProviders bean in WEB-INF/applicationContext.xml, as follows:

<bean class="java.util.ArrayList" id="builtInParameterProviders">
    <constructor-arg>
        <list>
            <bean class="com.jaspersoft.jasperserver.api.engine.jasperreports.util.UserProfileBuiltInParameterProvider" />
            <bean class="your.package.and.class.name.here" />
        </list>
    </constructor-arg>
</bean>

Have a look at the UserProfileBuiltInParameterProvider class as an example.

See Also

• Built-in Parameters for Logged In User
• How to use built in parameter (LoggedInUser) in report and scriptlet
• Using report parameters
• Creating additional built-in parameters