How to compare values between two columns?

[lee.owens]

Hi Forum,

Is it possible to format values conditionally when comparing data between 2 columns? 

For example, IF Column A > Column B THEN Column C = "Yellow"

It appears as if you can only conditionally format data with static values - Column A > 20 THEN "Red" for example.

Thanks

[reportdev]

you can use conditional styles in jasper studio. sample below

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  --><!-- 2017-02-21T14:05:01 --><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="compare" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="358d8010-5f1c-4bdb-a0fe-41ff59b7857b">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <style name="Style1">        <conditionalStyle>            <conditionExpression><![CDATA[$F{ID}.compareTo($F{ID2})  > 0]]></conditionExpression>            <style mode="Opaque" forecolor="#FF0400" backcolor="#FFFF00"/>        </conditionalStyle>    </style>    <queryString>        <![CDATA[select 1 as ID, 2 as ID2 from dual unionselect 3 as ID, 2 as ID2 from dual]]>    </queryString>    <field name="ID" class="java.math.BigDecimal"/>    <field name="ID2" class="java.math.BigDecimal"/>    <background>        <band splitType="Stretch"/>    </background>    <columnHeader>        <band height="21" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="100" height="20" uuid="d633e915-b545-499f-9b6b-fbca0a6f2ecd"/>                <text><![CDATA[A]]></text>            </staticText>            <staticText>                <reportElement x="100" y="0" width="100" height="20" uuid="da3670cb-f9c3-4066-9fdb-4c3d3387f598"/>                <text><![CDATA[b]]></text>            </staticText>            <staticText>                <reportElement x="200" y="1" width="100" height="20" uuid="1aea2acb-377c-4334-a9fe-c253ea1931fd"/>                <text><![CDATA[Compare]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="20" splitType="Stretch">            <textField>                <reportElement x="0" y="0" width="100" height="20" uuid="429e73e7-4afb-4ac7-81c6-d650fe9d5bc6">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                </reportElement>                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="100" y="0" width="100" height="20" uuid="3af6bf71-971d-4af5-8895-6d9e79224fa9"/>                <textFieldExpression><![CDATA[$F{ID2}]]></textFieldExpression>            </textField>            <textField>                <reportElement style="Style1" x="200" y="0" width="100" height="20" uuid="775435e9-0181-4c8d-bd89-9d0028722abd"/>                <textFieldExpression><![CDATA["Test"]]></textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]

 

 

[erickdelsocorro]

Yes, it is possible.  To achieve this, follow the steps on Jaspersoft Studio:

1. Create New Style. On the outline window, right-click style and select Create Style. Afterwards, Right-click the newly created Style then select Create Conditional Style.

2. Change Backcolor Setting. On properties window, under appearance. Select the appropriate back color.

3. Set the condition. Ensure <NO CONDITION SET> under your new style is selected. Back to the properties window, under style tab, place the condition on Conditional Expression field. i.e: $F{ROW_POSITION}.compareTo( $F{MAP_TYPE}) >  0

4. Set TextField Style. Select the text field you want to apply the style. On the properties window, under Advance tab, On Style setting which is under the graphic property, type in the name of the custom style you made.

Let me know if this works for you! :)

[szaharia]

Hi,

Another way to set dynamic styles for report elements is to make use of style feature properties at element level. More on these properties you can find out here:

http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.style.{style_property_suffix}

[lee.owens]

Hi, 

I think you're talking about Excel... I'm trying to achieve the same result, but in Jaspersoft

[lee.owens]

Thanks for the answers, guys. I'll try them out and see what works best.

Thanks

[lee.owens]

 

Hi Erickdel's

That's worked perfectly. Thanks for your help

 

erickdelsocorro