How to sum based on printed columns

Jaspersoft Community 'Read-Only' as of July 8, 2022

Transition to New TIBCO Community Just Weeks Away

You can still search, review wiki content, and review discussions in read-only mode. Please email community@tibco.com with questions or issues requiring TIBCO review or response.

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. 

jndh90's picture
13
Joined: Feb 27 2013 - 10:31am
Last seen: 5 years 4 months ago

3 Answers:

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

ecanaveras's picture
Joined: Sep 26 2012 - 11:02am
Last seen: 2 months 1 week ago

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

jndh90 - 7 years 10 months 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 - 7 years 10 months ago

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

jndh90 - 7 years 10 months ago
show 2 more...

Implemented this and works very nicely, thanks again!

jndh90 - 7 years 10 months ago

Your Welcome!! :)

ecanaveras - 7 years 10 months 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)

tchen's picture
112971
Joined: Feb 27 2008 - 7:33am
Last seen: 3 days 2 hours ago

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

jndh90 - 7 years 10 months 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"/>
    <style name="print_admin" mode="Opaque" forecolor="#000000" backcolor="#66FF66">
        <conditionalStyle>
            <conditionExpression><![CDATA[!$P{print_admin}.booleanValue()]]></conditionExpression>
            <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>
    <parameter name="print_admin" class="java.lang.Boolean">
        <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 (
select u.username as username, r.rolename as rolename from jiuser u
inner join jiuserrole j on j.userid = u.id
inner join jirole r on r.id = j.roleid
)
select username, sum(admin_role) as admin_total, sum(super_role) as super_total, sum(user_role) as user_total, sum(other_role) as other_total from (
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
group by username
order by username]]>
    </queryString>
    <field name="username" class="java.lang.String"/>
    <field name="admin_total" class="java.lang.Long"/>
    <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">
        <variableExpression><![CDATA[($P{print_admin}.booleanValue()?$F{admin_total}.intValue():0)+($P{print_super}.booleanValue()?$F{super_total}.intValue():0)+($P{print_user}.booleanValue()?$F{user_total}.intValue():0)+($P{print_other}.booleanValue()?$F{other_total}.intValue():0)]]></variableExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band splitType="Stretch"/>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="30" splitType="Stretch">
            <staticText>
                <reportElement x="1" y="2" width="100" height="20" uuid="26369e38-e485-4566-bed1-682396cb049c"/>
                <text><![CDATA[username]]></text>
            </staticText>
            <staticText>
                <reportElement style="print_admin" x="101" y="2" width="100" height="20" uuid="0997f537-93ba-4530-b69d-0b3de98a6d68"/>
                <text><![CDATA[admin_total]]></text>
            </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>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="1" y="0" width="100" height="20" uuid="d7004c14-db4c-4f54-9130-224b124c1367"/>
                <textFieldExpression><![CDATA[$F{username}]]></textFieldExpression>
            </textField>
            <textField pattern="###0;-###0">
                <reportElement style="print_admin" x="101" y="0" width="100" height="20" uuid="71e35f4c-44f4-4a47-b0b4-334c7c9eb039"/>
                <textFieldExpression><![CDATA[$F{admin_total}]]></textFieldExpression>
            </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>

tchen's picture
112971
Joined: Feb 27 2008 - 7:33am
Last seen: 3 days 2 hours ago
Feedback
randomness