chrisj.msac Posted March 2, 2018 Share Posted March 2, 2018 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.5jam 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 More sharing options...
asejas Posted March 2, 2018 Share Posted March 2, 2018 I would use scriptlets:https://community.jaspersoft.com/blog/all-you-want-know-about-scriptlets Link to comment Share on other sites More sharing options...
reportdev Posted March 2, 2018 Share Posted March 2, 2018 Try doing this wayCreate an rounding_id column in your SQLuse jasper expressions to change the rounding mode and display the valueEx: 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now