Jump to content
Changes to the Jaspersoft community edition download ×

SQL data rounding


chrisj.msac

Recommended Posts

Hello,

I am selecting data from multiple tables and ordering the data.

 

I need to apply certain rounding rules to the information returned based on another dataset. I relaize i need to use a case statement for this.

 

My question is how I would go about this?

 

An example of the returned data is as follows:

column 1  |  Coulmn 2 |

milk               5.5

jam                2.1

 

I want to round the jam to the nearest whole number and keep Milk at it's same number.

 

Thanks for any help.

 

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Try doing this way

  1. Create an rounding_id column in your SQL
  2. use jasper expressions to change the rounding mode and display the value

Ex: Since the bigDecimal.setScale does not allow 0, I had to override the normal expression and change it to a string.

$F{ROUNDING_MODE}.intValue() <= 0 ? $F{COLUMN2}.setScale(0,BigDecimal.ROUND_HALF_UP).toPlainString() : 
$F{COLUMN2}.setScale($F{ROUNDING_MODE}.intValue() )

<?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="sample" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="6d69f6ac-a0ee-4101-a303-f6fa69e29f54">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <queryString>        <![CDATA[select column1, column2, rounding_mode from (select 'milk' as column1, 5.5 as column2, 0 as rounding_mode from dualunion allselect 'jam' as column1, 2.1 as column2, 2 as rounding_mode from dualunion allselect 'bread' as column1, 2.6 as column2, 2 as rounding_mode from dualunion allselect 'coffee' as column1, 8.985 as column2, 3 as rounding_mode from dual)]]>    </queryString>    <field name="COLUMN1" class="java.lang.String"/>    <field name="COLUMN2" class="java.math.BigDecimal"/>    <field name="ROUNDING_MODE" class="java.math.BigDecimal"/>    <background>        <band splitType="Stretch"/>    </background>    <columnHeader>        <band height="30" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="80" height="30" uuid="ba03c4fe-513a-471f-9011-7567c7624006"/>                <text><![CDATA[COLUMN1]]></text>            </staticText>            <staticText>                <reportElement x="80" y="0" width="100" height="30" uuid="46f4a175-c695-49bd-ab1e-5e66741c5f0b"/>                <text><![CDATA[COLUMN2]]></text>            </staticText>            <staticText>                <reportElement x="180" y="0" width="100" height="30" uuid="6d7211c9-9820-49a3-ab46-56d2715c8648"/>                <text><![CDATA[ROUNDING_MODE]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="35" splitType="Stretch">            <textField>                <reportElement x="0" y="0" width="80" height="30" uuid="3b7c3652-968d-405d-a188-e25b02c74612"/>                <textFieldExpression><![CDATA[$F{COLUMN1}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="80" y="0" width="100" height="30" uuid="20e014b4-6bfc-4e1a-80cb-fbef504aa550"/>                <textFieldExpression><![CDATA[$F{COLUMN2}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="180" y="0" width="100" height="30" uuid="91cd58ab-847b-4af6-bd1d-12305b6694cf"/>                <textFieldExpression><![CDATA[$F{ROUNDING_MODE}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="280" y="0" width="100" height="30" uuid="db7b95a5-c08a-452a-80c0-3c201713a32a"/>                <textFieldExpression><![CDATA[$F{ROUNDING_MODE}.intValue() <= 0 ? $F{COLUMN2}.setScale(0,BigDecimal.ROUND_HALF_UP).toPlainString() : $F{COLUMN2}.setScale($F{ROUNDING_MODE}.intValue() )]]></textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...