How to compare values between two columns?

0

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

lee.owens's picture
Joined: Nov 15 2016 - 8:46am
Last seen: 3 days 23 hours ago

Hi, 

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

lee.owens - 2 years 4 months ago

5 Answers:

0

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 union
select 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>

reportdev's picture
1226
Joined: Oct 12 2015 - 12:05pm
Last seen: 2 months 3 weeks ago
1

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! :)

erickdelsocorro's picture
Joined: Feb 21 2017 - 4:50pm
Last seen: 2 years 4 months ago
0

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}

shertage's picture
1932
Joined: Sep 26 2006 - 8:06pm
Last seen: 3 days 20 hours ago
0

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

Thanks

lee.owens's picture
Joined: Nov 15 2016 - 8:46am
Last seen: 3 days 23 hours ago
0

 

Hi Erickdel's


That's worked perfectly. Thanks for your help

 


erickdelsocorro


lee.owens's picture
Joined: Nov 15 2016 - 8:46am
Last seen: 3 days 23 hours ago
Feedback