Tom C

This Q & A series will post answers to most frequently asked questions from our commercial users to benefit the entire user community. The Questions:In TIBCO JasperReports Server Security Guide (https://community.jaspersoft.com/documentation/tibco-jasperreports-server-security-guide/v7/key-and-keystore-management), it states that "Because the keystore and keys are created during installation, the user account that performs the installation is the owner of the keystore file and holder of the keystore passwords". Does it refer to the password in the .jrsksp file? Where can a user get a keystore file when exporting the repository content from JasperReports Server (JRS) 7.5 export web UI in order to import it into another JRS 7.5 instance? The exported zip doesn't contain any key file. The Answer: The server specific keystore files (.jrsks and .jrsksp) are generated during the JRS installation. The keystore is used to encrypt and decrypt any password stored in the JRS repository database, such as user password, and JDBC connection password, etc. Those keystore files are stored on the server where the default location is the Linux root or Windows user root. Users need to make sure this location has full access to allow read and write permissions for the application server (Tomcat) or it will fail to start with a keystore initialization error. Those two keystore files do not require a password to access their content. When running js-export from the command line with the --genkey option, the user will see the generated key sequence in the command line display. User needs to copy that sequence and use it to run the import in another repository instance. The exported repository content file does not contain that secret key sequence or it will defeat the purpose of having a unique key for each and every JRS server instance to avoid hacking. Users can refer to the following customer wiki articles of mine for additional information with more details. https://community.jaspersoft.com/wiki/tibco-jasperreports-server-75-encryption-configuration-files https://community.jaspersoft.com/wiki/migrating-tibco-jasperreports-server-repository-content-between-two-75-version-server In JRS 7.5.x, users need to use command line export utility to get the key for the import to use. In the later version of JRS, users should be able to specify which key to use for repository content migration in the Export web UI. https://community.jaspersoft.com/documentation/tibco-jasperreports-server-administrator-guide/v790/import-and-export-through-web-ui "Exporting from the Repository" Users do not have a similar "genkey" option from the web UI but they can use "Legacy key" instead to fulfill their cross server repository content migration need for post JRS 7.5 version instances. The pre 7.5 JRS does not need a server specific key to migrate its repo content though the export/import process. For details about this repository keystore issue, please review TIBCO JasperReports® Server Administrator Guide Software Release 7.5 document CHAPTER 7 "IMPORT AND EXPORT" and related topics in JasperReports Server Security Guide. https://community.jaspersoft.com/documentation/tibco-jasperreports-server-administrator-guide/v750/import-and-export-through-web-ui https://community.jaspersoft.com/documentation/tibco-jasperreports-server-security-guide/v7/managing-keys-import-and-export ============================================ TTC-20210523-1945472-20231102

This is a sample report demonstrating how to lay out report items in two columns in a top to bottom then left to right fashion with evenly distributed line items in each column. Report Design Consideration- The report uses the PostgreSQL ROW_NUMBER() function to assign a unique sequential integer value to each row in a result set. - Based on the sequentialized resultset, the report can divide the report data into two seperate lists and display them side by side in the report. - This report only works for a single page layout. Report Design Template<?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="20210516sample" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="0c1b2252-fb72-4242-85b5-88e8c034f395"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <subDataset name="Dataset1" uuid="30c06156-693a-4dde-8261-d3daeb06f0f1"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <parameter name="ORDER BY" class="java.lang.String" isForPrompting="false"/> <parameter name="report_query" class="java.lang.String" isForPrompting="false"/> <parameter name="LIST QUERY" class="java.lang.String"> <defaultValueExpression><![CDATA[$P{report_query}+", selectdata as (SELECT row_number() OVER(ORDER BY "+$P{ORDER BY}+" ASC) AS row, * FROM reportdata)"]]></defaultValueExpression> </parameter> <parameter name="totalrows" class="java.lang.Integer"/> <parameter name="list_left" class="java.lang.Boolean"/> <parameter name="rowLimit" class="java.lang.String"> <defaultValueExpression><![CDATA[" where row "+($P{list_left}.booleanValue()?"<= "+($P{totalrows}.intValue()+1)/2:"> "+($P{totalrows}.intValue()+1)/2)]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[$P!{LIST QUERY} select * from selectdata $P!{rowLimit} ]]> </queryString> <field name="row" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="row"/> <property name="com.jaspersoft.studio.field.label" value="row"/> </field> <field name="organization" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="organization"/> <property name="com.jaspersoft.studio.field.label" value="organization"/> <property name="com.jaspersoft.studio.field.tree.path" value="jitenant"/> </field> <field name="orguser" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="orguser"/> <property name="com.jaspersoft.studio.field.label" value="orguser"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiuser"/> </field> <field name="orguserrole" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="orguserrole"/> <property name="com.jaspersoft.studio.field.label" value="orguserrole"/> <property name="com.jaspersoft.studio.field.tree.path" value="jirole"/> </field> </subDataset> <parameter name="REPORT QUERY" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["select t.tenantname as organization, u.username as orguser, r.rolename as orguserrole from jitenant t inner join jiuser u on u.tenantid = t.id inner join jiuserrole j on j.userid = u.id inner join jirole r on r.id = j.roleid"]]></defaultValueExpression> </parameter> <parameter name="ORDER BY" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["organization, orguser, orguserrole"]]></defaultValueExpression> </parameter> <parameter name="report_query" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["with reportdata as ( "+$P{REPORT QUERY}+" order by "+$P{ORDER BY}+" limit "+$P{Record Limit}+" )"]]></defaultValueExpression> </parameter> <parameter name="Record Limit" class="java.lang.Integer"> <defaultValueExpression><![CDATA[5]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[$P!{report_query} select count(*) as totalRows from reportdata]]> </queryString> <field name="totalrows" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="totalrows"/> <property name="com.jaspersoft.studio.field.label" value="totalrows"/> </field> <detail> <band height="35" splitType="Prevent"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <componentElement> <reportElement x="0" y="0" width="280" height="30" uuid="ca9384e9-802b-4ddd-8875-371ad5382b63"/> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical"> <datasetRun subDataset="Dataset1" uuid="db2d7cc1-d724-432a-a323-1705eb260dc5"> <datasetParameter name="report_query"> <datasetParameterExpression><![CDATA[$P{report_query}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="totalrows"> <datasetParameterExpression><![CDATA[$F{totalrows}.intValue()]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="list_left"> <datasetParameterExpression><![CDATA[true]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="280"> <textField> <reportElement x="0" y="0" width="40" height="30" forecolor="#FF0000" uuid="2c2be3bc-ab1f-40bc-b854-e07181f0b22b"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{row}]]></textFieldExpression> </textField> <textField> <reportElement x="40" y="0" width="90" height="30" forecolor="#FF0000" uuid="a1c165b2-0f5b-49c0-a110-b5b4ab360745"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{organization}]]></textFieldExpression> </textField> <textField> <reportElement x="130" y="0" width="80" height="30" forecolor="#FF0000" uuid="61303761-0387-43c3-b0b0-633287f6cd67"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{orguser}]]></textFieldExpression> </textField> <textField> <reportElement x="210" y="0" width="70" height="30" forecolor="#FF0000" uuid="9ef3983f-22e9-4f26-b8fb-02d07ac6a6c7"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{orguserrole}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> <componentElement> <reportElement x="290" y="0" width="280" height="30" uuid="ab3ff1c5-ceb6-4159-88d5-96810a2969c7"> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical"> <datasetRun subDataset="Dataset1" uuid="940cd6b9-40dc-4a5f-a629-16363a8ad6ab"> <datasetParameter name="report_query"> <datasetParameterExpression><![CDATA[$P{report_query}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="totalrows"> <datasetParameterExpression><![CDATA[$F{totalrows}.intValue()]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="list_left"> <datasetParameterExpression><![CDATA[false]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="280"> <textField> <reportElement x="0" y="0" width="40" height="30" forecolor="#009900" uuid="de0b168a-87e0-45f3-a048-df58794a61ab"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{row}]]></textFieldExpression> </textField> <textField> <reportElement x="40" y="0" width="90" height="30" forecolor="#009900" uuid="de907bc3-a5d2-4c5c-a482-d69800e56593"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{organization}]]></textFieldExpression> </textField> <textField> <reportElement x="130" y="0" width="80" height="30" forecolor="#009900" uuid="2561ef02-cf1f-4d1f-9299-9685cc9490c6"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{orguser}]]></textFieldExpression> </textField> <textField> <reportElement x="210" y="0" width="70" height="30" forecolor="#009900" uuid="8e64b58f-e7f2-430e-b71b-d607e9c954f5"/> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{orguserrole}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> </detail> </jasperReport> Design notes:$P{REPORT QUERY} contains report query for the result dataset; $P{ORDER BY} holds the order of the result set. This is used in both main report and sub dataset to generate record sequence in the correct order; The main report query only provide records tally to help determine where the record break occurs into two list segment for the left and right column display; The sub dataset Dataset1 has a where condition to provide subset of the data based on which list element is making the call: For the list on the left: "where row <= " total record midpoint For the list on the right: "where row > " total record midpoint The "midpoint" is the (total sequence number + 1) / 2 to guarantee the left column contains one more record than the right column should total count being an odd number; Report Output Note:- Users can test run this report in Jaspersoft Studio (JSS) 7.9 using a PostgresQL JasperReports Server (JRS) repository database. - User can use "Record Limit" parameter to control the query result set number of rows being returned to test the report layout calculation. TTC-20210522

