Using Filter/Parameter with input text box to type values in Canned Reporting

In this blow we are going to cover how to use a input box type as a parameter/filter while creating a canned report. This blog is relevant if you are using Open Source BI Helical Insight Enterprise version 6.0 onwards.

An input type filter can allow an end user a box wherein he can put the desired value. Based on the value input he has put (and the filtering condition which has been configured), the data can accordingly get changed.

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

1. 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.

2. 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"

3. STEP3 : After providing the SQL query, click on Save and then Run. You will see the first 10 records of the resultset in the preview section. This is especially useful

4. STEP 4: Add an input parameter

In the 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 can create a parameter configuration to pass either a single value or multiple values in the input box. For a single value, we use String or Numeric depending on the column’s data type, and for multiple values, we use a Collection.

The parameter is named travel_medium. Since this is an input-type parameter, where the user does not see any dropdown values but directly enters the desired value in the text box, no SQL should be provided in the parameter SQL placeholder.

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 value a user is inputting.

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.

SQL Query to accept a single value from the input box :

SELECT * FROM "travel_details" WHERE "travel_medium" = ${travel_medium}

SQL Query to accept multiple values from the input box :

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 for single value from input box :

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

• Report SQL with ALL Handling for single value from input box :

  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 condition _all_ LIKE ‘_all_’ for a single-value input box or _all_ IN (‘_all_’) for multiple-value input box 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.

STEP 7: Save the report, open it in a window, and test the report with the parameter. In the input text box you can type the required value, click on Apply and see the desired resultset.

Passing a single value in an input box parameter
Example: Cab

Passing multiple values in an input box parameter
Example: Bus,Cab,Train