Creating Cascading Parameter in Helical Insight

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.

Introduction:

Cascading Parameter are the parameters which are interlinked and upon selecting the main parameter’s value the selected value is passed as a filter in the cascaded parameter and accordingly values will be changed.

Concept : Generally the parameters data comes from the adhoc data source so we do not have any option to write query to implement cascading. In order to get the data by writing sql query, we have an option called “efwd” file. In this file we can fetch the data of parameters by writing query associated with map id . We can create parameters and pass these parameters in the query where clause. We will explain step by step to make you understand better.

Steps to implement cascading parameters :

1. Create a sample report with at least two filters , add the report to the dashboard
2. Add the filters in the dashboard
3. The sample Dashboard view will be look like below
4. we need to create efwd file in order to apply cascading functionality

Create the file with extension : efwd
Cascading.efwd

 

<EFWD>
    <DataSources>
        <Connection id="1" type="global.jdbc">
			<globalId>4</globalId> // provide the global id we can get from data sources page in Helilcal Insight Application
		</Connection>
    </DataSources>
	
    <DataMaps>
        <DataMap id="1" connection="1" type="sql" >
            <Name>sample_query1</Name>
            <Query>
                <![CDATA[
                    select distinct travel_type from travel_details
                ]]>
            </Query>
			
			
         </DataMap>
		 <DataMap id="2" connection="1" type="sql" >
            <Name>sample_query1</Name>
            <Query>
                <![CDATA[
                    select distinct travel_medium from travel_details where travel_type in (${travel_type})
                ]]>
            </Query>
			<Parameters>
                <Parameter name="travel_type" type="Collection" default="''"/>
				
            </Parameters>
			
         </DataMap>
		
    </DataMaps>
</EFWD>

Decscription of the code : The code contains the data source connection information and sql queries to fetch data of the parameters to be cascaded. In the first query we are fetching the parameter data. In the second query we are fetching parameter data by providing first parameter selected value.

5. Created a folder with any name in the below application installation path :

…hi\hi-repository\Cascading\cascading efwd

And place the efwd file in that folder , in this case it will be like this :

…hi\hi-repository\Cascading\cascading efwd

6. Open the dashboard in edit mode

7. By default the parameters drop down data is coming from adhoc data source. So need to get that data from efwd.

8. Edit the first parameter and configure things as shown below :

Disable the adhoc data source browse the folder where efwd file is present and select that folder. Provide the proper map id’s for the parameters and if the parameter is multi select donot enable the multi select toggle if you create multi select filters.

Note : efwd file will not appear while browsing just select the folder where the efwd file is present.

9. Edit the second parameter and configure things as shown below :

10. Save all the configurations and save the dashboard.

11. Dashboard open mode Test Case 1 :

12. Dashboard open mode Test Case 2 : ( Second filter data is changed based on first parameter selected values)

Note :  We can implement cascading parameters for multile parameters also. We need to update efwd file and dashboard filter edit configuration as per parameters accordingly

Thank You
Anjaneyulu
Helical IT Solutions Pvt Ltd