Creating Canned Reports with a Date Range selector Input Parameters

STEP 3: Add an input parameter

1. Click on + icon next to parameter. You can add as many parameters as required. In this case we will add two input parameters i.e. one startdate and the second is enddate



2. Select the type of input parameter. For dates the type of input parameter is “String”



3. Edit the name of the parameter by clicking on the pencil icon. As you can see we have given the parameter names as startdate and enddate. By default when you add the input parameters its name is created as parameter1 parameter 2 so on and so forth. It’s always advisable to rename them for easier understanding and usage.

STEP 4: Edit the Configuration of the input parameter

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



2. In cases of date picker / date range picker there is no need of writing SQL.
3. 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



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. We will use DateRangePicker
• parameters: Put the inputparameternames wherein the the values selected on the frontend will get passed to the parameters. We are putting startdate and enddate here.
• 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.
• 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 : Put the format which we would like to use to show on frontend. Like in this case we will put YYYY-MM-DD
• outputFormat : Put the format which we would like to use which should get passed to the SQLQuery. Like in this case we will put YYYY-MM-DD
• 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.



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

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

Below are the configuration screens for reference for startdate and for enddate. Since the daterange picker is a single component we are defining all the configuration for enddate also here only. As you can see have defined the Dashboard.setVariable for both the parameters and also in parameters we are passing the values. So the configuration screen for enddate can be completely blank.

For your reference you can see the configuration screen for enddate is completely blank.

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.