Pentaho Dashboard – Show Message if No Data Available

I do not know how to achieve this in ‘jFreeChartComponent’ or in the CCC (community chart component). My solutions are built with action sequence and that’s where I needed to have this feature.

The concept is simple. After getting the result set from the database through ‘SQLLookupRule’ component, I am checking if the result set length is greater than zero or not. The ‘JavascriptRule’ component is used to determine the length of the result set. And then using the ‘if’ condition I am generating the chart or just a simple string saying ‘No Result Found’.

So my action sequence output will be

<outputs> 
	<image-tag type="string"/>  
	<output type="string"/>
</outputs>

The ‘JavascriptRule’ component to check the result set is simple

<action-definition>
	<component-name>JavascriptRule</component-name>
	<action-type>JavaScript</action-type>
	<action-inputs>
		<queryResult type="result-set" mapping="query_result"/> 
	</action-inputs>
	<action-outputs>
		<noData type="string"/>
	</action-outputs>
	<component-definition>
		<script><![CDATA[noData = "false";
		if(queryResult.getRowCount() == 0){
			noData = "true";
		}]]></script>
	</component-definition>
</action-definition>

So depending upon the result set name my output variable ‘noData’ will be “true” or “false”. And based on the ‘noData’ value I am generating respective output

<actions> 
	<condition><![CDATA[noData == "false"]]></condition>  
	<action-definition> 
		<component-name>ChartComponent</component-name>
		<action-type>Pie Chart</action-type>
		<action-inputs> 
			<chart-data type="result-set" mapping="query_result"/>  
		</action-inputs>
		<action-outputs> 
			<image-tag type="string"/> 
		</action-outputs>
		<component-definition> 
			<chart-attributes> 
			<chart-type>PieChart</chart-type>  
			<title>chartTitle</title>  
			<title-position>top</title-position>  
			<display-labels>false</display-labels>  
			<url-template>javascript:;</url-template>  
			<paramName>label</paramName>  
			<width>350</width>  
			<height>350</height> 
			</chart-attributes> 
		</component-definition> 
	</action-definition>
</actions>
  
<actions>
	<condition><![CDATA[noData == "true"]]></condition>
	<action-definition>
		<component-name>JavascriptRule</component-name>
		<action-type>JavaScript</action-type>
		<action-outputs>
			<output type="string"/>
		</action-outputs>
		<component-definition>
			<script><![CDATA[output = "No Result Found";]]></script>
		</component-definition>
	</action-definition>
</actions>

Pentaho Dashboard – Export Chart Data

To give user an option to export any chart data in csv format is very easy when you use ‘jFreeChartComponent’ to generate the chart. The ‘caption’ option in the chart configuration is used to put a ‘detail’ link beneath every chart. The ‘detail’ link opens up the options to export the chart data and some others.

I didn’t like the  ’jFreeChartComponent’ because of many reasons. If I do not use mdx queries, I have to expose my normal sql queries in javascript while using ‘jFreeChartComponent’. Also I am not sure how I can make some reusable components while I use ‘jFreeChartComponent’.

So I was looking for a good solution where I can give my dashboard users option to download the chart data in csv format while I generate those chart using action sequences (xaction chartcomponent). I could have used the new Pentaho CCC (community chart component), but my solution was too complex to use CCC’s auto generated code. I searched for a solution in Pentaho’s forums but there was no solutions for this. I started one thread there asking for a suggestion, but nobody could come up with any solution. So then I started thinking for a solution by myself.

It was not possible that the same action sequence will be able to provide me the chart as well as the option to generate csv file from the data. So I broke down the solution into three different parts.

1. One action sequence is generating the result set querying the database (chartData.xaction)
2. One action sequence is using ‘SubActionComponent’ and getting the result set from (1) and then generating the chart based on the data using ‘ChartComponent’ (chart.xaction)
3. The other action sequence is again using ‘SubActionComponent’ to get the result set from (1) and then  using ‘JFreeReportComponent’ to generate the csv file. (csvChart.xaction)

So the part of my action sequence generating the chart looks like

<action-definition>
	<component-name>SubActionComponent</component-name>
	<action-type>Render current Data</action-type>
	<action-inputs/>
	<action-outputs>
		<query_result type="result-set" mapping="query_result"/>
	</action-outputs>
	<component-definition>
		<solution><![CDATA[app]]></solution>
		<path><![CDATA[dashboards/SingleChart]]></path>
		<action><![CDATA[currentData.xaction]]></action>
	</component-definition>
</action-definition>

<action-definition>
	<component-name>ChartComponent</component-name>
	<action-type>Pie Chart</action-type>
	<action-inputs>
		<chart-data type="result-set" mapping="query_result"/>
	</action-inputs>
	<action-outputs>
		<image-tag type="string"/>
	</action-outputs>
	<component-definition>
		<chart-attributes>
			<chart-type>PieChart</chart-type>
			<title>chartTitle</title>
			<title-position>top</title-position>
			<display-labels>false</display-labels>
			<url-template>javascript:;</url-template>
			<paramName>label</paramName>
			<width>350</width>
			<height>350</height>
		</chart-attributes>
	</component-definition>
</action-definition>

Like the same way the part of the action sequence  generating the csv looks like

<action-definition>
	<component-name>SubActionComponent</component-name>
	<action-type>Render current Data</action-type>
	<action-inputs/>
	<action-outputs>
		<query_result type="result-set" mapping="query_result"/>
	</action-outputs>
	<component-definition>
		<solution><![CDATA[app]]></solution>
		<path><![CDATA[dashboards/SingleChart]]></path>
		<action><![CDATA[currentData.xaction]]></action>
	</component-definition>
</action-definition>

<action-definition>
	<component-name>JFreeReportComponent</component-name>
	<action-type>Pentaho Report</action-type>
	<action-inputs>
		<data type="result-set" mapping="query_results"/>
	</action-inputs>
	<action-resources>
		<report-definition type="resource"/>
	</action-resources>
	<action-outputs>
		<report type="content"/>
	</action-outputs>
	<component-definition>
		<output-type>csv</output-type>
	</component-definition>
</action-definition>

So as the action sequences are ready I have to call them in my template file. I am here using ‘ExecuteXaction’ component to call the csvChart.xaction, but before that rendering the chart from the template file is very straight forward.

components[components.length] = {
	name : "currentChart",
	type : "XactionComponent",
	solution : solution,
	path : path,
	action : "chart.xaction",
	listeners : [],
	parameters : [],
	htmlObject : "renderedChart",
	executeAtStart : true,
	postExecution: function(){
		Dashboards.fireChange(csvListner, "csvListner");
	}
};

and the ‘ExecuteXaction’ is listening the ‘csvListner’ and fires the event as soon as the chart is loaded.

components[components.length] = {
	name : "chartCsv",
	type : "ExecuteXaction",
	solution : solution,
	path : path,
	action : "csvCchart.xaction",
	listeners : [currentCsvListner],
	parameters : [],
	htmlObject : "downLoadData",
	label: "Download Data",
	executeAtStart : false
};

The ‘ExecuteXaction’ renders a button saying ‘Download Data’ and once clicked the csv download option comes and asks user to save the file.