Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Tuesday, April 17, 2018

Tip: Conditional colors of bars in HTML(high charts) column or bar graphs without category values (or) SeriesItemProperty usage to display conditional colors of bars in Jasper reports

Hi folks,

Did you ever had a need of displaying column chart without having category from database ?
Did you ever want to have 4 fields of query output to be plotted as bars ?

Here we go.!
Let's say the query output format to be plotted as bars as follows

Query:
SELECT 100 AS Measure1, 200 AS Measure2, 300 AS Measure3, 400 AS Measure4 FROM customer limit 1

Output:
Measure1   Measure2   Measure3   Measure4
100               200              300              400


Usually, in jasper column graphs to get bars we write query in such a way that the output should come as below

field1      field2
category 100
category 200
category 300
category 400
and it gets the same color (by default blue incase of highcharts bars) as it has category field. 

Incase of only measures, we add them as individual measures in the chart Measures section as shown in below image. 

and when we preview the graph it would get different colors for each of the bar as the graph would plotted based on series although we don't have any series or for empty series.

In this cases, if we want to apply conditional colors on Measures, it can be done in the Advanced properties of each of the measures.

for instance, to apply conditional color on Measure1,
1) Double click on Measure1
2) In the popped up window select "Advanced properties".
3) Click on "Add" button
4) Select "SeriesItemProperty" in the dropdown of "Contributer".
5)  Give "color" as name in "Property Name"
6) Now, observe there are 4 ways like "Use constant Value", "Use bucket value", "Use Measure Value" and "Use Expression".
  As we don't have to deal with categories, we ignore "Use Bucket Value" property section. In usual cases of conditional colors we work with it. And the constant value and Use Measure is not the scope. Ultimately we need to use "Use Expression" property. Let's click on it to open it's expression editor and write below code of "Measure1"

($F{measure1}>=00  && $F{measure1} <=100 )? "red": ($F{measure1}>=101 &&  $F{measure1} <=200 ) ? "yellow":($F{measure1}>=201 &&  $F{measure1} <=300 ) ?"green":"blue"

Repeat the same steps of rest of the measures i.e., for Measure2, Measure3, Measure4. 

Based on the values range we provide different colors as shown in above image. In this case, I used 4 columns to plot and whenever the values meets the range provided each of the bar display either "red", "yellow", "green" or "blue". 

Note that in the sample output below I made the query to get 4 different colors for 4 bars but if you have the same value for any other measures it would get the same color. (look at case2 or case3 sample outputs below)

Sample output: case 1
SELECT 100 AS Measure1, 200 AS Measure2, 300 AS Measure3, 400 AS Measure4 FROM customer limit 1


Sample output : case 2 
SELECT 100 AS Measure1, 200 AS Measure2, 300 AS Measure3, 100 AS Measure4 FROM customer limit 1
Sample output : case 3
SELECT 300 AS Measure1, 200 AS Measure2, 300 AS Measure3, 300 AS Measure4 FROM customer limit 1
I hope this helps someone in community.!

- Sadakar Pochampalli

