Hiding a column in a Jasper Report output to Excel at run time

[Daniel Donciu]

We need to hide a column in the report based on the value of a calculated field. The report is in Excel format, ServiceRequestMaster_excel.jrxml. I added a calculated non-persisted boolean attribute in the Service Request class, and also added this to the queryString in the jrxml file, and also I create a new field for this attribute, as “<field name=”visible” class=”java.lang.Boolean”/>”. Through some internet search, I found that this should be possible by adding a clause “printWhenExpression” inside the reportElement element in both the groupHeader textField element, and the groupFooter textField element, however this did not solve the problem as the column/attribute is still visible independent of the value of the visible attribute. The element I added is “<printWhenExpression><![CDATA[$F{visible}]]></printWhenExpression>”. Please advise. I attached the report file jrxml.

0

[Daniel Donciu]

Update: the jrxml file type is not permitted for security reasons… so I made a copy in txt.

0

[Daniel Donciu]

The txt didn’t work either (what is permitted????), so here it is (a bit abridged):

<?xml version=”1.0″ encoding=”UTF-8″?>
<jasperReport xmlns=”http://jasperreports.sourceforge.net/jasperreports&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xsi:schemaLocation=”http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd&#8221; name=”ClientComplaintReport_excel” pageWidth=”759″ pageHeight=”611″ orientation=”Landscape” whenNoDataType=”AllSectionsNoDetail” columnWidth=”759″ leftMargin=”0″ rightMargin=”0″ topMargin=”0″ bottomMargin=”0″ isIgnorePagination=”true” uuid=”397f7890-abb0-4c5e-88dc-e258928a1fba”>
<property name=”net.sf.jasperreports.export.xls.white.page.background” value=”false”/>
<property name=”ireport.scriptlethandling” value=”0″/>
<property name=”ireport.encoding” value=”UTF-8″/>
<property name=”ireport.zoom” value=”1.771561000000001″/>
<property name=”ireport.x” value=”359″/>
<property name=”ireport.y” value=”0″/>
<import value=”net.sf.jasperreports.engine.*”/>
<import value=”java.util.*”/>
<import value=”net.sf.jasperreports.engine.data.*”/>
<style name=”Title” fontName=”Arial” fontSize=”14″ isBold=”true”/>
<style name=”Text” fontName=”Arial” fontSize=”8″/>
<style name=”TableHeaderReverse” mode=”Opaque” forecolor=”#FFFFFF” backcolor=”#000000″ fill=”Solid” fontName=”Arial” fontSize=”9″/>
<style name=”Detail” fontName=”Arial” fontSize=”8″>
<box leftPadding=”2″>
<bottomPen lineWidth=”0.25″ lineStyle=”Solid”/>
</box>
</style>
<parameter name=”SUBREPORT_DIR” class=”java.lang.String” isForPrompting=”false”/>
<parameter name=”NEXJ_WHERE” class=”java.lang.Object” isForPrompting=”false”/>
<parameter name=”runByUser” class=”java.lang.String” isForPrompting=”false”/>
<parameter name=”IS_WHITE_PAGE_BACKGROUND” class=”java.lang.Boolean” isForPrompting=”false”>
<defaultValueExpression><![CDATA[Boolean.FALSE]]></defaultValueExpression>
</parameter>
<parameter name=”DateFormatter_mediumDate” class=”java.text.DateFormat” isForPrompting=”false”>
<defaultValueExpression><![CDATA[$P{REPORT_FORMAT_FACTORY}.createDateFormat(((SimpleDateFormat)DateFormat.getDateInstance(DateFormat.MEDIUM, $P{REPORT_LOCALE})).toPattern(), $P{REPORT_LOCALE}, $P{REPORT_TIME_ZONE})]]></defaultValueExpression>
</parameter>
<parameter name=”:instance” class=”java.lang.Object” isForPrompting=”false”/>
<queryString language=”NexJOO”>
<![CDATA[(ServiceRequest (requestIdentifier openTime (creator fullName) iaCode iaName branchCode branchName userFullNames (primaryEntity fullName) (unresolved90Days caption) reOpenedDate text closeComment (template caption) receivedDate complainantName (complainantProvince shortCaption) complainantRelationship acknowledgementDocumentsSentDate (stage caption) (channel caption) receivedBy acknowledgedDate dueTime closeTime account amountRefund visible)
() ((requestIdentifier . #t)))]]>
</queryString>
<field name=”requestIdentifier” class=”java.lang.String”/>
<field name=”openTime” class=”java.sql.Timestamp”/>
<field name=”creator fullName” class=”java.lang.String”/>
<field name=”iaCode” class=”java.lang.String”/>
<field name=”iaName” class=”java.lang.String”/>
<field name=”userFullNames” class=”java.lang.String”/>
<field name=”branchCode” class=”java.lang.String”/>
<field name=”branchName” class=”java.lang.String”/>
<field name=”primaryEntity fullName” class=”java.lang.String”/>
<field name=”unresolved90Days caption” class=”java.lang.String”/>
<field name=”reOpenedDate” class=”java.sql.Timestamp”/>
<field name=”text” class=”java.lang.String”/>
<field name=”closeComment” class=”java.lang.String”/>
<field name=”template caption” class=”java.lang.String”/>
<field name=”receivedDate” class=”java.sql.Timestamp”/>
<field name=”receivedBy” class=”java.lang.String”/>
<field name=”acknowledgedDate” class=”java.sql.Timestamp”/>
<field name=”acknowledgementDocumentsSentDate” class=”java.sql.Timestamp”/>
<field name=”dueTime” class=”java.sql.Timestamp”/>
<field name=”account” class=”java.math.BigDecimal”/>
<field name=”closeTime” class=”java.sql.Timestamp”/>
<field name=”complainantName” class=”java.lang.String”/>
<field name=”complainantRelationship” class=”java.lang.String”/>
<field name=”complainantProvince shortCaption” class=”java.lang.String”/>
<field name=”stage caption” class=”java.lang.String”/>
<field name=”channel caption” class=”java.lang.String”/>
<field name=”amountRefund” class=”java.math.BigDecimal”/>
<field name=”visible” class=”java.lang.Boolean”/>
<variable name=”pageFirstItem” class=”java.lang.String” resetType=”Page” calculation=”First”>
<variableExpression><![CDATA[$F{requestIdentifier}]]></variableExpression>
<initialValueExpression><![CDATA[$F{requestIdentifier}]]></initialValueExpression>
</variable>
<variable name=”staticGroupVal” class=”java.lang.String”>
<variableExpression><![CDATA[“a”]]></variableExpression>
</variable>
<variable name=”DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.SHORT, $P{REPORT_LOCALE}).format(new Date())” class=”java.lang.String”/>
<variable name=”generateDate” class=”java.lang.String”>
<variableExpression><![CDATA[DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.SHORT, $P{REPORT_LOCALE}).format(new Date())]]></variableExpression>
</variable>
<group name=”tableHeader”>
<groupExpression><![CDATA[]]></groupExpression>
<groupHeader>
<band splitType=”Stretch”/>
</groupHeader>
<groupFooter>
<band splitType=”Stretch”/>
</groupFooter>
</group>
<group name=”ReportCountGroup”>
<groupExpression><![CDATA[$V{staticGroupVal}]]></groupExpression>
<groupHeader>
<band splitType=”Stretch”/>
</groupHeader>
<groupFooter>
<band splitType=”Stretch”/>
</groupFooter>
</group>
<group name=”allDetail”>
<groupExpression><![CDATA[$V{REPORT_COUNT}]]></groupExpression>
<groupHeader>
<band height=”25″ splitType=”Stretch”>
<printWhenExpression><![CDATA[new Boolean($V{REPORT_COUNT} == 0)]]></printWhenExpression>
<textField isStretchWithOverflow=”false” isBlankWhenNull=”true”>
<reportElement uuid=”a8b68fde-709c-4d84-a152-51e66d7d8326″ key=”textField-1″ style=”Text” positionType=”Float” mode=”Transparent” x=”1″ y=”0″ width=”100″ height=”25″ isRemoveLineWhenBlank=”true”/>
<box topPadding=”3″ leftPadding=”2″ bottomPadding=”3″ rightPadding=”3″>
<topPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<leftPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<bottomPen lineWidth=”0.0″ lineColor=”#000000″/>
<rightPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
</box>
<textElement textAlignment=”Left” verticalAlignment=”Middle”>
<font fontName=”Arial” size=”8″ isBold=”true” isUnderline=”false” pdfFontName=”Helvetica-Bold”/>
</textElement>
<textFieldExpression><![CDATA[$R{IDS_CREATED}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow=”false” isBlankWhenNull=”true”>
<reportElement uuid=”e55211a9-d71c-46aa-a169-f39179bd05b9″ key=”textField-17″ style=”Text” positionType=”Float” mode=”Transparent” x=”1701″ y=”0″ width=”100″ height=”25″ isRemoveLineWhenBlank=”true”>
<printWhenExpression><![CDATA[$F{visible}]]></printWhenExpression>
</reportElement>
<box topPadding=”3″ leftPadding=”2″ bottomPadding=”3″ rightPadding=”3″>
<topPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<leftPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<bottomPen lineWidth=”0.0″ lineColor=”#000000″/>
<rightPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
</box>
<textElement textAlignment=”Left” verticalAlignment=”Middle”>
<font fontName=”Arial” size=”8″ isBold=”true” isUnderline=”false” pdfFontName=”Helvetica-Bold”/>
</textElement>
<textFieldExpression><![CDATA[$R{idsc.ServiceRequest.amountRefund}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow=”false” isBlankWhenNull=”true”>
<reportElement uuid=”e55211a9-d71c-46aa-a169-f39179dd04b6″ key=”textField-25″ style=”Text” positionType=”Float” mode=”Transparent” x=”2501″ y=”0″ width=”100″ height=”25″ isRemoveLineWhenBlank=”true”/>
<box topPadding=”3″ leftPadding=”2″ bottomPadding=”3″ rightPadding=”3″>
<topPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<leftPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<bottomPen lineWidth=”0.0″ lineColor=”#000000″/>
<rightPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
</box>
<textElement textAlignment=”Left” verticalAlignment=”Middle”>
<font fontName=”Arial” size=”8″ isBold=”true” isUnderline=”false” pdfFontName=”Helvetica-Bold”/>
</textElement>
<textFieldExpression><![CDATA[$R{IDS_ACCOUNT}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
<groupFooter>
<band height=”18″ splitType=”Stretch”>
<textField isStretchWithOverflow=”true” isBlankWhenNull=”true”>
<reportElement uuid=”e9cbe421-5463-4f11-9dac-7e29b79105ed” key=”textField-101″ style=”Text” positionType=”Float” stretchType=”RelativeToTallestObject” mode=”Transparent” x=”1″ y=”0″ width=”100″ height=”16″/>
<box topPadding=”3″ leftPadding=”2″ bottomPadding=”3″ rightPadding=”3″>
<topPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<leftPen lineWidth=”0.0″ lineStyle=”Solid”/>
<rightPen lineWidth=”0.0″ lineStyle=”Solid”/>
</box>
<textElement textAlignment=”Left” verticalAlignment=”Middle”>
<font fontName=”Arial” size=”8″ pdfFontName=”Helvetica”/>
</textElement>
<textFieldExpression><![CDATA[$P{DateFormatter_mediumDate}.format($F{openTime})]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow=”true” isBlankWhenNull=”true”>
<reportElement uuid=”e9036b7a-7af9-48ac-bcb3-fb25434ad728″ key=”textField-117″ style=”Text” positionType=”Float” stretchType=”RelativeToTallestObject” mode=”Transparent” x=”1701″ y=”0″ width=”100″ height=”16″>
<printWhenExpression><![CDATA[$F{visible}]]></printWhenExpression>
</reportElement>
<box topPadding=”3″ leftPadding=”2″ bottomPadding=”3″ rightPadding=”3″>
<topPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<leftPen lineWidth=”0.0″ lineStyle=”Solid”/>
<rightPen lineWidth=”0.0″ lineStyle=”Solid”/>
</box>
<textElement textAlignment=”Left” verticalAlignment=”Middle”>
<font fontName=”Arial” size=”8″ pdfFontName=”Helvetica”/>
</textElement>
<textFieldExpression><![CDATA[$F{amountRefund}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow=”true” isBlankWhenNull=”true”>
<reportElement uuid=”8504a124-b78c-455d-9d26-a4fa1c9df342″ key=”textField-125″ style=”Text” positionType=”Float” stretchType=”RelativeToTallestObject” mode=”Transparent” x=”2501″ y=”0″ width=”100″ height=”16″/>
<box topPadding=”3″ leftPadding=”2″ bottomPadding=”3″ rightPadding=”3″>
<topPen lineWidth=”0.0″ lineStyle=”Solid” lineColor=”#000000″/>
<leftPen lineWidth=”0.0″ lineStyle=”Solid”/>
<rightPen lineWidth=”0.0″ lineStyle=”Solid”/>
</box>
<textElement textAlignment=”Left” verticalAlignment=”Middle”>
<font fontName=”Arial” size=”8″ pdfFontName=”Helvetica”/>
</textElement>
<textFieldExpression><![CDATA[$F{account}]]></textFieldExpression>
</textField>
</band>
</groupFooter>
</group>
<background>
<band splitType=”Stretch”/>
</background>
<title>
<band splitType=”Stretch”/>
</title>
<pageHeader>
<band splitType=”Stretch”/>
</pageHeader>
<columnHeader>
<band splitType=”Stretch”/>
</columnHeader>
<detail>
<band splitType=”Stretch”/>
</detail>
<columnFooter>
<band splitType=”Stretch”/>
</columnFooter>
<pageFooter>
<band splitType=”Stretch”/>
</pageFooter>
<summary>
<band splitType=”Stretch”/>
</summary>
</jasperReport>

0

[Daniel Donciu]

To be clear on what is happening, with the added tag “printWhenExpression” inside the “reportElement”, the header column name and the values are not displayed, however the column is still present as an empty column in the middle of the report. How can we make this column hidden?

0

[Savio Lee]

Jasper report doesn’t support dynamic columns so the general strategy we use is to have overlapping elements where only 1 of the overlapping element is ever displayed using the printWhenExpression.

For example, suppose you have columns A, B, and C, which you want to display during condition true, but only display columns A and C during condition false.

For your true case you will have FieldA, FieldB, FieldC as normal, with the printWhenExpression configured for B and C to only display when true (A appears in both cases in the same position so we don’t need to configure it).

To handle the second case you would create a FieldD which displays the same value as FieldC, however the printWhenExpression will be configured to show when false.

Then you would place FieldD directly on top of FieldB overlapping the same position:

[FieldA] [FieldB/FieldD occupying same space] [FieldC]

That way during condition true jasper renders:

[FieldA] [FieldB] [FieldC]

While during condition false jasper renders:

[FieldA] [FieldD]

But since FieldD is showing the same value as FieldC it should appear as if the column moved next to A.

Note you will need to do this for the elements in your columns as well.

0

[Author]

Posts