# How to sum based on printed columns

I am using the table component so that the user can select what columns he wants to see on the report and hide the columns not selected.  I am doing this by implementing a boolean parameter for each column and if the value is selected or true then it is printed .  Based on what columns he wants to see, I want to sum across those printed columns. So say the columns he selects are A, C, and D and the value for A is 2, C is 4, D is 3 the total should be 9.

13
Joined: Feb 27 2013 - 10:31am
Last seen: 6 years 7 months ago

Hi,

Use Variables:

VAR1 = COLUMN_A

`\$P{SHOW_COLUMN_A} ? \$F{FIELD_COLUMN_A} : 0`

VAR2 = COLUMN_C

`\$P{SHOW_COLUMN_C} ? \$F{FIELD_COLUMN_C} : 0`

VARn = COLUMN_N

`\$P{SHOW_COLUMN_N} ? \$F{FIELD_COLUMN_N} : 0`

Expression:

`\$V{VAR1} +  \$V{VAR2} + \$V{VARn}`

Regards

523
Joined: Sep 26 2012 - 11:02am
Last seen: 10 months 3 weeks ago

I don't know what columns the user will select to print. It can be any variation of columns A through F.

jndh90 - 9 years 1 month ago

creates variables for all columns, use the column showing the expression to return the field to add or 0 ex: \$P{SHOW_COLUMN_A} ? \$F{FIELD_COLUMN_A} : 0

ecanaveras - 9 years 1 month ago

Ok, I'll give that a try. Thank you!

jndh90 - 9 years 1 month ago
show 2 more...

Implemented this and works very nicely, thanks again!

jndh90 - 9 years 3 weeks ago

ecanaveras - 9 years 3 weeks ago

Suppose you have three Boolean type parms to control the corresponding column print:

<parameter name="print_column1" class="java.lang.Boolean">....
<parameter name="print_column2" class="java.lang.Boolean">...
<parameter name="print_column3" class="java.lang.Boolean">...

You can calculate the printed column tally in a variable with the following expression:

(\$P{print_column1}.booleanValue()?\$F{column1}.intValue():0)+(\$P{print_column2}.booleanValue()?\$F{column2}.intValue():0)+(\$P{print_column3}.booleanValue()?\$F{column3}.intValue():0)

123038
Joined: Feb 27 2008 - 7:33am
Last seen: 1 day 19 hours ago

This is similar to the suggestion from ecanaveras, it works quite nicely. Thank you!

jndh90 - 9 years 3 weeks ago

sample report template - use a Postgres jasperserver DB to test it.

