How to join columns in jaspersoft studio

I need to make a report that the user will choose which columns he wants to display, for example in the case where there are 9 columns in total, and the user chooses less, I end up with some blank spaces.
Would there be a way to join only the columns that the user chose and align them to the left? Without the blank gaps in between

To create these columns I'm using a simple Text Field.

These Fields texts are with the parameter (Blank When NUL= true)

I'm using Jaspersoft Studio 6.19.0 I tried to use Selecting columns but I couldn't

gabriel.baldez's picture
Joined: Feb 25 2022 - 6:33am
Last seen: 1 month 3 weeks ago

1 Answer:

You can create a user-specified column report where you prompt the user which columns they want and then populate a set of variables with the data requested.

In the example below (test data coming from www.mockaroo.com) the user can specify ID, first_name, last_name, and sales_birth_date in any of the 5 column parameters.  The report will then populate the columns accordingly using the col_n_data variables.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.14.0.final using JasperReports Library version 6.14.0-2ab0d8625be255bf609c78e1181801213e51db8f  -->
<!-- 2022-06-26T14:41:28 -->
<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="Example of User Specified Colums" pageWidth="792" pageHeight="612" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="752" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="f8d5675b-20b0-44cb-9948-92502a7067cb">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="PeimsDS"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <parameter name="Col_1" class="java.lang.String"/>
    <parameter name="Col_2" class="java.lang.String"/>
    <parameter name="Col_3" class="java.lang.String"/>
    <parameter name="Col_4" class="java.lang.String"/>
    <parameter name="Col_5" class="java.lang.String"/>
    <queryString language="plsql">
        <![CDATA[Select 1 as ID, 'Dorey' as FIRST_NAME, 'Dimsdale' as LAST_NAME, 120875 as SALES, TO_DATE('6/8/2005', 'MM/DD/YYYY') as BIRTH_DATE
FROM DUAL
UNION ALL
Select 2 as ID, 'Fonzie' as FIRST_NAME, 'Liley' as LAST_NAME, 26567 as SALES, TO_DATE('7/7/2005', 'MM/DD/YYYY') as BIRTH_DATE
FROM DUAL
UNION ALL
Select 3 as ID, 'Alis' as FIRST_NAME, 'Bisacre' as LAST_NAME, 148660 as SALES, TO_DATE('6/29/2005', 'MM/DD/YYYY') as BIRTH_DATE
FROM DUAL
UNION ALL
Select 4 as ID, 'Nanni' as FIRST_NAME, 'McKew' as LAST_NAME, 29524 as SALES, TO_DATE('7/23/2005', 'MM/DD/YYYY') as BIRTH_DATE
FROM DUAL]]>
    </queryString>
    <field name="ID" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.name" value="ID"/>
        <property name="com.jaspersoft.studio.field.label" value="ID"/>
    </field>
    <field name="FIRST_NAME" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.name" value="FIRST_NAME"/>
        <property name="com.jaspersoft.studio.field.label" value="FIRST_NAME"/>
    </field>
    <field name="LAST_NAME" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.name" value="LAST_NAME"/>
        <property name="com.jaspersoft.studio.field.label" value="LAST_NAME"/>
    </field>
    <field name="SALES" class="java.math.BigDecimal">
        <property name="com.jaspersoft.studio.field.name" value="SALES"/>
        <property name="com.jaspersoft.studio.field.label" value="SALES"/>
    </field>
    <field name="BIRTH_DATE" class="java.sql.Timestamp">
        <property name="com.jaspersoft.studio.field.name" value="BIRTH_DATE"/>
        <property name="com.jaspersoft.studio.field.label" value="BIRTH_DATE"/>
    </field>
    <variable name="Col_1_Label" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_1}.equalsIgnoreCase( "ID" )              ? "ID"
    :$P{Col_1}.equalsIgnoreCase( "FIRST_NAME" ) ? "FIRST_NAME"
    :$P{Col_1}.equalsIgnoreCase( "LAST_NAME" )  ? "LAST_NAME"
    :$P{Col_1}.equalsIgnoreCase( "SALES" )      ? "SALES"
    :$P{Col_1}.equalsIgnoreCase( "BIRTH_DATE" ) ? "BIRTH_DATE"
    : ""]]></variableExpression>
    </variable>
    <variable name="Col_2_Label" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_2} == null ? " "
    :$P{Col_2}.equalsIgnoreCase( "ID" )         ? "ID"
    :$P{Col_2}.equalsIgnoreCase( "FIRST_NAME" ) ? "FIRST_NAME"
    :$P{Col_2}.equalsIgnoreCase( "LAST_NAME" )  ? "LAST_NAME"
    :$P{Col_2}.equalsIgnoreCase( "SALES" )      ? "SALES"
    :$P{Col_2}.equalsIgnoreCase( "BIRTH_DATE" ) ? "BIRTH_DATE"
    : " "]]></variableExpression>
    </variable>
    <variable name="Col_3_Label" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_3} == null ? " "
    :$P{Col_3}.equalsIgnoreCase( "ID" )         ? "ID"
    :$P{Col_3}.equalsIgnoreCase( "FIRST_NAME" ) ? "FIRST_NAME"
    :$P{Col_3}.equalsIgnoreCase( "LAST_NAME" )  ? "LAST_NAME"
    :$P{Col_3}.equalsIgnoreCase( "SALES" )      ? "SALES"
    :$P{Col_3}.equalsIgnoreCase( "BIRTH_DATE" ) ? "BIRTH_DATE"
    : " "]]></variableExpression>
    </variable>
    <variable name="Col_4_Label" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_4} == null ? " "
    :$P{Col_4}.equalsIgnoreCase( "ID" )         ? "ID"
    :$P{Col_4}.equalsIgnoreCase( "FIRST_NAME" ) ? "FIRST_NAME"
    :$P{Col_4}.equalsIgnoreCase( "LAST_NAME" )  ? "LAST_NAME"
    :$P{Col_4}.equalsIgnoreCase( "SALES" )      ? "SALES"
    :$P{Col_4}.equalsIgnoreCase( "BIRTH_DATE" ) ? "BIRTH_DATE"
    : " "]]></variableExpression>
    </variable>
    <variable name="Col_5_Label" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_5} == null ? " "
    :$P{Col_5}.equalsIgnoreCase( "ID" )         ? "ID"
    :$P{Col_5}.equalsIgnoreCase( "FIRST_NAME" ) ? "FIRST_NAME"
    :$P{Col_5}.equalsIgnoreCase( "LAST_NAME" )  ? "LAST_NAME"
    :$P{Col_5}.equalsIgnoreCase( "SALES" )      ? "SALES"
    :$P{Col_5}.equalsIgnoreCase( "BIRTH_DATE" ) ? "BIRTH_DATE"
    : " "]]></variableExpression>
    </variable>
    <variable name="Col_1_Data" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_1} == null ? ""
    :$P{Col_1}.equalsIgnoreCase( "ID" )         ? TEXT($F{ID},"#########")
    :$P{Col_1}.equalsIgnoreCase( "FIRST_NAME" ) ? $F{FIRST_NAME}
    :$P{Col_1}.equalsIgnoreCase( "LAST_NAME" )  ? $F{LAST_NAME}
    :$P{Col_1}.equalsIgnoreCase( "SALES" )      ? TEXT($F{SALES},"¤#,##0.###;¤(-#,##0.###)")
    :$P{Col_1}.equalsIgnoreCase( "BIRTH_DATE" ) ? new SimpleDateFormat( "dd-MMM-yyyy").format($F{BIRTH_DATE})
    : ""]]></variableExpression>
    </variable>
    <variable name="Col_2_Data" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_2} == null ? ""
    :$P{Col_2}.equalsIgnoreCase( "ID" )         ? TEXT($F{ID},"#########")
    :$P{Col_2}.equalsIgnoreCase( "FIRST_NAME" ) ? $F{FIRST_NAME}
    :$P{Col_2}.equalsIgnoreCase( "LAST_NAME" )  ? $F{LAST_NAME}
    :$P{Col_2}.equalsIgnoreCase( "SALES" )      ? TEXT($F{SALES},"¤#,##0.###;¤(-#,##0.###)")
    :$P{Col_2}.equalsIgnoreCase( "BIRTH_DATE" ) ? new SimpleDateFormat( "dd-MMM-yyyy").format($F{BIRTH_DATE})
    : ""]]></variableExpression>
    </variable>
    <variable name="Col_3_Data" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_3} == null ? ""
    :$P{Col_3}.equalsIgnoreCase( "ID" )         ? TEXT($F{ID},"#########")
    :$P{Col_3}.equalsIgnoreCase( "FIRST_NAME" ) ? $F{FIRST_NAME}
    :$P{Col_3}.equalsIgnoreCase( "LAST_NAME" )  ? $F{LAST_NAME}
    :$P{Col_3}.equalsIgnoreCase( "SALES" )      ? TEXT($F{SALES},"¤#,##0.###;¤(-#,##0.###)")
    :$P{Col_3}.equalsIgnoreCase( "BIRTH_DATE" ) ? new SimpleDateFormat( "dd-MMM-yyyy").format($F{BIRTH_DATE})
    : ""]]></variableExpression>
    </variable>
    <variable name="Col_4_Data" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_4} == null ? ""
    :$P{Col_4}.equalsIgnoreCase( "ID" )         ? TEXT($F{ID},"#########")
    :$P{Col_4}.equalsIgnoreCase( "FIRST_NAME" ) ? $F{FIRST_NAME}
    :$P{Col_4}.equalsIgnoreCase( "LAST_NAME" )  ? $F{LAST_NAME}
    :$P{Col_4}.equalsIgnoreCase( "SALES" )      ? TEXT($F{SALES},"¤#,##0.###;¤(-#,##0.###)")
    :$P{Col_4}.equalsIgnoreCase( "BIRTH_DATE" ) ? new SimpleDateFormat( "dd-MMM-yyyy").format($F{BIRTH_DATE})
    : ""]]></variableExpression>
    </variable>
    <variable name="Col_5_Data" class="java.lang.String">
        <variableExpression><![CDATA[$P{Col_5} == null ? ""
    :$P{Col_5}.equalsIgnoreCase( "ID" )         ? TEXT($F{ID},"#########")
    :$P{Col_5}.equalsIgnoreCase( "FIRST_NAME" ) ? $F{FIRST_NAME}
    :$P{Col_5}.equalsIgnoreCase( "LAST_NAME" )  ? $F{LAST_NAME}
    :$P{Col_5}.equalsIgnoreCase( "SALES" )      ? TEXT($F{SALES},"¤#,##0.###;¤(-#,##0.###)")
    :$P{Col_5}.equalsIgnoreCase( "BIRTH_DATE" ) ? new SimpleDateFormat( "dd-MMM-yyyy").format($F{BIRTH_DATE})
    : ""]]></variableExpression>
    </variable>
    <pageHeader>
        <band height="53" splitType="Stretch">
            <staticText>
                <reportElement x="228" y="1" width="297" height="22" uuid="27d6c2b1-30b7-4704-a78a-45b5eae41c9e"/>
                <textElement textAlignment="Center">
                    <font fontName="Arial" size="18" isBold="true"/>
                </textElement>
                <text><![CDATA[Example of User Specified Colums]]></text>
            </staticText>
            <textField evaluationTime="Auto" isBlankWhenNull="false">
                <reportElement x="0" y="31" width="151" height="16" uuid="f61d74ab-3fd0-46aa-9c8d-6f39e90e933a"/>
                <box>
                    <bottomPen lineWidth="1.0"/>
                </box>
                <textFieldExpression><![CDATA[$V{Col_1_Label}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Auto" isBlankWhenNull="false">
                <reportElement x="151" y="31" width="151" height="16" uuid="3b25a046-2b57-4fcc-8189-e8b912f66551"/>
                <box>
                    <bottomPen lineWidth="1.0"/>
                </box>
                <textFieldExpression><![CDATA[$V{Col_2_Label}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Auto" isBlankWhenNull="false">
                <reportElement x="302" y="31" width="150" height="16" uuid="95b6b178-a0c8-424a-8489-c4e08e4188fd"/>
                <box>
                    <bottomPen lineWidth="1.0"/>
                </box>
                <textFieldExpression><![CDATA[$V{Col_3_Label}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Auto" isBlankWhenNull="false">
                <reportElement x="452" y="31" width="150" height="16" uuid="1877d8c8-224c-4adb-bed9-4760ae4dbb29"/>
                <box>
                    <bottomPen lineWidth="1.0"/>
                </box>
                <textFieldExpression><![CDATA[$V{Col_4_Label}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Auto" isBlankWhenNull="false">
                <reportElement x="602" y="31" width="150" height="16" uuid="b3e24594-7741-4572-9b91-9b3876a3297a"/>
                <box>
                    <bottomPen lineWidth="1.0"/>
                </box>
                <textFieldExpression><![CDATA[$V{Col_5_Label}]]></textFieldExpression>
            </textField>
        </band>
    </pageHeader>
    <detail>
        <band height="17" splitType="Stretch">
            <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.spreadsheet.SpreadsheetLayout"/>
            <textField>
                <reportElement x="0" y="0" width="151" height="17" uuid="beeb9673-e5f3-4eb8-9222-5da0c47dcb54"/>
                <textFieldExpression><![CDATA[$V{Col_1_Data}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="151" y="0" width="151" height="17" uuid="6cb15937-4d83-4b6b-85f1-d473968d51ad"/>
                <textFieldExpression><![CDATA[$V{Col_2_Data}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="302" y="0" width="150" height="17" uuid="66acdfc0-e791-4208-8eab-5fa5d4a26e97"/>
                <textFieldExpression><![CDATA[$V{Col_3_Data}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="452" y="0" width="150" height="17" uuid="a58e1c48-1c0a-4412-aedb-9706aa8282b9"/>
                <textFieldExpression><![CDATA[$V{Col_4_Data}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="602" y="0" width="150" height="17" uuid="e140ed37-bd68-4918-9d03-42d750a87b0c"/>
                <textFieldExpression><![CDATA[$V{Col_5_Data}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <pageFooter>
        <band height="13">
            <textField textAdjust="StretchHeight">
                <reportElement stretchType="ContainerHeight" x="0" y="0" width="727" height="13" uuid="7aab1f05-1f49-46c9-8cd7-66861c5e843e"/>
                <textFieldExpression><![CDATA[$P{JASPER_REPORT}.getName()]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
</jasperReport>

jgust's picture
2138
Joined: Jun 10 2010 - 6:39am
Last seen: 1 hour 1 min ago
Feedback