In this blog we shall see how to create canned reports with multi select input parameters.

STEP 1: Create a connection

To know more about the types of datasource connections that can be established, refer to the blog.

STEP 2: Write a query without using parameter/where clause

1. After providing the connection details in the previous step, click on + button next to Queries.



2. Select the connection to be used for the query.



3. Click on Configuration icon and write the query without where clause or any input parameters in the editor pane.



4. Save the query by clicking on Apply or using keyboard shortcut Ctrl+S.
5. Click on Run to execute the query. This will return the top 10 rows of the resultset.

STEP 3: Add an input parameter

1. Click on + icon next to parameter. You can add as many parameters as required.



2. Select the type of input parameter. For multi-select parameters, the type should be set to Collection.



3. Edit the name of the parameter by clicking on the pencil icon.



STEP 4: Edit the Configuration of the input parameter

1. Select the connection for the query of the input parameter.



2. Write the query that will return the input parameter column in the SQL section.



If the query is of groovy or adhoc type, select the correct type by clicking on S and selecting the type. The three options available are SQL, SQL.GROOVY, SQL.ADHOC



3. Click on Apply or Ctrl+S to save the query
4. Go to configuration section and give the configuration that you desire. Please note that the variable name, name specified within the configuration and the name of the parameter should match exactly as shown in the below image. All the three highlighted portions should have the exactly the same name

Since in this case we are using Multiselect, multiple tag should be set as true.



The following options are available in the configuration –

• Name: Give the name of the parameter in double quotes in this section. As mentioned earlier the name should be same as the parameter name
• displayName : While a person is viewing the report the name which is given here will display at the top of that input parameter box.
• type: Type of input parameter. The various options are select (normal select box), select2 (searchable select box), datepicker, daterangepicker, input, button and checkbox. Checkbox is also one of the option but not visible in the comment. Checkbox is an multiselect parameter.
• parameters: This is an optional section and to be used while using cascading input parameters. Name the columns that this parameter is dependent on.
• listeners : This is an optional section and to be used while using cascading input parameters. Name the columns that this parameter should listen to.
• requestParameters : name of components acting as input.
• triggers : add component names to trigger on change
• multiple: whether the parameter is multi-select or not. Default is false (single select). When multiselect is to be used this has to be made true.
  -multiselect is to be used this has to be made true.
• display : value to be displayed. Give name of the column whose values are to be displayed
• value: column name whose value should be returned
• all: true – this is used only for checkbox
• displayFormat : used for date/datetimepicker
• outputFormat : used for date/datetimepicker
• executeAtStart: whether the parameter values are to be populated during report load
• map: this is the number of the SQL that is to be used for the filter. Ensure that this value is correct.



5. Set the default values for the parameter using the following syntax. This has to be put on the “Configuration” screen. This is the default values which the input parameter will have when the report opens.

Dashboard.setVariable('parameter_name ', 'default_value ');

For multiselect parameters, use array to define the default value. Since in this case it is a multiselect value we are using the below kind of syntax.

Dashboard.setVariable('parameter_name ',[ 'default_value1 ', 'default_value2 ']);

STEP 5: Add listener of parameter to query section

Now we have to specify that this report should listen to that specific input parameter. Click on P icon. You will see the added parameter list. Select the parameter(s) that you want to use in the query



STEP 6: Add where clause to the query

Now in the actual SQLQuery for the report we will add parameter so that it can listen and whatever is the value users selects it gets passed to that specific placeholder.

Add where clause in the query section using the selected parameters. For using the values returned by the parameter, use the parameter name with $ sign and within {}. Syntax – (${parameter_name}).

Save the query and execute to get the resultset.



STEP 7: Go to the Canvas to Use Parameters on the report

After making all the changes in the datasources page, go to canned report canvas.

1. In the Datasources pane under Calculations/Parameters, you will see that the parameter has been added. This parameter can now be used in the report, if required by simply dragging it like any other variable.



2. Go to Preview Parameters section in the property pane



By default, “Show Parameters” will be checked. If you do not want to show the parameters to end user, you can deselect this.

“Position” section defines where the parameter section should be visible with respect to the report when the report is mainly being used in read mode by end users. There are 4 options: right, top, left and bottom.

STEP 8: Preview the report

Click on Preview icon to preview the report. Click on the hamburger icon, you can see the parameter in the position that you have mentioned.



STEP 9: After the report is satisfactory, save the report in desired location.

Learn More about Different Input Parameter Usages

• Creating Canned Reports with Input Parameters with Single Select
• Types of Input Parameter In Canned Reports Interface Of Helical Insight
• Creating Canned Reports with Date Picker as Input Parameters
• Creating Canned Reports with a Date Range selector Input Parameters
• Create Canned Reports With ALL As Default Value In Input Parameters

For further assistance, kindly contact us on support@helicalinsight.com or post your queries at Helical Forum