```<?xml version="1.0" encoding="UTF-8"?>
<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="test-selected-sum" language="groovy" pageWidth="595" pageHeight="842" columnWidth="585" leftMargin="5" rightMargin="5" topMargin="20" bottomMargin="20" uuid="8d1de0c6-3a38-43fe-b518-675955e82419">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<conditionalStyle>
<style mode="Opaque" forecolor="#FFFFFF" backcolor="#FF3333"/>
</conditionalStyle>
</style>
<style name="print_super" mode="Opaque" forecolor="#000000" backcolor="#66FF66">
<conditionalStyle>
<conditionExpression><![CDATA[!\$P{print_super}.booleanValue()]]></conditionExpression>
<style mode="Opaque" forecolor="#FFFFFF" backcolor="#FF3333"/>
</conditionalStyle>
</style>
<style name="print_user" mode="Opaque" forecolor="#000000" backcolor="#66FF66">
<conditionalStyle>
<conditionExpression><![CDATA[!\$P{print_user}.booleanValue()]]></conditionExpression>
<style mode="Opaque" forecolor="#FFFFFF" backcolor="#FF3333"/>
</conditionalStyle>
</style>
<style name="print_other" mode="Opaque" forecolor="#000000" backcolor="#66FF66">
<conditionalStyle>
<conditionExpression><![CDATA[!\$P{print_other}.booleanValue()]]></conditionExpression>
<style mode="Opaque" forecolor="#FFFFFF" backcolor="#FF3333"/>
</conditionalStyle>
</style>
<defaultValueExpression><![CDATA[true]]></defaultValueExpression>
</parameter>
<parameter name="print_super" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[true]]></defaultValueExpression>
</parameter>
<parameter name="print_user" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[true]]></defaultValueExpression>
</parameter>
<parameter name="print_other" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[true]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[with t as (
inner join jiuserrole j on j.userid = u.id
inner join jirole r on r.id = j.roleid
)
select username, 1 as admin_role, 0 as super_role, 0 as user_role, 0 as other_role from t where rolename in ('ROLE_ADMINISTRATOR')
union
select username, 0 as admin_role, 1 as super_role, 0 as user_role, 0 as other_role from t where rolename in ('ROLE_SUPERUSER')
union
select username, 0 as admin_role, 0 as super_role, 1 as user_role, 0 as other_role from t where rolename in ('ROLE_USER')
union
select username, 0 as admin_role, 0 as super_role, 0 as user_role, 1 as other_role from t where rolename not in ('ROLE_ADMINISTRATOR','ROLE_SUPERUSER','ROLE_USER')
) x
</queryString>
<field name="super_total" class="java.lang.Long"/>
<field name="user_total" class="java.lang.Long"/>
<field name="other_total" class="java.lang.Long"/>
<variable name="print_total" class="java.lang.Long">
</variable>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band splitType="Stretch"/>
</title>
<band splitType="Stretch"/>
<band height="30" splitType="Stretch">
<staticText>
<reportElement x="1" y="2" width="100" height="20" uuid="26369e38-e485-4566-bed1-682396cb049c"/>
</staticText>
<staticText>
<reportElement style="print_admin" x="101" y="2" width="100" height="20" uuid="0997f537-93ba-4530-b69d-0b3de98a6d68"/>
</staticText>
<staticText>
<reportElement style="print_super" x="201" y="2" width="100" height="20" uuid="ff38e288-7549-41d2-a824-b6e3063e168c"/>
<text><![CDATA[super_total]]></text>
</staticText>
<staticText>
<reportElement style="print_user" x="301" y="2" width="100" height="20" uuid="b0c74ee0-0ca5-401d-80cb-ff9dcc2347e7"/>
<text><![CDATA[user_total]]></text>
</staticText>
<staticText>
<reportElement style="print_other" x="401" y="2" width="100" height="20" uuid="84dea072-cc93-4012-8327-884c676a6bb0"/>
<text><![CDATA[other_total]]></text>
</staticText>
</band>
<detail>
<band height="30" splitType="Stretch">
<textField>
<reportElement x="1" y="0" width="100" height="20" uuid="d7004c14-db4c-4f54-9130-224b124c1367"/>
</textField>
<textField pattern="###0;-###0">
<reportElement style="print_admin" x="101" y="0" width="100" height="20" uuid="71e35f4c-44f4-4a47-b0b4-334c7c9eb039"/>
</textField>
<textField pattern="###0;-###0">
<reportElement style="print_super" x="201" y="0" width="100" height="20" uuid="5f3cb0cd-f6db-423b-8234-cd5e8ecc2f0d"/>
<textFieldExpression><![CDATA[\$F{super_total}]]></textFieldExpression>
</textField>
<textField pattern="###0;-###0">
<reportElement style="print_user" x="301" y="0" width="100" height="20" uuid="70384657-434d-4956-840f-036d21087aca"/>
<textFieldExpression><![CDATA[\$F{user_total}]]></textFieldExpression>
</textField>
<textField pattern="###0;-###0">
<reportElement style="print_other" x="401" y="0" width="100" height="20" uuid="2e77aa81-e93b-4f6a-af80-38dd7acd4c98"/>
<textFieldExpression><![CDATA[\$F{other_total}]]></textFieldExpression>
</textField>
<textField pattern="###0;-###0">
<reportElement x="501" y="0" width="84" height="20" uuid="4685224e-e97c-44c3-8a8b-00dc0f3980fc"/>
<textElement>
<font size="12" isBold="true" isUnderline="false"/>
</textElement>
<textFieldExpression><![CDATA[\$V{print_total}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter>
<band splitType="Stretch"/>
</columnFooter>
<pageFooter>
<band splitType="Stretch"/>
</pageFooter>
<summary>
<band splitType="Stretch"/>
</summary>
</jasperReport>```

123038
Joined: Feb 27 2008 - 7:33am
Last seen: 1 day 19 hours ago