Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only
Back to discussions
Expand all | Collapse all

Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

  • 1.  Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Sun February 14, 2021 10:00 PM
    MAM 7.6.1.2:

    I have a report that is emailed to me on a schedule as an Excel spreadsheet.

    I've noticed that the data in the spreadsheet gets split up individual sheets (just like the pages in the original BIRT report).

    Is there a way to consolidate the sheets in the spreadsheet so that the data is in a single Excel table?

    Thanks.



    #Maximo
    #AssetandFacilitiesManagement


  • 2.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Mon February 15, 2021 02:23 AM
    Hi,

    Export as .xls instead of xlsx and you will get what you want


    kind regards
    agron

    ------------------------------
    Agron Basha
    ------------------------------

    Original Message


  • 3.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Mon February 15, 2021 05:31 PM
    Edited by System Admin Wed March 22, 2023 11:48 AM

    Yeah as mentioned below, set export file type to .xls spreadsheet and you'll get what you want but in the older excel format.

    IBM are aware of the issue, but say it's a 'feature':

    why-do-xlsx-export-report-pages-open-multiple-excel-spreadsheet-tabs-instead-single-spreadsheet-page 



    ------------------------------
    Richard Jackson
    Asset Information and Systems Manager
    Massey University
    Palmerston North
    New Zealand
    ------------------------------

    Original Message


  • 4.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Mon February 15, 2021 08:04 PM

    Ah yes, the old "meant to do that" routine. Classic.

    Thanks.

    Image


    Original Message


  • 5.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Tue February 16, 2021 02:12 AM
    The good thing although with this approach is that you can choose how you want the file to look.
    I have never came across a use case where you actually want to split it into numerous sheets.

    ------------------------------
    Agron Basha
    ------------------------------

    Original Message


  • 6.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Tue February 16, 2021 02:55 PM

    Yeah I understand an export to say .pdf format retaining the page layout design format, but when opting for an excel spreadsheet format you typically want a single page of just data.   

    The splitting to different tabs behaviour essentially rules out using the .xlsx option, which is disappointing in 2021.  

    To fit on a single tab, reports have to be generated in the older .xls spreadsheet format, which results in much larger files sizes and also spills onto a new tab after the older maximum limit of 65536 rows. 



    ------------------------------
    Richard Jackson
    Asset Information and Systems Manager
    Massey University
    Palmerston North
    New Zealand
    ------------------------------

    Original Message


  • 7.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Wed February 17, 2021 09:57 AM
    I added a parameter to choose the XLSX format. 
    Then in the initialize add 
    if(params["Excel"].value ==1) {
    var table =reportContext.getReportRunnable()designHandle.getDesignHandle().findElement("main");
    table.setProperty("pageBreakInterval",0);
    }

    select the table
    In properties tab, General Name: give the table the name main.

    #Maximo
    #AssetandFacilitiesManagement


  • 8.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Wed February 17, 2021 10:27 AM
    Hello Wes

    Good input ! One question though. Does this option also allow you to control the formatting of any QBR reports? These are often used by our users to do simple data exports. XLSX is the preferred file format due to compatibility and no of rows.

    Thanks, Ruud


    Original Message


  • 9.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Wed February 17, 2021 01:13 PM
    It's frustrating given how well the standard list/table download works now, which appears as 'pure' .xlsx really well straight from Maximo (V7.6.1.2)

    ------------------------------
    Richard Jackson
    Asset Information and Systems Manager
    Massey University
    Palmerston North
    New Zealand
    ------------------------------

    Original Message


  • 10.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Wed February 17, 2021 03:10 PM
    Sorry I haven't tried with QBR reports.

    #AssetandFacilitiesManagement
    #Maximo


  • 11.  RE: Scheduled report to Excel: Consolidate multiple sheets/pages into single table?

    Posted Wed February 17, 2021 02:09 PM
    We actually have a use case where the XLSX multiple page format worked well.  We have a scheduled Shift Report that has multiple sections of data.  The first page is a summary of all of the data on the report, then the subsequent pages have the detail data on separate tabs.  Our report writer also found a way in BIRT to name the tabs to make it easier for the user to find what they want to drill into.

    ------------------------------
    Tom Peterson
    Steel Services Specialist
    TimkenSteel Corp.
    Canton OH
    330-471-7481
    ------------------------------

    Original Message