Users can store image files into JasperReports Server (JRS) repository database, and use a query to get the image resource and have the image blob displayed in the report. Here is a very simple sample report to demonstrate how to display a repo image in a report. Users need to deploy the "st_patricks_day.gif" image into the JRS repository in any folder (make sure the image label is st_patricks_day.gif) and test run the report in JSS 7.9 using that repo datasource. - report query select f.data from jifileresource f inner join jiresource r on r.id = f.id and r.label = 'st-patricks-day.gif' - field type <field name="data" class="java.io.InputStream"/> - display the image field in the report <image> <reportElement x="0" y="0" width="572" height="180" uuid="f3d25d1b-8cf2-4c1f-ab6b-8269afaf0a6f"/> <imageExpression><![CDATA[$F{data}]]></imageExpression> </image> Report Template <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample report" language="groovy" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e0793667-0723-42e3-be12-37c9ec18aae9"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <queryString> <![CDATA[select f.data from jifileresource f inner join jiresource r on r.id = f.id and r.label = 'st-patricks-day.gif']]> </queryString> <field name="data" class="java.io.InputStream"/> <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 height="200" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <image> <reportElement x="0" y="0" width="572" height="180" uuid="f3d25d1b-8cf2-4c1f-ab6b-8269afaf0a6f"/> <imageExpression><![CDATA[$F{data}]]></imageExpression> </image> </band> </summary> </jasperReport> Report HTML output showing the gif animation ================================================== TTC-20210515

This is the fifth of the mini series of sample reports using various techniques to deal with multi-sheet report xls outputs. In this sample report, users can select between a standard report template focusing on column header being displayed in every non spreadsheet output page, and a spreadsheet template focusing on column header being displayed in every spreadsheet. Since the report template cannot be changed at the export phase of the report generation, users have to choose different template layouts at the runtime to generate a report intended for standard output or a multi-sheet xls output. This report uses group break to generate new spreadsheet with the "break before row" property. For a standard output report, the column header is created in the normal way using column header band and will be present in every new page. The multi-sheet xls output has all its column header suppressed to cope with large group content being across multiple pages thus having more than one column headers.. For a spreadsheet output report, the column header band is disabled and column header information is placed in the group header to guarantee every sheet in xls report output will have a column header. Other report output formats (HTML, PDF, etc.) only have column header displayed in the group break page. Report Template <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample report with multi-sheet" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.3" value="columnHeader"/> <parameter name="Spreadsheet Output" class="java.lang.Boolean"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="Row per Sheet" class="java.lang.Integer" isForPrompting="false"> <defaultValueExpression><![CDATA[50]]></defaultValueExpression> </parameter> <parameter name="DEBUG" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[false]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[with r_count as ( select resourcetype, count(resourcetype)as resourcetype_count from jiresource group by resourcetype ) select jiresource.name , jiresource.label , jiresource.resourcetype , r_count.resourcetype_count from jiresource inner join r_count on r_count.resourcetype = jiresource.resourcetype order by jiresource.resourcetype]]> </queryString> <field name="name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="name"/> <property name="com.jaspersoft.studio.field.label" value="name"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="label" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="label"/> <property name="com.jaspersoft.studio.field.label" value="label"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="resourcetype" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="resourcetype"/> <property name="com.jaspersoft.studio.field.label" value="resourcetype"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="resourcetype_count" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="resourcetype_count"/> <property name="com.jaspersoft.studio.field.label" value="resourcetype_count"/> </field> <group name="Sheet" isStartNewPage="true"> <groupExpression><![CDATA[$F{resourcetype}]]></groupExpression> <groupHeader> <band height="30"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement mode="Transparent" x="0" y="0" width="380" height="30" backcolor="#FFCCFF" uuid="5ba31fcd-322b-404a-9b62-55fa9aab5681"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819923a"/> <printWhenExpression><![CDATA[!($V{REPORT_COUNT}.intValue()>0)&&$P{Spreadsheet Output}.booleanValue()]]></printWhenExpression> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[resourcetype]]></text> </staticText> <staticText> <reportElement mode="Transparent" x="0" y="0" width="380" height="30" backcolor="#99FF00" uuid="5ba31fcd-322b-404a-9b62-55fa9aab5681"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819934b"/> <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/> <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()>0&&$P{Spreadsheet Output}.booleanValue()]]></printWhenExpression> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[resourcetype]]></text> </staticText> <staticText> <reportElement x="380" y="0" width="190" height="30" uuid="72d6313e-e5e9-448e-8cec-8f8684fd403d"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> <printWhenExpression><![CDATA[$P{Spreadsheet Output}.booleanValue()]]></printWhenExpression> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[label]]></text> </staticText> </band> </groupHeader> </group> <columnHeader> <band height="30"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[!($P{Spreadsheet Output}.booleanValue())]]></printWhenExpression> <staticText> <reportElement mode="Transparent" x="0" y="0" width="380" height="30" backcolor="#FFFFFF" uuid="76178472-0167-4cf2-bc84-3ae0f9ba9328"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819923a"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[resourcetype]]></text> </staticText> <staticText> <reportElement x="380" y="0" width="190" height="30" uuid="99ee507b-7497-495c-b8e5-90c4fbdaa60d"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[label]]></text> </staticText> </band> </columnHeader> <detail> <band height="35" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$V{Sheet_COUNT}.intValue()<$P{Row per Sheet}.intValue()+1]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="380" height="30" uuid="aa1f0633-0c67-4881-861f-63f8650716a2"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819923a"/> <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$F{resourcetype}.substring($F{resourcetype}.lastIndexOf( "." ))]]></propertyExpression> <printWhenExpression><![CDATA[!(!($V{Sheet_COUNT}.intValue()>1)&&$V{REPORT_COUNT}.intValue()>1&&!($P{Spreadsheet Output}.booleanValue()))]]></printWhenExpression> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression> </textField> <textField> <reportElement x="0" y="0" width="380" height="30" uuid="588b8e82-863f-4bce-8f37-1a0abadb378d"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819934b"/> <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/> <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$F{resourcetype}.substring($F{resourcetype}.lastIndexOf( "." ))]]></propertyExpression> <printWhenExpression><![CDATA[!($V{Sheet_COUNT}.intValue()>1)&&$V{REPORT_COUNT}.intValue()>1&&!($P{Spreadsheet Output}.booleanValue())]]></printWhenExpression> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression> </textField> <textField> <reportElement x="380" y="0" width="190" height="30" forecolor="#000000" uuid="714f8d01-a048-42c5-a05e-ddcb471a32ef"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{label}]]></textFieldExpression> </textField> <textField> <reportElement x="380" y="0" width="50" height="30" forecolor="#FF0000" uuid="e2cde07c-e292-4d2b-ac8e-5dcd553ec701"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <printWhenExpression><![CDATA[$P{DEBUG}.booleanValue()]]></printWhenExpression> </reportElement> <textElement> <font size="12"/> </textElement> <textFieldExpression><![CDATA[$V{Sheet_COUNT}]]></textFieldExpression> </textField> <textField> <reportElement x="430" y="0" width="50" height="30" forecolor="#FF9900" uuid="e377c949-22b3-4654-82ee-d26a53ac3f72"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <printWhenExpression><![CDATA[$P{DEBUG}.booleanValue()]]></printWhenExpression> </reportElement> <textElement> <font size="12"/> </textElement> <textFieldExpression><![CDATA[$V{COLUMN_COUNT}]]></textFieldExpression> </textField> <textField> <reportElement x="480" y="0" width="50" height="30" forecolor="#009900" uuid="68921fee-913e-4755-b960-71c22e11db4c"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <printWhenExpression><![CDATA[$P{DEBUG}.booleanValue()]]></printWhenExpression> </reportElement> <textElement> <font size="12"/> </textElement> <textFieldExpression><![CDATA[$V{REPORT_COUNT}]]></textFieldExpression> </textField> </band> </detail> </jasperReport> Report outputs Spreadsheet report Standard report Note: User can test run this sampler reprot in Jaspersoft Studio 7.9 using the resource data from JasperReports Server repository database. ======================================================================= TTC-20210515

