In this blog, we will discuss about how we can implement cyclic (One to Many) cascading between multiple parameters in Helical Insight. In this blog we have covered how to do it in the versions upto version 4.1. This kind of filter is called as interactive filtering in PowerBI. This allows the value of one filter to respond to other filters, the other filter can also respond to first filter.
Pre-requisite :
– Helical Insight application version 4
– Knowledge of how basic cascading filtering works in Helical Insight version 4. For understanding the same you can refer our detailed documents:
- https://www.helicalinsight.com/creating-cascading-parameter-helical-insight/
- https://www.helicalinsight.com/implement-cascading-input-parameters-helical-insight/
Follow below steps to implement cyclic cascading between parameters:
- Create adhoc report in your Helical Insight application with filters / parameters which you want to apply. For reference, we have created sample tabular report with multiple columns and 3 different filters / parameters.
- Create a file with extension efwd and store this file into any folder of your hi-repository. In this file we need to provide SQLs of our input parameters. For reference, in our example we have created 3 input parameters destination, source and employee_name. SQLs for listing this parameters will be part of efwd file. Below we have taken 1 reference of employee_name parameter SQL :
<DataMap id="1" connection="1" type="sql" >
<Name>Query for EmployeeName</Name>
<Query>
<![CDATA[
select
"HIUSER"."employee_details"."employee_name" as "employee_name"
from
"HIUSER"."travel_details"
inner join "HIUSER"."employee_details" on ("HIUSER"."employee_details"."employee_id" = "HIUSER"."travel_details"."travelled_by")
where
(("HIUSER"."travel_details"."destination" in (${destination}) OR ('_all_') in (${destination})) AND ("HIUSER"."travel_details"."source" in (${source}) OR ('_all_') in (${source})))
group by
"HIUSER"."employee_details"."employee_name"
]]>
</Query>
<Parameters>
<Parameter name="source" type="Collection" default="'_all_'"/>
<Parameter name="destination" type="Collection" default="'_all_'"/>
</Parameters>
</DataMap>
Above mentioned SQL is for listing employee_name. Within this SQL we have passed other 2 parameters of source and destination in the where clause. This we have done in order to achieve cascading. Once any value gets changed in source and destination filters, then those values will be passed as filter to this employee_name sql and accordingly the list will be generated.
Same thing we are going to do for other 2 parameters of source and destination as well.
– In listing SQL of source -> we are going to pass employee_name and destination as filter.
– In listing SQL of destination -> we are going to pass employee_name and source as filter.
- Once we have created efwd file with all parameter SQLs then we can start creating our Dashboard. In order to create dashboard from HI version 4 open Dashboard Designer, create select OR select 2 kind of parameter from front end UI of dashboard designer.
While creating any parameter make sure that you are setting variable of that parameter as well as listening to our input parameters and taking those values in input section.
For example : We are taking reference of employee_name input parameter. This parameter will set value of employee_name variable but it will listen to source and destination parameters and takes the values of source and destination as input. Refer screenshot below :
Similarly create other 2 parameter of source and destination.
For source -> It will set variable of source and listen to employee_name and destination as well take values as input of employee_name and destination.
For destination -> It will set variable of destination and listen to employee_name and source as well take values as input of employee_name and source.
This way it will become cyclic cascading where every input parameter will change other parameters and the same parameter will get change on changing of other parameters.
