Implementing Inter panel Communication in Community Reporting of Helical Insight

Introduction: In this blog we are going to explain how to implement inter panel communication between tabular report and a pie chart in the Dashboard.

For using Inter-panel communication functionality, user needs to create two reports i.e. parent report and child report. Here parent report is Tabular report and Child report is Pie chart.

Parent report: Parent report: Primary report on which we are going to click and pass on the values to the filter of child report.

Child report: Secondary report which will reflect the data according to the click on the parent report columns. This is the child report with the filter, the value to this filter is being passed based on what we have clicked on the parent report.

Note: Child report should have a filter which would be the input parameter to be passed in parent report. It is always better to create the Child report first.

Please make sure you have gone through the blog “Introduction to Community Reporting Interface”

Steps to create pie chart report with single select parameters:

  1. Data Source
  2. Dashboard Layout
  3. Parameter
  4. Report

1. Data source :

Create data source connection.

Here we are using connection type : plain jdbc

Data Source

In the configuration of data source, provide the details as shown below.

<driver>com.mysql.jdbc.Driver</driver>   // sql driver 
<url>jdbc:mysql://192.168.2.51:3306/Travel_Data </url>  // jdbc url
<user>hiuser</user>  // database username
<pass>hiuser</pass>  // database password

Note : Based on your requirement you can connect to other datasources, rest of the steps mentioned below are the same.

2. Dashboard Layout :

In the dashboard layout we basically specify the layout of the report, dashboards, input parameters which we are creating. All the divs are specified here within which they get rendered.

Here we are creating 2 divs: Tabular, Pie_Chart

Report divs are : Tabular, Pie_Chart

When you click on dashboard layout a you will see the following:

Dashboard Layout

In the above screen shot we find two options (left side)

HTML and CSS .

If you click on HTML/CSS the place holder for respective component will be displayed and highlighted in the Dashboard layout panel.

We can place the code related to dashboard layout in HTML and styling in CSS.

Dashboard layout:

HTML :

<div class="col-md-6" id="Tabular"></div>
<div class="col-md-6" id="Pie_Chart"></div>

CSS:

In CSS place holder we can add the css related to pie chart customization as well as report customization like back-ground color , report border , border radius, color etc.

3. Parameters :

In the inter panel communication, we pass single value at a time (when we click on the row in the parent report). The parameter must be single select. We should create input parameter by clicking on the + button of parameters section but we should not provide any configuration and SQL because we are passing the value to this parameter when we click on the parent report row.

In this sample we are creating one input parameter of the type “single select”

See the below screenshot, we are creating one parameter.

Note : Generally when we click on add(+) parameter , the parameter is created with default name “parameter1”. We can rename the parameters name with the help of edit icon. In the configuration place holder, by default, configuration code is generated. In this case we should delete all the configurations, then click on apply or control+s.



A. Journey_Type : (single select)

Choose connection as “connection1”(previously created in data sources)

Here “Client” is a Single-select input control, so we need to choose input type as “String” as shown in below image. By default it is always “String”.

Note: For all multi select parameters we should select “Collection” irrespective of parameter datatype. If it is single select parameter we choose the type based on datatype of parameters.

Configuration : Should not provide any configuration.

SQL : Should not provide any sql query.

4. Report :

We can configure different visualizations to render in different divs in the dashboard layout.

When click on add report button, the following layout will appear:

In the left panel we can see report, SQL, Visualization related options.

Click on configure, on the right side we can see the place holders for report configuration, SQL, Visualization. Place the respective code and then click on apply.

Tabular Report configuration:

We need to choose the connection as “connection1”

Configuration: 

var dashboard= Dashboard;
dashboard.resetAll();
dashboard.setVariable('Client','Envision')
var Tabular= {
    name: "Tabular",
    type:"chart",
    vf : {
      id: "1",
      file: "__efwvf_name__"
    },

htmlElementId : "#Tabular",
    executeAtStart: true
 };