This is the simplified version of the sample report demonstrating how to layout a non null column using a list element printed in horizontal direction. You can test the report in Jaspersoft Studio (JSS) 7.9 with any PostgreSQL database. Report detail band 1, 2 and 3 call sub dataset to construct non null columns data of a row from the report main query into separate rows to feed the list. The list gives nicely spaced layout column values based on the total number of rows per id group. Detail bands 1, 2,and 3 will print more than two rows, two rows, or one row of the listed values in the respective band. Report Template: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="demo" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="352a04b8-c2ab-45cd-85f6-5095d9cbcad5"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.parameters.override.report.hints" value="true"/> <property name="net.sf.jasperreports.export.xls.ignore.cell.border" value="false"/> <property name="net.sf.jasperreports.export.xls.show.gridlines" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.3" value="columnHeader"/> <subDataset name="Dataset1" uuid="ebbb4e6d-7792-4a6a-aa0d-f5545b55a38e"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <parameter name="id" class="java.lang.Integer"> <defaultValueExpression><![CDATA[]]></defaultValueExpression> </parameter> <parameter name="reportdata" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA[]]></defaultValueExpression> </parameter> <queryString> <![CDATA[$P!{reportdata} , selectdata as ( select c1 as cl from reportdata where c1 is not null and id = $P{id} union all select c2 as cl from reportdata where c2 is not null and id = $P{id} union all select c3 as cl from reportdata where c3 is not null and id = $P{id} ) select cl from selectdata order by cl]]> </queryString> <field name="cl" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="cl"/> <property name="com.jaspersoft.studio.field.label" value="cl"/> </field> </subDataset> <parameter name="reportdata" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["with reportdata as (select 1 as id, 'A' as c1, 'B' as c2, 'C' as c3 union all select 2 as id, 'A' as c1, 'B' as c2, null as c3 union all select 3 as id, 'A' as c1, null as c2, 'C' as c3 union all select 4 as id, null as c1, 'B' as c2, 'C' as c3 union all select 5 as id, 'A' as c1, null as c2, null as c3 union all select 6 as id, null as c1, 'B' as c2, null as c3 union all select 7 as id, null as c1, null as c2, 'C' as c3)"]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[$P!{reportdata} , selectdata as ( select id, c1 as cl from reportdata where c1 is not null union all select id, c2 as cl from reportdata where c2 is not null union all select id, c3 as cl from reportdata where c3 is not null ) select id, count(*) as idcount from selectdata group by id order by id]]> </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="idcount" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="idcount"/> <property name="com.jaspersoft.studio.field.label" value="idcount"/> </field> <title> <band height="35" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="572" height="30" forecolor="#0000FF" uuid="3cd5f27a-4487-4372-a8cd-0ed11dc338ee"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement> <font size="14" isBold="true" isItalic="true"/> </textElement> <textFieldExpression><![CDATA["Sample List Report"]]></textFieldExpression> </textField> </band> </title> <detail> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$F{idcount}.intValue()>2]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="100" height="30" forecolor="#009900" uuid="ce7d2e53-8c7a-4cc2-b3ee-1c6b5e0cd092"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA["column info "+$F{id}]]></textFieldExpression> </textField> <componentElement> <reportElement x="100" y="0" width="472" height="30" forecolor="#339900" uuid="9031a020-80db-4965-b266-c9ad9e0492ce"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="Dataset1" uuid="d50c5365-8299-4518-87d9-7db161759400"> <datasetParameter name="id"> <datasetParameterExpression><![CDATA[$F{id}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="reportdata"> <datasetParameterExpression><![CDATA[$P{reportdata}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="120"> <textField> <reportElement mode="Opaque" x="0" y="0" width="100" height="30" backcolor="#FFFF66" uuid="5ff59e58-d31e-44aa-b2a2-7733e35d626e"/> <box> <pen lineColor="#FF0000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{cl}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$F{idcount}.intValue()==2]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="100" height="30" forecolor="#009900" uuid="ce7d2e53-8c7a-4cc2-b3ee-1c6b5e0cd092"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA["column info "+$F{id}]]></textFieldExpression> </textField> <componentElement> <reportElement x="100" y="0" width="472" height="30" forecolor="#339900" uuid="9031a020-80db-4965-b266-c9ad9e0492ce"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="Dataset1" uuid="e10babcf-18b4-4ae6-9f02-1929496f1477"> <datasetParameter name="id"> <datasetParameterExpression><![CDATA[$F{id}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="reportdata"> <datasetParameterExpression><![CDATA[$P{reportdata}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="180"> <textField> <reportElement mode="Opaque" x="0" y="0" width="140" height="30" backcolor="#99FF00" uuid="5ff59e58-d31e-44aa-b2a2-7733e35d626e"/> <box> <pen lineColor="#FF0000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{cl}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$F{idcount}.intValue()<2]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="100" height="30" forecolor="#009900" uuid="ce7d2e53-8c7a-4cc2-b3ee-1c6b5e0cd092"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA["column info "+$F{id}]]></textFieldExpression> </textField> <componentElement> <reportElement x="100" y="0" width="472" height="30" forecolor="#339900" uuid="9031a020-80db-4965-b266-c9ad9e0492ce"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="Dataset1" uuid="2d604a3c-9bb3-4c40-a4c3-a9216a07ca08"> <datasetParameter name="id"> <datasetParameterExpression><![CDATA[$F{id}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="reportdata"> <datasetParameterExpression><![CDATA[$P{reportdata}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="300"> <textField> <reportElement mode="Opaque" x="0" y="0" width="260" height="30" backcolor="#FF3366" uuid="5ff59e58-d31e-44aa-b2a2-7733e35d626e"/> <box> <pen lineColor="#FF0000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{cl}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> </detail> </jasperReport> Report outputs: ========================================================= TTC-20210515

Here is the sample report to demonstrate how to layout a report row with null column suppressed using a list element printed out horizontally. You can test the report in Jaspersoft Studio (JSS) 7.9 with any PostgreSQL database. Some design considerations for the report list element: 1) Main report query provides data for tabular report layout. The tabular report uses column header and detail 1 band to print out the report content; 2) Report detail 2 band is only used as a placeholder to construct non null columns data of a row from the report main query into separate rows to feed the list. This detail 2 band calls sub dataset 1 to return a count on how many rows the sub dataset has; 3) Report detail 3, 4 and 5 bands call sub dataset 2 to construct non null columns data of a row from the report main query into separate rows to feed the list. They will lay out the nicely spaced list values based on the total number of rows gotten from step 2. Detail bands 3, 4,and 5 will print one row, two rows, or more than two rows of the listed values in the respective band. Report Template: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="demo" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="352a04b8-c2ab-45cd-85f6-5095d9cbcad5"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <subDataset name="Dataset1" uuid="de64ccee-84a6-4e0f-b5cd-11ffcd0adc02"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <parameter name="reportdata" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA[]]></defaultValueExpression> </parameter> <parameter name="id" class="java.lang.Integer"> <defaultValueExpression><![CDATA[]]></defaultValueExpression> </parameter> <queryString> <![CDATA[$P!{reportdata} , selectdata as ( select c1 as cl from reportdata where c1 is not null and id = $P{id} union all select c2 as cl from reportdata where c2 is not null and id = $P{id} union all select c3 as cl from reportdata where c3 is not null and id = $P{id} ) select count(*) from selectdata]]> </queryString> <field name="count" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="count"/> <property name="com.jaspersoft.studio.field.label" value="count"/> </field> <variable name="rowCount" class="java.lang.Long"> <variableExpression><![CDATA[$F{count}]]></variableExpression> <initialValueExpression><![CDATA[0]]></initialValueExpression> </variable> </subDataset> <subDataset name="Dataset2" uuid="36272201-2b1c-40ed-a34a-06abbad8412e"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <parameter name="reportdata" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA[]]></defaultValueExpression> </parameter> <parameter name="id" class="java.lang.Integer"> <defaultValueExpression><![CDATA[]]></defaultValueExpression> </parameter> <queryString> <![CDATA[$P!{reportdata} select c1 as cl from reportdata where c1 is not null and id = $P{id} union all select c2 as cl from reportdata where c2 is not null and id = $P{id} union all select c3 as cl from reportdata where c3 is not null and id = $P{id}]]> </queryString> <field name="cl" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="cl"/> <property name="com.jaspersoft.studio.field.label" value="cl"/> </field> </subDataset> <parameter name="reportdata" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["with reportdata as (select 1 as id, 'A' as c1, 'B' as c2, 'C' as c3 union all select 2 as id, null as c1, 'B' as c2, 'C' as c3 union all select 3 as id, 'A' as c1, null as c2, 'C' as c3 union all select 4 as id, 'A' as c1, 'B' as c2, null as c3 union all select 5 as id, null as c1, null as c2, 'C' as c3 union all select 6 as id, 'A' as c1, null as c2, null as c3 union all select 7 as id, null as c1, 'B' as c2, null as c3)"]]></defaultValueExpression> </parameter> <parameter name="List Report" class="java.lang.Boolean"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[$P!{reportdata} select * from reportdata]]> </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="c1" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="c1"/> <property name="com.jaspersoft.studio.field.label" value="c1"/> </field> <field name="c2" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="c2"/> <property name="com.jaspersoft.studio.field.label" value="c2"/> </field> <field name="c3" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="c3"/> <property name="com.jaspersoft.studio.field.label" value="c3"/> </field> <variable name="columnCount" class="java.lang.Long"> <initialValueExpression><![CDATA[0]]></initialValueExpression> </variable> <title> <band height="35" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="572" height="30" forecolor="#0000FF" uuid="3cd5f27a-4487-4372-a8cd-0ed11dc338ee"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement> <font size="14" isBold="true" isItalic="true"/> </textElement> <textFieldExpression><![CDATA["Sample ("+($P{List Report}.booleanValue()?"List":"Tabular")+" Report)"]]></textFieldExpression> </textField> </band> </title> <columnHeader> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[!$P{List Report}.booleanValue()]]></printWhenExpression> <staticText> <reportElement x="0" y="0" width="80" height="30" uuid="d153c658-55ad-4cc4-b5e2-13f52526a5c7"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="90737dca-1544-4f6e-8133-a904a0d11c85"/> </reportElement> <box> <pen lineColor="#009900"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#009900"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#009900"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#009900"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#009900"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font size="12" isBold="true"/> </textElement> <text><![CDATA[id]]></text> </staticText> <staticText> <reportElement x="80" y="0" width="170" height="30" uuid="57a686fe-d528-4ef0-a0a3-b07b7fb30e57"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="189ea8a5-32ed-4f27-8947-fd408ad7c507"/> </reportElement> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font size="12" isBold="true"/> </textElement> <text><![CDATA[c1]]></text> </staticText> <staticText> <reportElement x="250" y="0" width="160" height="30" uuid="e7375133-3d06-4a8c-b691-82857a452089"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="c9ac0dde-e301-4068-8a8a-5eb2d271b44f"/> </reportElement> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font size="12" isBold="true"/> </textElement> <text><![CDATA[c2]]></text> </staticText> <staticText> <reportElement x="410" y="0" width="162" height="30" uuid="47bf0a80-7dd4-4834-b785-25dc970009f0"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="9bd4d530-5c85-487a-82e9-b68bfe19c4d6"/> </reportElement> <box> <pen lineColor="#000000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font size="12" isBold="true"/> </textElement> <text><![CDATA[c3]]></text> </staticText> </band> </columnHeader> <detail> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[!$P{List Report}.booleanValue()]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="80" height="30" uuid="09b32c33-9335-4ed8-b6e2-c66059440ad1"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="90737dca-1544-4f6e-8133-a904a0d11c85"/> </reportElement> <box> <pen lineColor="#339900"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#339900"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#339900"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#339900"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#339900"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{id}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement x="80" y="0" width="170" height="30" uuid="1916b724-e518-435a-b234-75083eec6d26"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="189ea8a5-32ed-4f27-8947-fd408ad7c507"/> </reportElement> <box> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{c1}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement x="250" y="0" width="160" height="30" uuid="166c6732-5257-4cb5-b0a6-f230ad2c08a4"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="c9ac0dde-e301-4068-8a8a-5eb2d271b44f"/> </reportElement> <box> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{c2}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement x="410" y="0" width="162" height="30" uuid="bddcc500-aac4-46d4-8897-10e0c794ef00"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="9bd4d530-5c85-487a-82e9-b68bfe19c4d6"/> </reportElement> <box> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{c3}]]></textFieldExpression> </textField> </band> <band height="5"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <componentElement> <reportElement x="0" y="0" width="100" height="1" uuid="4bdc9995-4be8-46c5-b82c-3de8b8309eda"> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical"> <datasetRun subDataset="Dataset1" uuid="ffc23f7a-ed55-43c7-ba8a-79821ce44433"> <datasetParameter name="reportdata"> <datasetParameterExpression><![CDATA[$P{reportdata}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="id"> <datasetParameterExpression><![CDATA[$F{id}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <returnValue fromVariable="rowCount" toVariable="columnCount"/> </datasetRun> <jr:listContents height="1" width="100"/> </jr:list> </componentElement> </band> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$P{List Report}.booleanValue()&&$V{columnCount}.intValue()<2]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="100" height="30" forecolor="#009900" uuid="ce7d2e53-8c7a-4cc2-b3ee-1c6b5e0cd092"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA["column info "+$F{id}]]></textFieldExpression> </textField> <componentElement> <reportElement x="100" y="0" width="472" height="30" forecolor="#339900" uuid="9031a020-80db-4965-b266-c9ad9e0492ce"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="Dataset2" uuid="8753b6c0-0b9e-4ff0-9f05-b83ad589c338"> <datasetParameter name="reportdata"> <datasetParameterExpression><![CDATA[$P{reportdata}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="id"> <datasetParameterExpression><![CDATA[$F{id}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="300"> <textField> <reportElement mode="Opaque" x="0" y="0" width="260" height="30" backcolor="#FF3366" uuid="5ff59e58-d31e-44aa-b2a2-7733e35d626e"/> <box> <pen lineColor="#FF0000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{cl}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$P{List Report}.booleanValue()&&$V{columnCount}.intValue()==2]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="100" height="30" forecolor="#009900" uuid="ce7d2e53-8c7a-4cc2-b3ee-1c6b5e0cd092"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA["column info "+$F{id}]]></textFieldExpression> </textField> <componentElement> <reportElement x="100" y="0" width="472" height="30" forecolor="#339900" uuid="9031a020-80db-4965-b266-c9ad9e0492ce"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="Dataset2" uuid="8753b6c0-0b9e-4ff0-9f05-b83ad589c338"> <datasetParameter name="reportdata"> <datasetParameterExpression><![CDATA[$P{reportdata}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="id"> <datasetParameterExpression><![CDATA[$F{id}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="180"> <textField> <reportElement mode="Opaque" x="0" y="0" width="140" height="30" backcolor="#99FF00" uuid="5ff59e58-d31e-44aa-b2a2-7733e35d626e"/> <box> <pen lineColor="#FF0000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{cl}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> <band height="40" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$P{List Report}.booleanValue()&&$V{columnCount}.intValue()>2]]></printWhenExpression> <textField> <reportElement x="0" y="0" width="100" height="30" forecolor="#009900" uuid="ce7d2e53-8c7a-4cc2-b3ee-1c6b5e0cd092"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement verticalAlignment="Middle"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA["column info "+$F{id}]]></textFieldExpression> </textField> <componentElement> <reportElement x="100" y="0" width="472" height="30" forecolor="#339900" uuid="9031a020-80db-4965-b266-c9ad9e0492ce"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="Dataset2" uuid="8753b6c0-0b9e-4ff0-9f05-b83ad589c338"> <datasetParameter name="reportdata"> <datasetParameterExpression><![CDATA[$P{reportdata}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="id"> <datasetParameterExpression><![CDATA[$F{id}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="120"> <textField> <reportElement mode="Opaque" x="0" y="0" width="100" height="30" backcolor="#FFFF66" uuid="5ff59e58-d31e-44aa-b2a2-7733e35d626e"/> <box> <pen lineColor="#FF0000"/> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#FF0000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$F{cl}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> </detail> </jasperReport> Report Output: (Tabular layout) (with list element) ========================================================================= TTC-20210514-1944451 demo.jrxml

