In this article we will see how we can create a dynamic parameter for canned reports.This document has been created for Helical Insight version 4.1 GA.
A parameter is a variable that takes the value passed by the user and hence the data gets filtered before it is displayed on your screen.
A parameter can be static or dynamic. Static parameters are those that are created using the columns present in Select statement of the query. For example, in our sample database we can select “Travel_medium” as one parameter and all the values will be then populated under “Travel_medium” parameter from the database( as shown in the below image)
Dynamic parameters are those wherein a user can even select which column will get used as a filter as well. We can have our own filter expression here which gets executed as a part of the query. For eg: Travel_type‘domestic’ and mode_of_payment = ‘Cheque’. In the image below, static and dynamic parameters are shown.
STEP1 : Create the datasourceconnection.
You can learn more about the datasource connections from this blog.
STEP 2: Write a query without using parameter/where clause
A. After providing the connection details in the previous step, click on + button next to Queries.
B. Select the connection to be used for the query.
C. Click on Configuration icon highlighted by green box and write the query without where clause or any input parameters in the editor pane.
D. Save the query by clicking on Apply or using keyboard shortcut Ctrl+S.
E. Click on Run to execute the query.
STEP 3: Add a dynamic parameter
A. Click on + icon next to parameter. You can add as many parameters as required.
B. Select the type of input parameter. For dynamicWhere, the type should be set to String.
C. Edit the name of the parameter by clicking on the pencil icon next to delete icon.Let us give it a name dynamicWhere.
STEP 4: Edit the Configuration of the input parameter
A. Select the connection for the query of the input parameter.
B. Write the query that will return the input parameter column in the SQL section.To write the SQL for dynamic parameter we can select any table that is present in the main query. So a sample sql that would be generated with such default value would be Select * from table_name Where 1=1 and hence there is no trade off while executing the query.
C. 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
D. Click on Apply or Ctrl+S to save the query
E. 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
Dashboard.setVariable('dynamicWhere', '1=1');
var dynamicWhere = {
name: "dynamicWhere" ,
displayName : "dynamicWhere",
type: "input",
parameters:["dynamicWhere"],
listeners : [], // name of components
requestParameters :{}, // name of components
triggers : [], // add component names to trigger on change
options:{
multiple: false, // it can be true or false
display : 'column_name',
value : 'column_name',
placeholder: 'select value'
//all: true, // used for checkbox
//displayFormat : 'YYYY-MM-DD', // used for date/datetimepicker
//outputFormat : 'YYYY-MM-DD' // used for date/datetimepicker
},
htmlElementId : "#htmlelement_id",
executeAtStart: true, // it can be true or false
map: 2,
efwd : {
file: ‘__efwd_file_name__'
},
Since the user has to define what column is to be passed for filter, the type to be selected will be input. Following is the configuration for dynamic parameter.
The default value for this parameter will be 1=1. Type is input as the user passes the filter expression.
STEP 5: Add listener of parameter to query section
Now we have to specify that this report should listen to this dynamic parameter.
A. Click on P icon. You will see the added parameter list i.e, dynamicWhere. Select it.
B. Add brackets for listener.
Click on listener name, dynamicWhere to see the Quotes. Add () for open and closed as shown below.
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 passes, 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 result set.
STEP 7: Go to the Canvas to Use Parameters on the report
After adding dynamicWhere in the datasources page, go to Canvas.
1. In the Datasources pane under Calculations/Parameters, you will see that this parameter has been added. This parameter can now be used in the report, if required by simply dragging it like any other variable.
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, in this case dynamicWhere and its default value.
You will see the default value for dynamicWhere parameter here. It can be changed to any normal filter expression.
Dynamic parameter can be clubbed along with static parameter. For example for our sample travel data: Select * from “travel_details”where “travel_medium” = ${travel_medium} and ${dynamicWhere}.