Using Multi Select Drop Down Input Parameter/Filter in Canned Reporting

In this blog we are going to cover how to use a drop down option from which an end user can select a multiple values and use. Based on the values selected, the report data can accordingly get filtered out. The values of the drop down are populated by the SQLQuery. SQL query is executed at runtime, and the resulting list of values is displayed as selectable options. The user can select multiple values from the list.

This blog is relevant for the canned reporting module of Open Source BI product Helical Insight version 6.0 onwards.

Steps to create report with multi select parameter in Helical Insight canned report :

STEP 1: Go to Datasource and select the desired connection. Read more here about how to connect to datasources. You can write the SQLQuery here and drag drop the required fields to create the report.

STEP 2 : Write the required SQL for the report creation here. No need to put the where clause right now.

SQL :

SELECT * FROM "travel_details"

STEP3 : After providing the SQL query, click on Save and then <Run. You will see the first 10 records of the result set.

STEP 4: Add an input parameter

In the Datasource page itself, click on Parameter section, click on the + (plus) symbol to add a parameter. Each time you click, it adds a new parameter named parameter1, parameter2, parameter3, and so on.

On the right corner of its name, you can also see three dots, which give the following options:

• Edit — You can rename the parameter from parameter1 to your choice of name.
• Delete — You can delete the parameter.
• Type — You can choose the parameter type:
  1. String: Accepts text input for filtering or searching dimensions (e.g., customer name = “John”).
  2. Collection: Allows selecting multiple values from a list to slice or filter data (e.g., regions = [East, West])
  3. Numeric: Accepts numbers to filter or calculate measures (e.g., sales > 1000).

We have given the parameter name as “travel_medium” and set the type to Collection ( as we are creating multi select parameter).

Write the report SQL query to return distinct values for the filter dropdown. Then, click Save and then Run — it returns the first 10 records of the result set.

Parameter SQL :

select distinct "travel_medium" from "travel_details"

If you would like the dropdown values should appear in ascending/descending fashion, accordingly modify this SQL. The SQL should be the native SQL of the db to which you are connecting

STEP 5: Add where clause to the main report query

Earlier in Step 2 we had written a main report query. Now we will add where clause in the report query section allowing it to receive the values set by a user.

For using the values returned by the parameter, use the parameter name with $ sign and within {}. Syntax – ${parameter_name} (in our case the parameter name we kept travel_medium, hence in our case ${travel_medium})

For multi-select parameters, use IN keyword for comparison in the where clause as array is returned.

Save the query and execute it, you will get a parameter prompt as below there you need to provide some parameter value to validate SQL with dynamic parameter value.

Report SQL :

SELECT * FROM "travel_details" WHERE "travel_medium" in (${travel_medium})

Handling the “ALL” Option in Filter UI :

In the filter UI, the option shown to the user is ALL, but internally the value passed to SQL is _all_. Since the database column does not actually contain _all_, a direct equality check would return no records.

To handle this, the SQL condition is written as:

Report SQL with ALL Handling :

SELECT * FROM "travel_details" 
WHERE ("travel_medium" = ${travel_medium} OR '_all_' in (${travel_medium}))

• If a specific value is selected, only matching records are returned.
• If ALL is selected (internally _all_), the second part of the condition '_all_' in ('_all_') always evaluates to true, so all rows are returned.

STEP 6 : Parameter configuration (specifying the parameter type)

To configure the created parameters, navigate to CANVAS > Parameter, then expand the parameters section. This will display all the created parameters, which need to be configured individually.

In the configuration panel, you can specify options such as: selecting the input parameter type, mapping display value and key columns for a query-based drop-down list, defining date formats, setting a default value for the parameter and hiding the parameter if needed.

Hide Filter: Hides the parameter from the UI but if the value is passed via report URL it can still be passed to the canned report. Read here to learn how to pass filter values from URL.

Filter Type: Defines the type of input control (e.g., dropdown, text box, date picker). Read here to learn about each type.

Default Value: When the report opens by default with what filter/input parameter value should it show the data that value has to be defined here.

Multiple: Allows selecting multiple values if set to true, otherwise it allows to select only a single value only.

Display: Column shown to the user in the dropdown.

Value: Column whose value is passed to the query when selected.

In most of the cases, the Display and Value is the same. But in certain cases let’s say, we want to Display Employee_Name on the frontend for a user to select, whereas in the SQL where clause we want to pass Employee_ID, in those cases Display and Value field is useful.

Quotes: Controls whether the parameter value is wrapped in quotes in the query. Certain DB syntaxes might need a specific kind of quotes/syntaxes within which the filter values goes. That can be specified and used here.

In our case we will be using the option “Query based dropdown list” as the values of the dropdown is to be populated via the SQL which was written at Step 4. Display and Value we will keep the same. In Default Value we have kept as Bus, Cab that means by default the report will always open with showing Bus data. Quotes field is also set.

STEP 7: Save the report, open it in a window, and test the report with the parameter. You can see that it is showing multiple values from which an end user can select a value.