From version 5.0 of Helical Insight Enterprise Edition, the entire method of implementing cascading input parameters has been simplified and it can be acheived from the UI itself. Please refer to this blog to learn how to do that.
If you are using an older version you can go read further.
Cascading Parameter is the input parameters which are interlinked and upon selecting the first parameter’s value the selected value is passed as a filter in the cascaded parameter and accordingly, the second input parameter values will be changed.
For Example: In this blog we will be covering a use case wherein we will create 3 input parameters which are Year, Month and Days. Based on the Year selected the months will get populated, similarly based on the month selected the number of days that month is having data for will get populated.
In order to create a cascading parameter we have to create a file called .efwd which will have the SQL queries for the input parameters. We can create multiple parameters using different map id’s in a single .efwd file. DataMap id is unique id for every single parameter which will map the SQL to the parameter having the same Datamap id.
This .efwd file should be placed in a hi-repository directory in any folder in server.
Steps to create .efwd file is below:
Open any text editor like Notepad++ and provide the data source connection details between the <EFWD> —-</EFWD> tag as shown below.
<EFWD> <DataSources> <Connection id="1" type="global.jdbc"> <globalId>1</globalId> </Connection> </DataSources> </EFWD>
In the above script “global Id” is pointing to the data source connection which is present in Helical Insight application. You can visit the data source page, click on the database icon and in view you will be able to see the data source connection id.
Now define the SQL query for various parameters in between <datamap> </datamap> tag. You can write multiple SQLQuery for multiple input parameters in different data map tags with unique data map ids.
For example, I am creating “Year” parameter so I am writing the query as below in data map tag. The below SQLQuery is fetching the list of years from the database and it is getting passed to $yearName.
<DataMap id="1" connection="1" type="sql"> <Name>yearName</Name> <Query> <![CDATA[ select YEAR(meeting_date) as yearName from meeting_details group by yearName ]]> </Query> </DataMap>
At the dashboard level, on selecting the “Year” parameter “Month” parameter should be cascaded. For that, we need to pass the year parameter in Month select query as shown below. Below we have given a different data map id. Also in SQLQuery we are referring to year (by $yearName from above written SQLQuery) and fetching the data for that specific year.
<DataMap id="2" connection="1" type="sql"> <Name>monthName</Name> <Query> <![CDATA[ select MONTH(meeting_date) as monthName,MONTHNAME(meeting_date) as monthDisplay from meeting_details where YEAR(meeting_date) in (${yearName}) group by monthName, monthDisplay ]]> </Query> <Parameters> <Parameter name="yearName" type="String" default="2015"/> </Parameters> </DataMap>
Inside the parameter tags the input parameters/filters are entered. In the parameter tag, we should mention the name of a parameter, data type and what should be the default value it should have when a user opens the dashboard.
${yearName} is a parameter name which is defined in parameters tag.
In the data map id 3 we are listening to the yearName and MonthName and accordingly fetching the values. The SQLQuery is shown below.
<DataMap id="3" connection="1" type="sql"> <Name>days</Name> <Query> <![CDATA[ select DAY(meeting_date) as days from meeting_details where (YEAR(meeting_date) in (${yearName}) and MONTH(meeting_date) in (${monthName}))group by days ]]> </Query> <Parameters> <Parameter name="yearName" type="String" default="2015"/> <Parameter name="monthName" type="String" default="1"/> </Parameters> </DataMap>
This is all about parameter creation in the .efwd file
Creating the report with the required filters:
Create a report and add input parameters in report level.
When you create an input parameter in report level, the names which are given in the .efwd file, both should match. For example, for “Year” parameter we have given parameter name is “yearName”. So save the parameter with the same name in report level.
Creating a dashboard with cascading filters and reports:-
Open the Helical Insight application and click on Dashboard designer tab.
On the left side you will see “Reports”, click on it and search for a report(which is created above) and drag and drop the report.
Now if you click on the “Variables” you should be able to see the list of filters which this report is having. For example, in this case, it is yearName and monthName.
How to add parameters from a .efwd file in a dashboard:
On the left side you will see “Toggle tool”, click on down arrow button. There you can find the “Object” tool click on that tool it will be extended.
“Object” contains two types of components I.e. Web component and Dashboard component. Choose “dashboard component”
- Double click on the “select” component, a new window will open as shown in the figure below. Basically here we are various options for example, select is dropdown selector, select 2 is a searchable dropdown selector.
- Click on Browse to select the .efwd file from the file browser (which we had created earlier and saved in the “hi-repository” at the backend).
- Enter the data map id which is provided in .efwd file to get the value from the respective SQLQuery. For example, for “Year” input parameter the data map id is ‘1’ to enter the data map id as 1. Whereas for “month” input parameter the data map id is “2”. Whereas for “Days” input parameter the data map id is “3”.
- Provide the display and value column as shown below and save. This is especially useful when we want to show something and pass something else at the backend. For instance, we want to show Employee name on the frontend whereas at the backend we want to pass employee id. In this case, both are the same.
After saving the parameter it will be displayed in Dashboard designer.
In a similar way, you can add “Month” and “days” parameter also.
Setting dashboard variables and listeners:
The date input variable is listening to month and year and is not setting anything. Whereas month input parameter is listening to year and is setting the dashboard variable monthName. The year is not listening to anything but setting the dashboard variable yearName.
Once you have added the input parameter hover over it and you will see a pencil icon, click on it and specify the listeners (as well as input and input value) as well as what dashboard variables it is setting.
Year:
Month:
Days:
Here are the sample EFWD files you can download here EFWD Cascading Input Parameters
In case if you have any queries please get us at support@helicalinsight.com
Thank You