SQL :

	select 
	`meeting_details`.`client_name` as `Client`,
	sum(`TravelDetails`.`travel_cost`) as `Travel_Cost`
from `TravelDetails` 
	inner join `employee_details` on (`employee_details`.`employee_id` = `TravelDetails`.`Travelled_by`) 
	inner join `meeting_details` on (`employee_details`.`EMPLOYEE_ID` = `meeting_details`.`meeting_by`) 
group by
	`Client`

Visualization: Here we are creating custom tabular report. So we need to provide the complete script which is responsible for the table generation. In the script we are using external files : jquery.dataTables.min.js , jquery.dataTables.css , dataTables.jqueryui.css. Please place them( from the back end) in the report folder in the hi-repository . Give the files path in the script where it is required.

Download the External Files here: External Files

Code : 

var link = document.createElement("link");
		  link.href= "getExternalResource.html?path=1562042844398/jquery.dataTables.css"; //put path where you save the file 
		  link.rel = "stylesheet";
	
			var link2 = document.createElement("link");
			link.href= "getExternalResource.html?path=1562042844398/dataTables.jqueryui.css"; //put path where you save the file
			link.rel = "stylesheet";
		
		  var script = document.createElement("script");
				if(window.DashboardGlobals)
					script.src = window.DashboardGlobals.baseUrl+"/getExternalResource.html?path=1562042844398/jquery.dataTables.min.js"; //put path where you save the file				
				else
					script.src = "getExternalResource.html?path=1562042844398/jquery.dataTables.min.js"; //put path where you save the file

				document.getElementsByTagName("head")[0].appendChild(script);
                                var column_index=[];
								var flag;
								function tabulate(elem, data, columns) {	
								var table = d3.select(elem).append("table")	
								.attr("class"," table display compact width:100%;cellspacing:1 ")
								.attr("id","table")
								thead = table.append("thead"),
								tbody = table.append("tbody");


								// append the header row
								thead.append("tr")
								.selectAll("th")
								.data(columns)
								.enter()
								.append("th")
								.text(function(column) { return column; })
								.attr('class', function(d, i){ return "colH_" + i; })
								.style('background-color','#6c727d')
								.style('font-size','15px')
								.style('color','white')
								.style('border','5px solid white')
								.style('text-align','center')
								.style('padding-bottom','5px')
								.style('@media print','display:none')
								.style('padding-left','25px');


								// create a row for each object in the data
								var rows = tbody.selectAll("tr")
								.data(data)
								.enter()
								.append("tr");

								// create a cell in each row for each column
								var cells = rows.selectAll("td")
								.data(function(row) {
								return columns.map(function(column) {
								return {column: column, value: row[column]};
								});
								})
								.enter()
								.append("td")
								.text(function(d) { return d.value; })
								.attr('class', function(d, i){ return "col_" + i; })
								.attr('align', 'center')
								return table;
								}	

								// render the table
								var subjectTable = tabulate( '#chart_1', data, Object.keys(data[0]));	
								
							  script.onload = function(){	

								$(document).ready(function() {
								var table = $('#table').DataTable({"deferRender": true,
								"pagingType": "full_numbers",
								"lengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]],
								"paging": false,
								"jQueryUI": true,
								"ordering": true,
								"info": true,
								"language": {
								"decimal": ",",
								"thousands": ".",
								"lengthMenu": "Display MENU Records per page",
								"zeroRecords": "Nothing Found - Sorry",
								"info": "Showing page PAGE of _PAGES_",
								"infoEmpty": "No Records Available",
								"infoFiltered": "(filtered from MAX total records)"
								},
								
						responsive: true,
								"fnRowCallback": function( nRow, adata, iDisplayIndex ) {
											/* Append the grade to the default row class name */
											// console.log(nRow,adata,iDisplayIndex);
											if(adata[1]>= 1000 && adata[1]<= 1000000 ){
												$(nRow).find('td:eq(1)').css('background-color', '#2eca9c');
												$(nRow).find('td:eq(1)').css('color', 'white');
											}
											else if(adata[1]>= 1000000 && adata[1]< 1500000 ){
												$(nRow).find('td:eq(1)').css('background-color', '#fcda52');
												$(nRow).find('td:eq(1)').css('color', '#333333');
											}
											else if(adata[1]> 1500000 ){
												$(nRow).find('td:eq(1)').css('background-color', 'white');
												$(nRow).find('td:eq(1)').css('color', '#333333');
											}
										}
								});

// var rows = document.getElementsByTagName("tr");
    // for (var i = 0; i < rows.length; i++)
    // {
        // rows[i].onclick = function(d,i) {
           // console.log(this, d.name, i);
		   // dashboard.setVariable('Client',d.name)
		
        // };
    // }
// when we click on table row client name , the clicked row value will be passed as parameter to next child report
var tbl = document.getElementById("table");
        if (tbl != null) {
            for (var i = 0; i < tbl.rows.length; i++) {
                for (var j = 0; j < tbl.rows[i].cells.length; j++)
                    tbl.rows[i].cells[j].onclick = function () { getval(this); };
            }
        }
        function getval(cel) {
            console.log(cel.innerHTML);
			dashboard.setVariable('Client',cel.innerHTML)
        }	
																
																
													

								// $(document).off('click', '**');	
								
								});	
					
								
		  }

