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.
3 Answers:
Posted on August 29, 2014 at 4:52pm
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)
Posted on August 29, 2014 at 4:57pm
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>
I don't know what columns the user will select to print. It can be any variation of columns A through F.
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
Ok, I'll give that a try. Thank you!
Implemented this and works very nicely, thanks again!
Your Welcome!! :)