Creating a Studio report parameter that caters for both multi and single select input controls

Requirement:
We have a requirement to change the input control type from multi-select query to single-select query from just the UI without changing the query of the report under the 'Dataset and Query ...' tab in Studio.

The parameter used in the report is of java.util.Collection class and the field used for the parameter is of java.lang.String. The query of the report is using the $X{} parameter.

When we change the input control type to single-select query from the server UI, the report fails with the error - Invalid type java.lang.String for parameter used in an IN clause; the value must be an array or a collection.

The purpose of this requirement is to have our report consuming clients be able to change the input control type just from the server UI without having to change the report structure from the backend, and still have a seamless experience when switching between single-select and multi-select query input control types.

Is it possible to achieve this requirement of changing the input control type from multi-select query to single-select query just from the UI without changing the report query using the $X{} parameter?

Solution:
We can workaround this requirement by using two parameters. A test 'multiToSingleParameter.jrxml' report and its export catalog can be found in the attachments section of this article. The export catalog can be imported using the 'Legacy Key', and the report can be run using the sample Foodmart database.

The relevant part is this:
<parameter name="department_description" class="java.lang.Object" nestedType="java.lang.String"/>
<parameter name="department_description_collection" class="java.util.Collection" isForPrompting="false">
<defaultValueExpression><![CDATA[iF($P{department_description} instanceof Collection, $P{department_description}, IF($P{department_description} == null, null, Collections.singleton($P{department_description})))]]></defaultValueExpression>
</parameter>
<queryString language="SQL">
<![CDATA[select * from department
where $X{IN, department_description, department_description_collection}]]>
</queryString>

The query filters results for the department_description value(s) supplied.

Explanation:
Here, we have created two parameters:
1. department_description (which will be seen by the report consuming customers, and to which the customers will pass the required values).

The parameter is of java.lang.Object class with the nestedType of our field data type, java.lang.String.

2. department_description_collection (used to meet our requirement to be able change the input control type from multi-select query to single-select query just from the UI without changing the query of the report under the 'Dataset and Query ...' tab in Studio.).

The parameter is of java.util.Collection class with isForPrompting set to false.

This is the main parameter to meet our requirement, which takes into consideration the $P{department_description} value in its default value expression and uses this expression to determine whether this parameter should be used for single-select or multi-select input control.

Let us take a look at the default value expression of this parameter:
<parameter name="department_description_collection" class="java.util.Collection" isForPrompting="false">
<defaultValueExpression><![CDATA[iF($P{department_description} instanceof Collection, $P{department_description}, IF($P{department_description} == null, null, Collections.singleton($P{department_description})))]]></defaultValueExpression>
</parameter>

IF($P{department_description} instanceof Collection, $P{department_description}, IF($P{department_description} == null, null, Collections.singleton($P{department_description})))

As mentioned earlier, the default value expression of this parameter takes $P{department_description} parameter value into consideration.

It checks if $P{department_description} is an instanceOf Collection. If yes, then it passes the collection values supplied which can be used with multi-select input control type.

Next, if it is not a collection, it checks if the value supplied to the $P{department_description} is null. If yes, then it passes the input control value as null.

Finally, if not a single situation from the above is met, it means we are passing a single value to the department_description input control. The department_description_collection parameter handles this situation with the expression Collections.singleton($P{department_description}). This is used with single-select query input control.

Again, a test 'multiToSingleParameter.jrxml' report and its export catalog can be found in the attachments section of this article. The export catalog can be imported using the 'Legacy Key', and the report can be run using the sample Foodmart database.

Thank you for your time and attention to this article. Hope this detailed explanation and the solution helps you to achieve your requirement of seamlessly changing the input control type (multi-select to single-select query) just from the server UI without having to change the report structure from the backend in Studio.

_____________________________________________________________________________________________________________________________________________________________
Ref. Case #02109620

multitosingleparameter.jrxml

multitosingleselect.zip