Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Output to Excel

  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Default Output to Excel

    Sorry if this has been asked before - I did spend time searching first.

    I notice that when output is to Excel (and, for that matter, HTML but not so big a problem) all of the tables generated to position headers follow down to the body of the data - creating an enormous number of columns in the final spreadsheet.

    Is there some way to have the header sections' formatting close up all those columns and "start again" for the tabular data so that so many columns are not generated?

    Thanks!

    Bill

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    What version of the reporting engine are you using?
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Nov 2007
    Posts
    7

    Default Version Number

    1.6.0.GA.1000 is what displays in the login page - is this what you mean?

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Yes, this helps. In that case, you are not using the Flow-Engine, so I'm moving the whole thread to the classic-section.

    For your problem:

    The number of columns corresponds directly to your report layout's various x positions. The HTML and Excel exports translate all the absolute element positions into table-cells. To get simpler tables, reduce the number of distinct x positions. (In other words: Align your elements so that elements that should go into the same column start all start at the same x position and have the same width.)

    Our table-layout algorithm is explained in more detail here:
    http://www.sherito.org/2007/03/19/1174335540000.html

    For the Excel-export, you can force the start of a new sheet by simply adding a manual pagebreak to your report bands. The engine will translate that into a new sheet with its own table-layout.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    Nov 2007
    Posts
    7

    Default

    Thanks for your quick response!

    That explains the technical aspect - now for dealing with upper management...

    - Would this issue go away with the newer version of Pentaho?
    - Does upgrading provide an automatic conversion of existing reports into the newer format?
    - Is there any reason why a technology group which just adopted Pentaho (within 6 months) would be using a "Classic" version rather than the current version (e.g. is the current version less than 6 months old)?

    Thanks again!

  6. #6
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Well, classic does not mean "out-fashioned". The Flow-Engine is our upcoming new reporting engine, but as posted in big-fat letters in the forum: This one is still Alpha-Grade software.

    Pentaho is an OpenSource company, so you will see projects appear as soon as we completed the first version. These early versions are by no means meant for production use, but making these versions public gives us very early feedback that helps us to direct the development and attracts other developers to work with us.

    The "classic" in Pentaho-Reporting Classic refers to the way the engine works. This reporting engine in a banded reporting engine and uses the same ancient principles as COBOL or the almost forgotton RPG language of the old mainframe ages. This reporting style emphasizes the data side of the reporting task - so you usually start with a large table and then chop the table into groups and detail sections.

    The flow-engine (once finished) reverses the process and starts by describing the desired output. The Flow-Process then pulls in the data it needs to create the documents the user requested. In a year or (more likely) two, the Flow-Engine will then replace the classic engine inside the Pentaho-Platform, as the Flow-Process is a superset of the classic report process.

    All future versions of the reporting engines will always be able to process the old report definitions.

    Our current development efforts concentrate on adding the missing features to make the Pentaho Reporting Classic Engine a true version 1.0 (in the OpenSource sense of being feature complete, not the commercial sense of just not crashing anymore. In a commercial development process this engine would probably have been labeled as a version 4.0 or 5.0 already.)

    Your original posting referred to problems with the table-export which created too many columns. The algorithm to convert free-form content into tables is independent of the reporting engine. Tables have certain limitations, and unless we change the universe itself, there is nothing we can do about it. (I filed a bug-request to a couple of deities, but none responded so far ) So the only way to reduce the complexity of the generated tables is to properly align your elements in the report.

    The reasons for sticking with the classic version are simple: (1) It is stable. (2) It is supported by our UI tools. (3) It is supported by the Pentaho Platform. (4) The Flow-Engine is a development version and not suitable for production use outside of a very limited scope.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7
    Join Date
    Nov 2007
    Posts
    7

    Default

    Hehe - well, you were partly correct on my columns problem. Wish I could post a picture, but it is probably proprietary and would get me in trouble - but to describe it briefly the header section of the first page really has multiple header sections (legends, disclaimers, report titles, sub-titles, select criteria) all coming before the plain ol' column headers. It is all this extra stuff which I wish there were some way to force a clean break after so that their columns did not extend down through the data. This is where, in HTML terms, I was wishing you could end the "Header Table" and begin the "Data Table." Powerbuilder (I'm dating myself, and yes I did Fortran) handled this quite well once upon a time...

    In HTML such closing of the table would not create a problem in the page display as you could simply center it all across 100% page width. It might not be as "pretty" when output to Excel, but it would certainly eliminate nearly 100 columns in our particular reports.

    Well, we will look forward to the flow engine when it passes Beta.

  8. #8
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    You *can* do this for Excel by setting a manual pagebreak after your header. This forces the report-engine to start a new sheet which then will have a much cleaner layout.

    In HTML, the same facility exists, but the Pentaho-Platform does not expose the feature. But HTML usually is only used for viewing anyway, and the browser should hide the complex table quite well. And any user who really looks at the source of a generated page probably is used to horrible code
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  9. #9
    Join Date
    Nov 2007
    Posts
    7

    Default

    Quote Originally Posted by Taqua View Post
    You *can* do this for Excel by setting a manual pagebreak after your header. This forces the report-engine to start a new sheet which then will have a much cleaner layout.
    Yes, but then our programmers would have to change the behind-the-scenes code to add logic to change the setting based on whether the user selects HTML; PDF; or HTML. Unfortunately our budget door is closed. Good to know for next time, though.

    In HTML, the same facility exists, but the Pentaho-Platform does not expose the feature. But HTML usually is only used for viewing anyway, and the browser should hide the complex table quite well. And any user who really looks at the source of a generated page probably is used to horrible code
    That would be me, but I promise not to suggest that anyones' code is UGLY...

    Looks like we've reached the end of this road. Thanks for all your help, much appreciated.

  10. #10
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Oh, you can do this with style-expression inline. No need to hire a programmer.

    Set the following formula for the pagebreak style-key:
    Code:
    =ISEXPORTTYPE("table/excel")
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.