Jump to content
Changes to the Jaspersoft community edition download ×
  • Optimize Table Reports For Data Oriented Output


    mgeise

    Requirement

    Both standard and Ad Hoc reports can be used to produce long lists of data which could be exported to excel or csv for further processing. The html presentation of the data should be layout oriented. Requirements for a data oriented output are:

    • no split cells
    • no merged cells (excel can't sort accross cells)
    • no repeating header and footer
    • no export of the title band

    This you can achieve without changing the JasperReports Server source code. It can be solved by applying a few changes to these files:

    • state2jrxml.js which creates a JRXML out of the ad hoc configuration
    • optionally jasperreports.properties which contains global settings for the JasperReports configuration
    • best practices for report development

    The state2jrxml changes were applied in JasperReports Server 3.1. The file changed in newer versions so be careful when applying the changes.

    Where to find the files

    In the JasperReports Server application directory navigate to WEB-INFclassesjasperreports.properties and WEB-INFadhocstate2jrxml.js.

    Optimizing Standard Reports

    - columns don't overlap page width (design) - detail band property isSplitAllowed="false" - add for text fields the property stretchType="RelativeToTallestObject" You would also need to add all the custom properties mentioned above.

    (Optional) Setting Data Export Options Globally

    If you want to set the data oriented output by default you can achieve this by changing the JasperReports settings in JasperReports Server. Therefore you would add these settings to the jasperreports.properties files in WEB-INFclasses

    net.sf.jasperreports.export.xls.exclude.origin.band.1=title

    net.sf.jasperreports.export.xls.exclude.origin.band.2=summary

    net.sf.jasperreports.export.xls.exclude.origin.band.3=pageHeader

    net.sf.jasperreports.export.xls.exclude.origin.band.4=pageFooter

    net.sf.jasperreports.export.xls.collapse.row.span=false

    net.sf.jasperreports.export.xls.remove.empty.space.between.columns=true

    As custom property on report level (both for ad hoc and for standard reports) you need to set:

    <property name="net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.name" value="columnHeader"/>

    Configuring Ad Hoc Reports

    Avoiding Split Cells In Excel

    By default an ad hoc report is set to A4 or Letter format (portrait or landscape). If the column width exceeds the page width a split of the column which overlapped happens. To avoid this I changed the page width to the width of the content plus the margins.

    @@ -125,7 +125,7 @@ function getReport(state) {

    columnCount="1"

    printOrder="Vertical"

    orientation={ state.pageOrientation }

    -         pageWidth={ state.pageWidth  }

    +         pageWidth={ contentWidth + state.horizontalMargin + state.horizontalMargin }

    pageHeight={ state.pageHeight }

    columnWidth={state.getContentWidth()}

    columnSpacing="0"

     

    Another split is caused because of the word "Totals" in the summary band. This gets generated as soon as a sum function is added to the report. The easiest way to solve it is simply removing the text filed from the summary band.

    @@ -699,25 +708,11 @@ function addTablesSummaries(report, state) {

    key="frame-3">

    <property name="net.sf.jasperreports.export.pdf.tag.tr" value="full"/>

    </reportElement>

    -                    <staticText>

    -                        <reportElement

    -                            style="TableSummaries"

    -                            x={GROUP_INDENT}

    -                            y="0"

    -                            width={totalLabelWidth}

    -                            height={standardHeaderBandHeight}

    -                            key="staticText-1"

    -                            stretchType="RelativeToBandHeight">

    -                        <property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>

    -                        </reportElement>

    -                        <text>Totals:</text>

    -                    </staticText>

    {summaries}

    </frame>

    </band>

    </summary>;

    //summaryBand.band.frame.reportElement += invisibox();

    -    summaryBand.band.frame.staticText.reportElement += invisibox();

    report.appendChild(summaryBand);

    }

    Another split can be caused when a column is affected by a page break. Avoid this to set the detail band property isSplitAllowed to false.

    Avoiding Merged Cells In Excel

    There are a couple of merged cells: header and footer bands, summary, title etc. Also there is a merge if a cell value contains line breaks which happens e. g. when the cell length is less then the content length. To avoid this simply set the stretchType for the detail fields.

    @@ -613,6 +621,7 @@ function getDetailField(x, column) {

    y="0"

    width={column.getWidth()}

    height={DETAIL_BAND_HEIGHT}

    +            stretchType="RelativeToTallestObject"

    key="textField">

    <property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>

    </reportElement>

    General Settings For CSV And Excel Export

    I've added custom properties which configure the behaviour of certain bands while exporting.

    @@ -149,10 +149,18 @@ function getAdHocProperties(state)

    var props = <property name="com.jaspersoft.ji.adhoc" value="1"/>;

    props += <property name="net.sf.jasperreports.export.pdf.tagged" value="true"/>;

    props += <property name="net.sf.jasperreports.export.pdf.tag.language" value="EN-US"/>;

    -    if (state.printsChart()) {

    -        props += <property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/>

    -        props += <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="false"/>

    -    }

    +    props += <property name="net.sf.jasperreports.export.xls.exclude.origin.band.1" value="title"/>

    +    props += <property name="net.sf.jasperreports.export.xls.exclude.origin.band.2" value="summary"/>

    +    props += <property name="net.sf.jasperreports.export.xls.exclude.origin.band.3" value="pageHeader"/>

    +    props += <property name="net.sf.jasperreports.export.xls.exclude.origin.band.4" value="pageFooter"/>

    +    props += <property name="net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1" value="columnHeader"/>

    +    props += <property name="net.sf.jasperreports.export.xls.collapse.row.span" value="false"/>

    +    props += <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>

    +    props += <property name="net.sf.jasperreports.export.csv.exclude.origin.band.csvSummary" value="summary"/>

    +    props += <property name="net.sf.jasperreports.export.csv.exclude.origin.band.1" value="title"/>

    +    props += <property name="net.sf.jasperreports.export.csv.exclude.origin.band.2" value="pageFooter"/>

    +    props += <property name="net.sf.jasperreports.export.csv.exclude.origin.keep.first.band.1" value="columnHeader"/>

    +

    return props;

    }

    Further Reading

    • The JasperReports Configuration Reference available here
    • The JasperReports Ultimate Guide, Element Properties

     


    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...