Jump to content

How to sum based on printed columns


jndh90

Recommended Posts

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. 

Link to comment
Share on other sites

  • Replies 9
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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)

Link to comment
Share on other sites

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 uinner join jiuserrole j on j.userid = u.idinner 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')unionselect username, 0 as admin_role, 1 as super_role, 0 as user_role, 0 as other_role from t where rolename in ('ROLE_SUPERUSER')unionselect username, 0 as admin_role, 0 as super_role, 1 as user_role, 0 as other_role from t where rolename in ('ROLE_USER')unionselect 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')) xgroup by usernameorder 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>[/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...