JRXML (Supports from 6.4.2 Professional version)
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.2.final using JasperReports Library version 6.4.1  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Custom colors for bars" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="c6392b13-e801-4f23-97b3-c1721031bd19">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmartlatha"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/>
<property name="ireport.jasperserver.user" value="superuser"/>
<property name="ireport.jasperserver.report.resource" value="/EarthlySystems/Custom_colors_for_bars_files/main_jrxml"/>
<property name="ireport.jasperserver.reportUnit" value="/EarthlySystems/Custom_colors_for_bars"/>
<queryString>
<![CDATA[SELECT 100 AS Measure1, 200 AS Measure2, 300 AS Measure3, 400 AS Measure4 FROM customer limit 1]]>
</queryString>
<field name="measure1" class="java.lang.Integer"/>
<field name="measure2" class="java.lang.Integer"/>
<field name="measure3" class="java.lang.Integer"/>
<field name="measure4" class="java.lang.Integer"/>
<background>
<band splitType="Stretch"/>
</background>
<summary>
<band height="270" splitType="Stretch">
<componentElement>
<reportElement x="0" y="0" width="555" height="270" uuid="9d82f52d-8919-49a1-b898-d54f4c9b74f1">
<property name="com.jaspersoft.jasperreports.highcharts.interactive" value="false"/>
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="Column">
<hc:chartSetting name="default">
<hc:chartProperty name="title.text" value=""/>
<hc:chartProperty name="credits.enabled" value="false"/>
<hc:chartProperty name="credits.href" value=""/>
<hc:chartProperty name="credits.text" value=""/>
<hc:chartProperty name="yAxis.title.text" value=""/>
<hc:chartProperty name="chart.zoomType" value="xy"/>
<hc:chartProperty name="legend.enabled_customSimpleMode" value="true"/>
<hc:chartProperty name="legend.enabled">
<hc:propertyExpression><![CDATA[false]]></hc:propertyExpression>
</hc:chartProperty>
<hc:chartProperty name="yAxis.gridLineWidth" value="0"/>
<hc:chartProperty name="yAxis.visible" value="false"/>
<hc:chartProperty name="yAxis.labels.enabled" value="false"/>
<hc:chartProperty name="yAxis.labels.format" value="{}"/>
<hc:chartProperty name="chart.backgroundColor" value="#FFFFFF"/>
<hc:chartProperty name="plotOptions.column.pointPadding" value="0.3"/>
<hc:chartProperty name="xAxis.labels.enabled" value="false"/>
<hc:chartProperty name="plotOptions.series.shadow_customSimpleMode" value="true"/>
<hc:chartProperty name="chart.spacingLeft_customSimpleMode" value="true"/>
<hc:chartProperty name="chart.spacingLeft">
<hc:propertyExpression><![CDATA[2]]></hc:propertyExpression>
</hc:chartProperty>
<hc:chartProperty name="chart.spacingRight_customSimpleMode" value="true"/>
<hc:chartProperty name="chart.spacingRight">
<hc:propertyExpression><![CDATA[2]]></hc:propertyExpression>
</hc:chartProperty>
<hc:chartProperty name="colors"/>
<hc:chartProperty name="xAxis.gridLineWidth" value="0"/>
<hc:chartProperty name="xAxis.lineWidth" value="0"/>
<hc:chartProperty name="xAxis.visible" value="false"/>
<hc:chartProperty name="yAxis.gridZIndex" value="0"/>
<hc:chartProperty name="yAxis.minorGridLineWidth" value="0"/>
<hc:chartProperty name="yAxis.lineWidth" value="0"/>
<hc:chartProperty name="plotOptions.column.borderColor">
<hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
</hc:chartProperty>
<hc:chartProperty name="xAxis.gridLineColor">
<hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
</hc:chartProperty>
<hc:chartProperty name="xAxis.lineColor" value="&quot;&quot;"/>
<hc:chartProperty name="xAxis.minorGridLineWidth" value="0"/>
<hc:chartProperty name="xAxis.minorTickLength" value="0"/>
<hc:chartProperty name="xAxis.tickWidth" value="0"/>
<hc:chartProperty name="xAxis.tickColor" value="&quot;&quot;"/>
<hc:chartProperty name="xAxis.labels.format" value="{}"/>
</hc:chartSetting>
<multiAxisData>
<multiAxisDataset>
<dataset resetType="None"/>
</multiAxisDataset>
<dataAxis axis="Rows">
<axisLevel name="EmptyCategory">
<labelExpression><![CDATA["Level Label expression"]]></labelExpression>
<axisLevelBucket class="java.lang.Comparable">
<bucketExpression><![CDATA["EmptyCategory"]]></bucketExpression>
</axisLevelBucket>
</axisLevel>
</dataAxis>
<dataAxis axis="Columns"/>
<multiAxisMeasure name="Measure1" class="java.lang.Number" calculation="Nothing">
<labelExpression><![CDATA["Measure1"]]></labelExpression>
<valueExpression><![CDATA[$F{measure1}]]></valueExpression>
</multiAxisMeasure>
<multiAxisMeasure name="Measure2" class="java.lang.Number" calculation="Nothing">
<labelExpression><![CDATA["Measure2"]]></labelExpression>
<valueExpression><![CDATA[$F{measure2}]]></valueExpression>
</multiAxisMeasure>
<multiAxisMeasure name="Measure3" class="java.lang.Number" calculation="Nothing">
<labelExpression><![CDATA["Measure3"]]></labelExpression>
<valueExpression><![CDATA[$F{measure3}]]></valueExpression>
</multiAxisMeasure>
<multiAxisMeasure name="Measure4" class="java.lang.Number" calculation="Nothing">
<labelExpression><![CDATA["Measure4"]]></labelExpression>
<valueExpression><![CDATA[$F{measure4}]]></valueExpression>
</multiAxisMeasure>
</multiAxisData>
<hc:series name="Measure1">
<hc:contributor name="SeriesItemProperty">
<hc:contributorProperty name="color" valueType="Expression">
<hc:valueExpression><![CDATA[($F{measure1}>=00  && $F{measure1} <=100 )? "red": ($F{measure1}>=101 &&  $F{measure1} <=200 ) ? "yellow":($F{measure1}>=201 &&  $F{measure1} <=300 ) ?"green":"blue"]]></hc:valueExpression>
</hc:contributorProperty>
</hc:contributor>
</hc:series>
<hc:series name="Measure2">
<hc:contributor name="SeriesItemProperty">
<hc:contributorProperty name="color" valueType="Expression">
<hc:valueExpression><![CDATA[($F{measure2}>=00  && $F{measure2} <=100 )? "red": ($F{measure2}>=101 &&  $F{measure2} <=200 ) ? "yellow":($F{measure2}>=201 &&  $F{measure2} <=300 ) ?"green":"blue"]]></hc:valueExpression>
</hc:contributorProperty>
</hc:contributor>
</hc:series>
<hc:series name="Measure3">
<hc:contributor name="SeriesItemProperty">
<hc:contributorProperty name="color" valueType="Expression">
<hc:valueExpression><![CDATA[($F{measure3}>=00  && $F{measure3} <=100 )? "red": ($F{measure3}>=101 &&  $F{measure3} <=200 ) ? "yellow":($F{measure3}>=201 &&  $F{measure3} <=300 ) ?"green":"blue"]]></hc:valueExpression>
</hc:contributorProperty>
</hc:contributor>
</hc:series>
<hc:series name="Measure4">
<hc:contributor name="SeriesItemProperty">
<hc:contributorProperty name="color" valueType="Expression">
<hc:valueExpression><![CDATA[($F{measure4}>=00  && $F{measure4} <=100 )? "red": ($F{measure4}>=101 &&  $F{measure4} <=200 ) ? "yellow":($F{measure4}>=201 &&  $F{measure4} <=300 ) ?"green":"blue"]]></hc:valueExpression>
</hc:contributorProperty>
</hc:contributor>
</hc:series>
</hc:chart>
</componentElement>
</band>
</summary>
</jasperReport>