jndh90 Posted August 29, 2014 Share Posted August 29, 2014 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 More sharing options...
ecanaveras Posted August 29, 2014 Share Posted August 29, 2014 Hi,Use Variables:VAR1 = COLUMN_A$P{SHOW_COLUMN_A} ? $F{FIELD_COLUMN_A} : 0[/code]VAR2 = COLUMN_C$P{SHOW_COLUMN_C} ? $F{FIELD_COLUMN_C} : 0[/code]VARn = COLUMN_N$P{SHOW_COLUMN_N} ? $F{FIELD_COLUMN_N} : 0[/code] Expression:$V{VAR1} + $V{VAR2} + $V{VARn}[/code] Regards Link to comment Share on other sites More sharing options...
jndh90 Posted August 29, 2014 Author Share Posted August 29, 2014 I don't know what columns the user will select to print. It can be any variation of columns A through F. Link to comment Share on other sites More sharing options...
ecanaveras Posted August 29, 2014 Share Posted August 29, 2014 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 Link to comment Share on other sites More sharing options...
jndh90 Posted August 29, 2014 Author Share Posted August 29, 2014 Ok, I'll give that a try. Thank you! Link to comment Share on other sites More sharing options...
Tom C Posted August 29, 2014 Share Posted August 29, 2014 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 More sharing options...
Tom C Posted August 29, 2014 Share Posted August 29, 2014 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 More sharing options...
jndh90 Posted September 2, 2014 Author Share Posted September 2, 2014 This is similar to the suggestion from ecanaveras, it works quite nicely. Thank you! Link to comment Share on other sites More sharing options...
jndh90 Posted September 2, 2014 Author Share Posted September 2, 2014 Implemented this and works very nicely, thanks again! Link to comment Share on other sites More sharing options...
ecanaveras Posted September 2, 2014 Share Posted September 2, 2014 Your Welcome!! :) 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