This is the third of the mini series of sample reports using various techniques to deal with multi-sheet report xls outputs. In this sample report, we use group break to generate separate xls sheets. We have the group header host sheet column header with the following sheet break property to make new sheets starting from the second group. <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/> The report uses overlapping field layout with identical field content but mutually exclusive print when conditions to set the xls sheet break in every group but the first one to avoid a blank sheet in the first group. Report Desidn Template <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample report with multi-sheet" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First sheet/Second sheet/Third sheet/Fourth sheet/Fifth sheet"/> <parameter name="Row per Sheet" class="java.lang.Integer"> <defaultValueExpression><![CDATA[20]]></defaultValueExpression> </parameter> <parameter name="Number of Sheets" class="java.lang.Integer"> <defaultValueExpression><![CDATA[5]]></defaultValueExpression> </parameter> <parameter name="sheet_number" class="java.lang.Integer" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{Number of Sheets}.intValue()>0?($P{Number of Sheets}.intValue()>10?10:$P{Number of Sheets}):1]]></defaultValueExpression> </parameter> <parameter name="row_number" class="java.lang.Integer" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{sheet_number}.intValue()*20]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[select * from jiresource limit $P!{row_number}]]> </queryString> <field name="id" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="id"/> <property name="com.jaspersoft.studio.field.label" value="id"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="version" class="java.lang.Integer"> <property name="com.jaspersoft.studio.field.name" value="version"/> <property name="com.jaspersoft.studio.field.label" value="version"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="name"/> <property name="com.jaspersoft.studio.field.label" value="name"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="parent_folder" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="parent_folder"/> <property name="com.jaspersoft.studio.field.label" value="parent_folder"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="childrenfolder" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="childrenfolder"/> <property name="com.jaspersoft.studio.field.label" value="childrenfolder"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="label" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="label"/> <property name="com.jaspersoft.studio.field.label" value="label"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="description" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="description"/> <property name="com.jaspersoft.studio.field.label" value="description"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="resourcetype" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="resourcetype"/> <property name="com.jaspersoft.studio.field.label" value="resourcetype"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="creation_date" class="java.sql.Timestamp"> <property name="com.jaspersoft.studio.field.name" value="creation_date"/> <property name="com.jaspersoft.studio.field.label" value="creation_date"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="update_date" class="java.sql.Timestamp"> <property name="com.jaspersoft.studio.field.name" value="update_date"/> <property name="com.jaspersoft.studio.field.label" value="update_date"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <group name="Sheet" isStartNewPage="true"> <groupExpression><![CDATA[($V{REPORT_COUNT}.intValue()-1)/$P{Row per Sheet}.intValue()]]></groupExpression> <groupHeader> <band height="30"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement x="0" y="0" width="380" height="30" uuid="5ba31fcd-322b-404a-9b62-55fa9aab5681"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819923a"/> <printWhenExpression><![CDATA[!($V{REPORT_COUNT}.intValue()>1)]]></printWhenExpression> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[resourcetype]]></text> </staticText> <staticText> <reportElement x="0" y="0" width="380" height="30" uuid="5ba31fcd-322b-404a-9b62-55fa9aab5681"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819923a"/> <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/> <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()>1]]></printWhenExpression> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[resourcetype]]></text> </staticText> <staticText> <reportElement x="380" y="0" width="190" height="30" uuid="72d6313e-e5e9-448e-8cec-8f8684fd403d"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[label]]></text> </staticText> </band> </groupHeader> </group> <detail> <band height="30" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="380" height="30" uuid="aa1f0633-0c67-4881-861f-63f8650716a2"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="47c21803-dbeb-4438-ad94-76a56819923a"/> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression> </textField> <textField> <reportElement x="380" y="0" width="190" height="30" forecolor="#000000" uuid="714f8d01-a048-42c5-a05e-ddcb471a32ef"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7d69739f-d694-4ba7-b151-662656dabdff"/> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{label}]]></textFieldExpression> </textField> </band> </detail> </jasperReport> Report output: Multisheet xls Note 1) User can test run this report using JasperRerports Server repository data source; 2) The sheet break is injected into the subreport using the "break before row" property (refero to http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.break.before.row) =========================================================================================== TTC-20210510

This is the forth of the mini series of sample reports using various techniques to deal with multi-sheet report xls outputs. In this sample report, we will break each sub report into separate xls sheets. Unlike in the previous samples where the sheets were simply separated based on the report page break using the one page per sheet xls property, we use the following property to place it at a strategic location to set up a sheet break when exporting the report into xls output. <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/> The reason for the choice of using this sheet break option is that sub reports most likely will have multiple pages of report content therefore we cannot reply on page break to start new sheet in such case or will have to face unintended sheet separation in the sub report. The "break before row" property should be set up at the first visible element in the report xls output file to start the new sheet. In this sample report, it is set in the sub report, on the first row of the report data feeding the first column in report detail band. Report templates: Main report <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample report with multi-sheet" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.3" value="columnHeader"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.3" value="*"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.4" value="columnHeader"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <subDataset name="ListDataset" uuid="07f1ee71-b1eb-4d3d-9bf2-cd080412ca60"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo.xml"/> <queryString language="SQL"> <![CDATA[select resourcetype , count(resourcetype) as resourcetypecount from jiresource group by resourcetype]]> </queryString> <field name="resourcetype" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="resourcetype"/> <property name="com.jaspersoft.studio.field.label" value="resourcetype"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="resourcetypecount" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="resourcetypecount"/> <property name="com.jaspersoft.studio.field.label" value="resourcetypecount"/> </field> </subDataset> <parameter name="Max per Sheet" class="java.lang.Integer" isForPrompting="false"> <defaultValueExpression><![CDATA[50]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[select * from ( select resourcetype , count(resourcetype) as resourcetypecount from jiresource group by resourcetype ) t where resourcetypecount > $P{Max per Sheet}]]> </queryString> <field name="resourcetype" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="resourcetype"/> <property name="com.jaspersoft.studio.field.label" value="resourcetype"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="resourcetypecount" class="java.lang.Long"> <property name="com.jaspersoft.studio.field.name" value="resourcetypecount"/> <property name="com.jaspersoft.studio.field.label" value="resourcetypecount"/> </field> <variable name="resourcetype" class="java.lang.String" resetType="None"> <initialValueExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></initialValueExpression> </variable> <columnHeader> <band height="30"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()==0]]></printWhenExpression> <staticText> <reportElement x="0" y="0" width="350" height="30" uuid="35d2384e-4628-4e1b-8b43-0a8944dcbf55"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement> <font size="14" isBold="true" isItalic="true"/> </textElement> <text><![CDATA[resourcetype]]></text> </staticText> <staticText> <reportElement x="350" y="0" width="220" height="30" uuid="6ffff2b0-2fa0-464f-b054-914a12b9c60d"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbfc86c5-577f-49d0-970f-e6bcbacf421b"/> </reportElement> <textElement textAlignment="Right"> <font size="14" isBold="true" isItalic="true"/> </textElement> <text><![CDATA[resourcetypecount]]></text> </staticText> </band> </columnHeader> <detail> <band height="30" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()==1]]></printWhenExpression> <componentElement> <reportElement x="0" y="0" width="570" height="30" uuid="50180da2-1bd9-4d4c-b934-3d5ce47b2856"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical"> <datasetRun subDataset="ListDataset" uuid="12da974d-be42-459e-9495-28dd9ef417fd"> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="570"> <textField> <reportElement x="0" y="0" width="350" height="30" uuid="8706f920-24a9-404f-a7a9-500dbb8de522"> <property name="com.jaspersoft.studio.unit.width" value="px"/> <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA["SUMMARY"]]></propertyExpression> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression> </textField> <textField> <reportElement x="350" y="0" width="150" height="30" forecolor="#FF0000" uuid="be0cfdef-cea6-4c69-96df-3974993dbf51"> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement> <font size="10"/> </textElement> <textFieldExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></textFieldExpression> </textField> <textField> <reportElement x="500" y="0" width="70" height="30" uuid="cf085817-b14d-442f-864f-0b9ec3e998e3"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$F{resourcetypecount}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> <band height="31"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <break> <reportElement x="0" y="0" width="100" height="1" uuid="87282198-6f09-43af-8625-d51ad00114bd"> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> </break> <subreport> <reportElement x="0" y="1" width="570" height="30" uuid="a4d1d961-fad1-4fd4-bbea-2552722841fe"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <subreportParameter name="resourcetypename"> <subreportParameterExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></subreportParameterExpression> </subreportParameter> <subreportParameter name="Limit"> <subreportParameterExpression><![CDATA[$F{resourcetypecount}.intValue()%$P{Max per Sheet}.intValue()]]></subreportParameterExpression> </subreportParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <subreportExpression><![CDATA["sample report with multi-sheet-sub.jasper"]]></subreportExpression> </subreport> </band> </detail> </jasperReport> Subreport <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample report with multi-sheet" pageWidth="572" pageHeight="752" columnWidth="572" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.3" value="columnHeader"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="com.jaspersoft.studio.unit." value="pixel"/> <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/> <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/> <parameter name="resourcetypename" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["DashboardModelResource"]]></defaultValueExpression> </parameter> <parameter name="Limit" class="java.lang.Integer" isForPrompting="false"> <defaultValueExpression><![CDATA[50]]></defaultValueExpression> </parameter> <parameter name="resourceTypeNameLike" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["'%"+$P{resourcetypename}+"'"]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[select name , label , resourcetype from jiresource where resourcetype like $P!{resourceTypeNameLike} limit $P!{Limit}]]> </queryString> <field name="name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="name"/> <property name="com.jaspersoft.studio.field.label" value="name"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="label" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="label"/> <property name="com.jaspersoft.studio.field.label" value="label"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <field name="resourcetype" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="resourcetype"/> <property name="com.jaspersoft.studio.field.label" value="resourcetype"/> <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/> </field> <variable name="resourcetype" class="java.lang.String" resetType="None"> <initialValueExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></initialValueExpression> </variable> <columnHeader> <band height="30"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement x="0" y="0" width="350" height="30" uuid="35d2384e-4628-4e1b-8b43-0a8944dcbf55"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement> <font size="14" isBold="true" isItalic="true"/> </textElement> <text><![CDATA[resourcetype]]></text> </staticText> <staticText> <reportElement x="350" y="0" width="220" height="30" uuid="52fdfa67-390d-457a-9613-3654afb1cc58"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="368cd102-03c6-4cd4-9761-f3bf209f6a21"/> </reportElement> <textElement> <font size="14" isBold="true" isItalic="true"/> </textElement> <text><![CDATA[label]]></text> </staticText> </band> </columnHeader> <detail> <band height="35" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="350" height="30" uuid="a04724ac-064b-46e3-aa03-8785540b974f"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/> <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$P{resourcetypename}]]></propertyExpression> <printWhenExpression><![CDATA[!($V{REPORT_COUNT}.intValue()>1)]]></printWhenExpression> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression> </textField> <textField> <reportElement x="0" y="0" width="350" height="30" uuid="fa11098a-38c1-43af-951e-6e3e56120dc1"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$P{resourcetypename}]]></propertyExpression> <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()>1]]></printWhenExpression> </reportElement> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression> </textField> <textField> <reportElement x="350" y="0" width="220" height="30" uuid="a4698581-2936-4d45-aa9b-818c9e613f0e"> <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="368cd102-03c6-4cd4-9761-f3bf209f6a21"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{label}]]></textFieldExpression> </textField> </band> </detail> </jasperReport> Report output: Multisheet xls PDF Note 1) User can test run this report using JasperRerports Server repository data source; 2) The sheet break is injected into the subreport using the "break before row" property (refero to http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.break.before.row) 3) The sheet name is injected through report element sheet name property (refer to http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasper...). 4) Report columns from main and sub report are suppressed in xls output using the method as discussed in this wiki FAQ posting. =========================================================== TTC-20210510

This is the first of the mini series of sample reports using various techniques to deal with multi-sheet report xls outputs. This sample report demonstrates how to use report data group breaks to generate multip-sheet xls report output files in a tabular report design. Report template: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="report55617" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="85ebfc4c-73f9-47ad-a5cb-d4e2bc39299e"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/> <queryString language="SQL"> <![CDATA[select u.username, r.rolename, o.tenantname from jiuser u inner join jiuserrole j on j.userid = u.id inner join jirole r on r.id = j.roleid inner join jitenant o on o.id = u.tenantid order by 2, 3, 1]]> </queryString> <field name="username" class="java.lang.String"/> <field name="rolename" class="java.lang.String"> <property name="net.sf.jasperreports.export.xls.sheet.name" value="$F{rolename}"/> </field> <field name="tenantname" class="java.lang.String"/> <group name="role" isStartNewPage="true"> <groupExpression><![CDATA[$F{rolename}]]></groupExpression> </group> <background> <band splitType="Stretch"/> </background> <title> <band splitType="Stretch"/> </title> <pageHeader> <band splitType="Stretch"/> </pageHeader> <columnHeader> <band height="30" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement x="0" y="0" width="210" height="30" uuid="25508467-d654-4807-ad17-a31a0a4bc977"> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[rolename]]></text> </staticText> <staticText> <reportElement x="210" y="0" width="150" height="30" uuid="24d6bb28-17fc-44ba-b959-448deda71a12"> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[tenantname]]></text> </staticText> <staticText> <reportElement x="360" y="0" width="150" height="30" uuid="a6544345-cac6-4689-84b2-3a9e4a0198fb"> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[username]]></text> </staticText> </band> </columnHeader> <detail> <band height="30" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="210" height="30" uuid="85006485-57d5-4b24-979d-f4c1212363c7"> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$F{rolename}]]></propertyExpression> </reportElement> <textFieldExpression><![CDATA[$F{rolename}]]></textFieldExpression> </textField> <textField> <reportElement x="210" y="0" width="150" height="30" uuid="df489ae8-967d-48bf-9bbe-05d0c64df431"> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{tenantname}]]></textFieldExpression> </textField> <textField> <reportElement x="360" y="0" width="150" height="30" uuid="05960d09-8181-4842-b8db-3e63656cc98f"> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{username}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band splitType="Stretch"/> </columnFooter> <pageFooter> <band splitType="Stretch"/> </pageFooter> <summary> <band splitType="Stretch"/> </summary> </jasperReport> Report output: Note 1) User can use JasperReports Server repository data source and test this report in the Studio; 2) This report uses the following two report sheet configuration properties to break the data into separate sheet with custom sheet names: http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.one.page.per.sheet http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.sheet.name Please review sample report for multi-sheet xls report output using a table element to continue the discovery. ======================================================================================= TTC-20210508

This is the second of the mini series of sample reports using various techniques to deal with multi-sheet report xls outputs. This is a simple sample report to demonstrate how to break xls files into separate sheets based on report data group break in a table element. Report template: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="report56117table" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="11c460d3-d1c7-4102-9fbc-5b640a5de06e"> <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <style name="Table_TH" mode="Opaque" backcolor="#F0F8FF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <subDataset name="Dataset1" uuid="963178aa-7087-4b7c-9ac7-ab9dd82aee6b"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepoPostgres2"/> <queryString> <![CDATA[select u.username, r.rolename, o.tenantname from jiuser u inner join jiuserrole j on j.userid = u.id inner join jirole r on r.id = j.roleid inner join jitenant o on o.id = u.tenantid order by 2, 3, 1]]> </queryString> <field name="username" class="java.lang.String"/> <field name="rolename" class="java.lang.String"/> <field name="tenantname" class="java.lang.String"/> <group name="Group1" isStartNewColumn="true"> <groupExpression><![CDATA[$F{rolename}]]></groupExpression> </group> </subDataset> <queryString language="SQL"> <![CDATA[select 1]]> </queryString> <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 height="500" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <componentElement> <reportElement x="0" y="0" width="570" height="60" uuid="efd53bcf-1b3c-4d85-a4db-004f4e61a2ac"> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd"> <datasetRun subDataset="Dataset1" uuid="1317b399-5e1c-488e-9a6b-1da3bce1e70e"> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:column width="210" uuid="740b583b-f94a-43df-a7bf-5c673b354fbe"> <jr:columnHeader style="Table_CH" height="30"> <staticText> <reportElement x="0" y="0" width="210" height="30" uuid="b688b9a7-cdc0-47f3-bac8-bd99f11b9182"/> <text><![CDATA[rolename]]></text> </staticText> </jr:columnHeader> <jr:detailCell style="Table_TD" height="30"> <textField> <reportElement x="0" y="0" width="210" height="30" uuid="689c1f09-3023-4a11-a9d0-2c910fbd3293"> <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$F{rolename}]]></propertyExpression> </reportElement> <textFieldExpression><![CDATA[$F{rolename}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="148" uuid="208c9f71-27a2-45f6-bcfd-1267f18cc790"> <jr:columnHeader style="Table_CH" height="30"> <staticText> <reportElement x="0" y="0" width="148" height="30" uuid="a78a3a41-c56a-41ae-a154-d881beccbdce"/> <text><![CDATA[tenantname]]></text> </staticText> </jr:columnHeader> <jr:detailCell style="Table_TD" height="30"> <textField> <reportElement x="0" y="0" width="148" height="30" uuid="ab755f16-e2e4-4562-be1f-98fd29b34eec"/> <textFieldExpression><![CDATA[$F{tenantname}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="212" uuid="a2d75333-ccbb-432e-abf4-564d5b152685"> <jr:columnHeader style="Table_CH" height="30"> <staticText> <reportElement x="0" y="0" width="212" height="30" uuid="9c49f493-14fb-4a64-b3a9-37ff89dcb327"/> <text><![CDATA[username]]></text> </staticText> </jr:columnHeader> <jr:detailCell style="Table_TD" height="30"> <textField> <reportElement x="0" y="0" width="212" height="30" uuid="3f090fe6-08b3-4ef8-b404-9f2f66c809ce"/> <textFieldExpression><![CDATA[$F{username}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> </jr:table> </componentElement> </band> </summary> </jasperReport> Report output: Note 1) User can test run this report using JasperRerports Server repository data source; 2) The sheet name is injected through report element sheet name property (refer to http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.sheet.name). ======================================================================================= TTC-20210508

The easiest way to layout a report in horizontal direction is to use a crosstab *. However, since crosstab needs columns, rows, and measures to construct the data structure and make the calculation, users may need to reconstruct their report data content to meet the requirement. Suppose we have a report requirement as the following: Report CSV data: "P", "C" "Bob", 1 "Bob", 2 "Bob", 3 "Carol", 1 "Carol", 2 Report layout requirement: To make this work with crosstab, we need to provide column information in the report data structure for crosstab to categorized the data and make the calculation. The approach is to sequentialize the user's data and synchronize those data among different users ("Bob" and "Carol"). The modified report CSV data: "P", "C","-" "Bob", 1,"A" "Bob", 2,"B" "Bob", 3,"C" "Carol", 1,"A" "Carol", 2,"B" With the change, we can construct a crosstab report, then manually remove tally information and column display from the crosstab to make the final cut. Report design template: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample report" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="sampleData4William-crosstab"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/> <property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First sheet/Second sheet/Third sheet/Fourth sheet/Fifth sheet"/> <style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab_CG" mode="Opaque" backcolor="#BFE1FF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab_CT" mode="Opaque" backcolor="#005FB3"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab_CD" mode="Opaque" backcolor="#FFFFFF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab 1_CH" mode="Opaque" backcolor="#F0F8FF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab 1_CG" mode="Opaque" backcolor="#BFE1FF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab 1_CT" mode="Opaque" backcolor="#005FB3"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab 1_CD" mode="Opaque" backcolor="#FFFFFF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <queryString language="csv"> <![CDATA[]]> </queryString> <field name="P" class="java.lang.String"/> <field name="C" class="java.lang.Integer"/> <field name="-" class="java.lang.String"/> <summary> <band height="234"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <crosstab> <reportElement positionType="Float" x="0" y="70" width="570" height="20" uuid="06aa263f-8e83-464d-8994-6fd92739f688"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/> <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab_CH"/> <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab_CG"/> <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab_CT"/> <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab_CD"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> </reportElement> <crosstabDataset> <dataset resetType="Report"/> </crosstabDataset> <rowGroup name="P" width="60" totalPosition="End"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{P}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="Crosstab_CH"> <textField> <reportElement x="0" y="0" width="60" height="20" uuid="67a80877-17f0-488c-8792-27982f454074"/> <textFieldExpression><![CDATA[$V{P}]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents mode="Opaque" style="Crosstab_CT"/> </crosstabTotalRowHeader> </rowGroup> <columnGroup name="-" height="0" totalPosition="End"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{-}]]></bucketExpression> </bucket> <crosstabColumnHeader> <cellContents mode="Opaque" style="Crosstab_CH"> <property name="com.jaspersoft.studio.unit.height" value="px"/> </cellContents> </crosstabColumnHeader> <crosstabTotalColumnHeader> <cellContents mode="Opaque" style="Crosstab_CT"/> </crosstabTotalColumnHeader> </columnGroup> <measure name="C_MEASURE" class="java.lang.Integer"> <measureExpression><![CDATA[$F{C}]]></measureExpression> </measure> <crosstabCell width="60" height="20"> <cellContents mode="Opaque" style="Crosstab_CD"> <textField isBlankWhenNull="true"> <reportElement x="0" y="0" width="60" height="20" uuid="f4fecb82-5612-4011-b0b3-1738982f2625"/> <textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="0" height="20" columnTotalGroup="-"> <cellContents mode="Opaque" style="Crosstab_CT"> <property name="com.jaspersoft.studio.unit.width" value="px"/> </cellContents> </crosstabCell> <crosstabCell width="60" height="0" rowTotalGroup="P"> <cellContents mode="Opaque" style="Crosstab_CT"/> </crosstabCell> <crosstabCell width="0" height="0" rowTotalGroup="P" columnTotalGroup="-"> <cellContents mode="Opaque" style="Crosstab_CT"> <property name="com.jaspersoft.studio.unit.height" value="px"/> </cellContents> </crosstabCell> </crosstab> <crosstab> <reportElement x="0" y="0" width="570" height="60" uuid="6fa523c9-19ae-4065-97d3-bc4aa3917715"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/> <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab 1_CH"/> <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab 1_CG"/> <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab 1_CT"/> <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab 1_CD"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <crosstabDataset> <dataset resetType="Report"/> </crosstabDataset> <rowGroup name="P" width="60" totalPosition="End"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{P}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="Crosstab 1_CH"> <textField> <reportElement x="0" y="0" width="60" height="20" uuid="6a57bb58-af0c-40d3-b7d2-316f24f14267"/> <textFieldExpression><![CDATA[$V{P}]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents mode="Opaque" style="Crosstab 1_CT"> <staticText> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="ba16acc2-19fc-498f-907f-c938bc381208"/> <text><![CDATA[Total P]]></text> </staticText> </cellContents> </crosstabTotalRowHeader> </rowGroup> <columnGroup name="-" height="20" totalPosition="End"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{-}]]></bucketExpression> </bucket> <crosstabColumnHeader> <cellContents mode="Opaque" style="Crosstab 1_CH"> <textField> <reportElement x="0" y="0" width="60" height="20" uuid="6a2b4024-dfee-4ecb-8c10-9ee992fbc476"/> <textFieldExpression><![CDATA[$V{-}]]></textFieldExpression> </textField> </cellContents> </crosstabColumnHeader> <crosstabTotalColumnHeader> <cellContents mode="Opaque" style="Crosstab 1_CT"> <staticText> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7abb08b3-b43f-4bc2-bd00-0e6265b7c144"/> <text><![CDATA[Total -]]></text> </staticText> </cellContents> </crosstabTotalColumnHeader> </columnGroup> <measure name="C_MEASURE" class="java.lang.Integer"> <measureExpression><![CDATA[$F{C}]]></measureExpression> </measure> <crosstabCell width="60" height="20"> <cellContents mode="Opaque" style="Crosstab 1_CD"> <textField isBlankWhenNull="true"> <reportElement x="0" y="0" width="60" height="20" uuid="4c0f667c-7bde-421b-9f38-d8e9b13c8714"/> <textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" columnTotalGroup="-"> <cellContents mode="Opaque" style="Crosstab 1_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="c3cc795a-9bf4-4205-89df-122e24360ec0"/> <textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="P"> <cellContents mode="Opaque" style="Crosstab 1_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="a187a42f-4aaf-4c85-a6bd-a3ddd2dfde5d"/> <textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="P" columnTotalGroup="-"> <cellContents mode="Opaque" style="Crosstab 1_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="764b5862-1264-402e-8f72-a97a62c1e3e0"/> <textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> </crosstab> </band> </summary> </jasperReport> Report output: Note: A crosstab is a table showing the relationship between two or more variables and in general is for quantitative data analysis. Merely laying out report data in a certain direction, such as the use case in this sample, is not the best choice to use crosstab with JasperReports engine (JRL). This sample report only demonstrates how this can be achieved using the existing product feature but as users may discover the approach is convoluted and require manual trial and error modification to achieve the design goal. I do not recommend this approach but to use other methods to meet the design requirement. =========================================================================== TTC-20210508

The list element is very useful to help lay out the report data in horizontal direction. When using a CSV dataset, users need to provide a separate CSV file to feed the list through sub datasource because it is not consumable for reuse by Jasperreports engine (JRL). In this sample report, it demonstrates how to use CVS data to layout a simple report in horizontal direction. Report Requirement: Report CSV Data: for main "smapleData": "P", "C" "Bob", 1 "Bob", 2 "Bob", 3 "Carol", 1 "Carol", 2 for sub dataset "sample-3.csv": "Bob", 1 "Bob", 2 "Bob", 3 "Carol", 1 "Carol", 2 Report Design Template: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample report" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="smapleData4William"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/> <property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First sheet/Second sheet/Third sheet/Fourth sheet/Fifth sheet"/> <subDataset name="children" uuid="4f4009a1-920d-4d8c-a9ec-e3ba1e32b79e"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="smapleData"/> <parameter name="Parent" class="java.lang.String"> <defaultValueExpression><![CDATA["Bob"]]></defaultValueExpression> </parameter> <queryString language="csv"> <![CDATA[]]> </queryString> <field name="COLUMN_0" class="java.lang.String"/> <field name="COLUMN_1" class="java.lang.Integer"/> <filterExpression><![CDATA[$F{COLUMN_0}.equals($P{Parent})]]></filterExpression> <group name="P"> <groupExpression><![CDATA[$F{COLUMN_0}]]></groupExpression> </group> </subDataset> <queryString language="csv"> <![CDATA[]]> </queryString> <field name="P" class="java.lang.String"/> <field name="C" class="java.lang.Integer"/> <group name="Parent"> <groupExpression><![CDATA[$F{P}]]></groupExpression> </group> <group name="parent"> <groupExpression><![CDATA[$F{P}]]></groupExpression> <groupHeader> <band height="35"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="100" height="30" uuid="f2965bd8-6e58-4440-a239-40523d430609"/> <box> <topPen lineWidth="1.0"/> <leftPen lineWidth="1.0"/> <bottomPen lineWidth="1.0"/> <rightPen lineWidth="1.0"/> </box> <textElement textAlignment="Center"/> <textFieldExpression><![CDATA[$F{P}]]></textFieldExpression> </textField> <componentElement> <reportElement x="110" y="0" width="450" height="30" uuid="d1043b1a-534b-4a95-8f45-413814480e24"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.grid.JSSGridBagLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="children" uuid="5e5cfdc1-dafc-4399-a146-77de6324cddd"> <datasetParameter name="Parent"> <datasetParameterExpression><![CDATA[$F{P}]]></datasetParameterExpression> </datasetParameter> <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRCsvDataSource(new File("sample-3.csv"))]]></dataSourceExpression> </datasetRun> <jr:listContents height="30" width="30"> <textField> <reportElement x="0" y="0" width="30" height="30" uuid="d567e4a7-b45d-464c-afbb-2a7866a13180"/> <box> <pen lineColor="#0099FF"/> <topPen lineWidth="1.0"/> <leftPen lineWidth="1.0"/> <bottomPen lineWidth="1.0"/> <rightPen lineWidth="1.0"/> </box> <textElement textAlignment="Center"/> <textFieldExpression><![CDATA[$F{COLUMN_1}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> </groupHeader> </group> </jasperReport> Report Output: ================================================= TTC-20210508-1942019

Here is the sample report design to demonstrate how to lay out user's report data horizontally and vertically. We assume the audiences of this sample report already have basic knowledge on how JasperReports works and preferably know how Java works. Design Requirement: Report Template for Vertical Layout - Using Simple Tabular layout <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample for John Doe" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/> <property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First sheet/Second sheet/Third sheet/Fourth sheet/Fifth sheet"/> <queryString language="SQL"> <![CDATA[select 'Bob' as "P", 1 as "C" union all select 'Bob' as "P", 2 as "C" union all select 'Bob' as "P", 3 as "C" union all select 'Carol' as "P", 1 as "C" union all select 'Carol' as "P", 2 as "C"]]> </queryString> <field name="P" class="java.lang.String"/> <field name="C" class="java.lang.Integer"/> <group name="Parent"> <groupExpression><![CDATA[$F{P}]]></groupExpression> <groupHeader> <band height="65"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="5" width="180" height="30" uuid="0d53ba86-feb2-4b24-8d71-71433260c965"> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{P}]]></textFieldExpression> </textField> <staticText> <reportElement x="60" y="35" width="120" height="30" forecolor="#339900" uuid="bd633b29-c34b-4027-983e-50e89b3c8e66"> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <textElement> <font isBold="true"/> </textElement> <text><![CDATA[Dependent]]></text> </staticText> </band> </groupHeader> </group> <detail> <band height="30" splitType="Stretch"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="60" y="0" width="120" height="30" uuid="df90f71c-cf42-4c64-ac29-d443e6fe7d0d"/> <textFieldExpression><![CDATA[$F{C}]]></textFieldExpression> </textField> </band> </detail> </jasperReport> Report Output Report Template for Horizontal Layout - Using List Element <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 --> <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="sample for Jane Doe" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/> <property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First sheet/Second sheet/Third sheet/Fourth sheet/Fifth sheet"/> <subDataset name="children" uuid="4f4009a1-920d-4d8c-a9ec-e3ba1e32b79e"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <parameter name="Parent" class="java.lang.String"> <defaultValueExpression><![CDATA["Bob"]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[select 'Bob' as "P", 1 as "C" union all select 'Bob' as "P", 2 as "C" union all select 'Bob' as "P", 3 as "C" union all select 'Carol' as "P", 1 as "C" union all select 'Carol' as "P", 2 as "C"]]> </queryString> <field name="P" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="P"/> <property name="com.jaspersoft.studio.field.label" value="P"/> </field> <field name="C" class="java.lang.Integer"> <property name="com.jaspersoft.studio.field.name" value="C"/> <property name="com.jaspersoft.studio.field.label" value="C"/> </field> <filterExpression><![CDATA[$F{P}.equals($P{Parent})]]></filterExpression> <group name="P"> <groupExpression><![CDATA[$F{P}]]></groupExpression> </group> </subDataset> <queryString> <![CDATA[select 'Bob' as "P", 1 as "C" union all select 'Bob' as "P", 2 as "C" union all select 'Bob' as "P", 3 as "C" union all select 'Carol' as "P", 1 as "C" union all select 'Carol' as "P", 2 as "C"]]> </queryString> <field name="P" class="java.lang.String"> <property name="com.jaspersoft.studio.field.name" value="P"/> <property name="com.jaspersoft.studio.field.label" value="P"/> </field> <field name="C" class="java.lang.Integer"> <property name="com.jaspersoft.studio.field.name" value="C"/> <property name="com.jaspersoft.studio.field.label" value="C"/> </field> <group name="Parent"> <groupExpression><![CDATA[$F{P}]]></groupExpression> </group> <group name="parent"> <groupExpression><![CDATA[$F{P}]]></groupExpression> <groupHeader> <band height="35"> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="100" height="30" uuid="f2965bd8-6e58-4440-a239-40523d430609"/> <box> <topPen lineWidth="1.0"/> <leftPen lineWidth="1.0"/> <bottomPen lineWidth="1.0"/> <rightPen lineWidth="1.0"/> </box> <textElement textAlignment="Center"/> <textFieldExpression><![CDATA[$F{P}]]></textFieldExpression> </textField> <componentElement> <reportElement x="110" y="0" width="450" height="30" uuid="d1043b1a-534b-4a95-8f45-413814480e24"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.grid.JSSGridBagLayout"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="px"/> </reportElement> <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Horizontal"> <datasetRun subDataset="children" uuid="b93c0ae2-b3ac-4c66-9c86-8f49774be58f"> <datasetParameter name="Parent"> <datasetParameterExpression><![CDATA[$F{P}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:listContents height="30" width="30"> <textField> <reportElement x="0" y="0" width="30" height="30" uuid="f5d76872-ee07-4fd0-9e8a-8a0081114690"/> <box> <topPen lineWidth="1.0" lineColor="#009900"/> <leftPen lineWidth="1.0" lineColor="#009900"/> <bottomPen lineWidth="1.0" lineColor="#009900"/> <rightPen lineWidth="1.0" lineColor="#009900"/> </box> <textElement textAlignment="Center"/> <textFieldExpression><![CDATA[$F{C}]]></textFieldExpression> </textField> </jr:listContents> </jr:list> </componentElement> </band> </groupHeader> </group> </jasperReport> Report Output =================================================== TTC-20210508-1942019

Users can enter their debug properties in Jasperreports Server (JRS) Log Settings web UI. If experiencing "unexpected" debug information in the server log file, users should check this log setting in addition to the log4j2.properties file. Users can also run the Diagnostic Report to see what log settings this installation has. This information is in the LogSettings section of the report. Unfortunately, once the custom logging properties are is set in the JRS Log web UI , they cannot be removed from the web UI. We already have a product feature enhancement internally referenced as JS-34343 filed to address the issue and to provide the ability to remove an added logger to JRS Log Settings page in web UI. Until the enhancement is implemented, user need to login to JRS repository database and manually delete the entry from jiprofileattribute table. ========================================================== TTC-20210508

A user can get the following message when attempting to download TIBCO Jaspersoft products from the eDelivery system under their support portal account if the user has not been granted the access to the eDelivery system. The eDelivery system is designed to help customers manage their own access and not rely on TIBCO to make updates for them. User administrator who is the principal contact in the account has the ability to add other users in their TIBCO eDelivery accounts. To grant an user access to the Delivery system, the user administrator should log into the site at edelivery.tibco.com, then click on “MY Account” tab in the upper right corner. Select the “User Management” link, fill in the required fields, and click the blue “Create” button. When the green successful notice appears, the added user is enabled and ready to download product from the eDelivery system. Notice TIBCO discourages the use of group Email aliases for gaining access to the eDelivery site as it presents login password issues and export issues on who is downloading the software.====================================== TTC-20210110

The ProblemUser is complaining that an Ad Hoc that contains four filters that "upon applying the custom filter expression, there's no results are being pulled even though the data does exist." The AnalysisAfter closely examining the ad hoc queries from today's meeting, we came to the conclusion that user's issue relates to the boolean logic used in the ad hoc view filter. Suppose we have the following records in the database: Row A: Column 1 - "PERS_FIRST_NAME" : 'Aaron'; Column 2 - "CRSE_TTL" : '(12 hr) Class 4' Column 3 - "COMPLETION_IND" : 'Y' Row B: Column 1 - "PERS_FIRST_NAME" : 'Aaron'; Column 2 - "CRSE_TTL" : '(18 hr) Class 6' Column 3 - "COMPLETION_IND" : 'Y' If user has the custom filter (A and B and C and D), where filter A is "CRSE_TTL" = '(12 hr) Class 4'; filter B is "COMPLETION_IND" = 'Y'; filter C is "CRSE_TTL" = '(18 hr) Classr 6'; filter D is "COMPLETION_IND" = 'Y' Therefore, the selection criteria for the view is: where ( ("CRSE_TTL" = '(12 hr) Class 4') and ("COMPLETION_IND" = 'Y') and ("CRSE_TTL" = '(18 hr) Class 6') and ("COMPLETION_IND" = 'Y') ) Does the above two rows meet the selection criteria? The answer is NO. We cannot have either row A or row B Column 2 - "CRSE_TTL" contain both '(12 hr) Class 4' and '(18 hr) Class 6' at the same time in that row, correct? The SuggestionTo include those records in two separate rows, our selection criteria must be (A and B) OR (C and D) Since condition B and D in essence reference the same condition ("COMPLETION_IND" = 'Y'), using the distributive laws, the above custom filter can be simplified as (A or C) and B * We can drop filter D for that matter. Therefore the above will generate the following where clause for the ad hoc filter that meets your requirement: ( ("CRSE_TTL" = '(12 hr) Class 4 ' ORz ("CRSE_TTL" = '(18 hr) Class 6') } AND ("COMPLETION_IND" = 'Y') An even simpler solution is to use "is one of" filters to select "CRSE_TTL". filter A: "CRSE_TTL" is one of ('(12 hr) Class 4', '(18 hr) Class 6'); filter B is "COMPLETION_IND" = 'Y' The generated ad hoc filter SQL query where clause will be: ("CRSE_TTL" in ( '(12 hr) Class 4', '(18 hr) Class 6' )) AND ("COMPLETION_IND" = 'Y') TTC-20201129-1898491

The ProblemUser is getting the following error when running a job from the TIBCO Jaspersoft® ETL on a remote server. [toc]2020-10-01 14:23:26 ERROR JobGenerator - Failed to login to CommandLine : java.lang.NullPointerException at org.talend.commandline.util.RepositoryHandler.initRepository(RepositoryHandler.java:217) at org.talend.commandline.util.RepositoryHandler.init(RepositoryHandler.java:73) at org.talend.commandline.command.CommandProcessor.init(CommandProcessor.java:108) at org.talend.commandline.command.CommandProcessorSwitch.caseInitRemoteCommand(CommandProcessorSwitch.java:129) at org.talend.commandline.client.util.CommandAbstractSwitch.doSwitch(CommandAbstractSwitch.java:60) at org.talend.commandline.command.CommandConsumer.executeCommand(CommandConsumer.java:57) at org.talend.commandline.command.CommandConsumer.execute(CommandConsumer.java:35) at org.talend.commandline.mode.ServerCommandLine$CommmandConsumerRunnable.run(ServerCommandLine.java:139) at java.lang.Thread.run(Thread.java:748) The AnalysisIt seems something is missing or incorrect in the Jaspersoft ETL installation or configuration that causes this kind of error. Further investigate found Cmdline startup is giving the error message below when trying to do initRemote to the Local TAC: !ENTRY org.talend.commandline 1 0 2020-10-07 15:16:19.677 !MESSAGE Initialized to http://localhost:8080/org.jasper.administrator successfully with user: xxx@yyyy.com FAILED at Wed Oct 07 15:16:19 EDT 2020 exception : org.talend.commandline.client.command.CommandStatusWrapException: java.lang.RuntimeException: The service org.talend.core.ui.branding.IBrandingService has not been registered. at org.talend.core.GlobalServiceRegister.getService(GlobalServiceRegister.java:148) at org.talend.core.repository.model.RepositoryFactoryProvider.getAvailableRepositories(RepositoryFactoryProvider.java:58) at org.talend.core.repository.model.RepositoryFactoryProvider.getRepositoriyById(RepositoryFactoryProvider.java:118) at org.talend.commandline.util.RepositoryHandler.initRepository(RepositoryHandler.java:216) at org.talend.commandline.util.RepositoryHandler.init(RepositoryHandler.java:73) at org.talend.commandline.command.CommandProcessor.init(CommandProcessor.java:108) Upon verifying the Jaspersoft ETL license, it turns out that the user is using Jaspersoft ETL license on Jaspersoft ETL BigData Extended version product. The SuggestionPlease download and deploy the "regular" Jaspersoft ETL 7.3.1 product and have it installed to use it with the user's current Jaspersoft ETL 7.3.1 license to create the new jobs. TTC-20201129-1897658

The ProblemUser is getting an "invalid resource" error when editing a domain join after switching its data source. The SuggestionTo avoid rebuilding the entire domain because of this, you may want to try as the following: Writing down the domain joins that has reference errors; Creating a new domain using the table fields from the same database schema; Creating joins that were in errors in the original (modified) domain; Export domain schema directly from the Domain Designer web UI (you should not get any error since it is freshly created); Review this new domain schema and find the new reference id for those joins; Edit your original domain schema and replace old join reference id with the new one found in the above step; Load the modified original domain and test it. Depending upon the complexity of the domain database structure, this approach might not always work but it is worth giving a try if the amount of errors is manageable. TTC-20201129-1899133

The ProblemUser has existing domains using SQL Server data source with DB schema as "dbo". They need them to work with their Oracle databases with identical tables. The user asks whether "there a way I can edit the domain schema without having to remove the object and reinsert all the values again". The SuggestionTo meet your requirement to switch domain data source from SQL to oracle, you need to do the following: from TIBCO JasperReports® Server web UI repository web flow, make a copy of the original domain into another repository location; export the copied domain into a zip file; extract "schema_xml.data" file out of the zip file; rename "schema_xml.data" file to "{anyname}.xml", such as lens_domain.xml edit "lens_domain.xml" file and make a global change of the db prefix to reflect your Oracle DB schema; save the change; from JasperReports Server repository web flow, edit the copied domain, upload your modified "lens_domain.xml" file, then open it in domain designer to make sure there's no errors in all designer web UIs; save the domain change (OK, then submit); create new ad hoc view using this copied and modified domain to make sure there's no errors; save the ad hoc view and report into JasperReports​​​​​​​ Server repository; run this newly created ad hoc view report using the modified domain; Make sure there's no errors. It is important to always make a copy of the domain that you are intending to modify, test it, then make changes applied to the existing domain. Should error occur in any of the above steps, you can evaluate the error and make corrections accordingly. Make sure to clear ad hoc cache if the changes are involving ad hoc view and its report. You need to login as superuser, then go to Manage - Server Settings - Ad Hoc Cache - to use the "Clear ALL" button at the lower left corner of the query listing panel to flush out query result data from previous access. TTC-20201129-1899133

The ProblemUser has reported that he is getting the "corrupted license" error after applying what he thought a TIBCO JasperReports® Server license. The AnalysisUser had mistakenly deployed a TIBCO Jaspersoft® ETL license into a JasperReports® Server installation. After reapplying the correct JasperReports Server license to correct this mishap, the user is getting the "corrupted license" error. It appears the Jaspersoft ETL license that this user had tried in this JasperReports Server deployment accidentally triggered the license protection mechanism which requires the system administrator's action to reset. The ResolutionPlease do as the following to reset the license protection in user's JasperReports Server deployment: Stop apache-tomcat web application server; Look for .jrlic, .jrslic, and .jslic files on the server and delete them. Most likely the user will not be able to find the .jrlic file on the server and that is Oaky. These are hidden license files created when JasperReports Server is accessing the license content. The location of those files are under the system's user.home directory. For example, on Windows, it may be in C:/users/JohnDoe. In Linux, it could be in /usr depending on how the system variable %USER_HOME% is defined. !!! PLEASE MAKE SURE DO NOT TOUCH .jrsks AND .jrsksp FILES UNDER THE SAME LOCATION. !!! Those are for server keystore encryptions. If removed, user will not be able to start up JasperReports Server. Restart apache-tomcat web application server; Clear browser cache and test JasperReports Server again. TTC-20201129-1899429

The ProblemUser is reporting that they "are trying to connect Talend to a git repository hosted in a self-hosted Bitbucket that is only accessible via SSH on port 7999. We've followed the setup instructions here and are getting a 504 Gateway Timeout when we try to connect. We are able to clone the repository using the same user and SSH key on this same host, so we don't believe it's an issue with Bitbucket. I think the issue may be that we are connecting on a non-standard SSH port - 7999". The AnalysisThe Talend team had reviewed the issue and indicated that "their product currently only supports Git + SSH on port 22" and they have filed a ticket to address this port reassignment issue. The Work AroundThe Talend team has found a solution to work around this limitation. Here is the reference that they had provided to specify the port for the host in the configuration file: https://serverfault.com/questions/218256/specify-ssh-port-for-git To be specific, the possible solution is: Modify "ssh config" to specify the port for each host instead of being a global setting. Host asdf.com Port 1234 Host github.com User git Hostname github.com Port 22 TTC-20201129-1891646