Note: After placing the configuration, SQL, Visualization then click on apply or (control+s)

Pie chart Configuration : We should provide the listeners, requestParameters as “Client”. when we click on parent report , the row value is passed as filter to the child report.

Configuration :

var Pie_Chart = {
 name: "Pie_Chart",
    type:"chart",
	  listeners:["Client"],
	   requestParameters :{
     Client : "Client"
	 },
    vf : {
      id: "2",
      file: "__efwvf_name__"
    },
    htmlElementId : "#Pie_Chart",
    executeAtStart: true
  };

SQL :

select 
	`TravelDetails`.`Travel_medium` as `Travel_medium`,
	sum(`TravelDetails`.`travel_cost`) as `Travel_cost`
from
	`TravelDetails`
	inner join `employee_details` on (`employee_details`.`employee_id` = `TravelDetails`.`Travelled_by`) 
	inner join `meeting_details` on (`employee_details`.`employee_id` = `meeting_details`.`meeting_by`) 
where
	`meeting_details`.`client_name` = ${Client}
group by
	`TravelDetails`.`Travel_medium`

Visualization : 

if (data.length == 0) {
					$('#chart_2').html("<div><h2 style='text-align:center;color:#927333;'>No Data To Display</h2></div>");
					  } else {
					var array1=[];
					for (var i = 0; i < data.length; i++) {
					var array2=[]; 
					for (var prop in data[i]) { 

					array2.push(data[i][prop]);
					}
					array1[i] = array2;
					}
					var chart = c3.generate({
					bindto: '#chart_2',
					data: {
					columns: array1,
					type : 'pie'
					},
					tooltip: {
					show: true
					 },
					 legend:{
					show: true
					 }
					}); 
					}

Note: After placing the configuration, SQL, Visualization then click on apply or (control+s)

After completing all the steps save the community edition report.

In the back end server location the following files will be generated:

  1. Efw (report view in the front end)
  2. Efwce (editable file in the front end)
  3. Efwvf
  4. Html
  5. Efwd

In the front end file browser there are two files which can be seen:

When we double click on the report view file (with the extension efw) report opens like below. The other file with the extension EFWCE can be used to edit the created report/dashboard again.

DashBoard with Inter Panel Communication Report View : (screenshot1)



Screenshot2 :

For more details on EFWCE reporting refer the documentation :

EFWCE method of reporting in Helical Insight

For further assistance, kindly contact us on support@helicalinsight.com or post your queries at Helical Insight Forum

You May Also Like

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