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”

  1. 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.
  2. 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).
  3. 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”.
  4. 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

Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered