How To Export to a custom Excel Report Question

SBM 11.3

Is there a way I can export to a custom Excel template? Bonus points if I can do it from multiple sources.

For instance, I'd like say, six lines populated from a particular aux table with certain fields in certain places, then at line 10, start the listing report with its eight columns of data I want from the main table.

Is such a thing possible in BPM? Or would I have to use an external reporting tool (SSDT et al) to do this?
Responses (4)
  • Accepted Answer

    Tuesday, October 09 2018, 05:26 PM - #Permalink
    The only thing I can think of without using a 3rd party reporting tool - and not even sure if this would work, or be practical, or suit your needs - would be to create separate listing reports in SBM. Then in Excel, use the Data tab to make connections to the various listing reports, then use lookups, etc to display the data from seperate reports on one worksheet. But doing all that - I would probably go with a 3rd party reporting tool.:)
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, October 20 2018, 12:29 PM - #Permalink
    This is a little more "iffy" and is just an idea ... I haven't tried it

    In Excel, layout the worksheet the way you want it to look. In each cell where you want an Aux table value, enter

    Where "USR_MY_AUX_TBL" is the name of your aux table, "1" is replaced with the record number (by ID or Value Display Format) , and "FLDNAME" is the DBName of the field.

    Starting at the first row where you want multiple rows from your main table, in the first and last of the 10 worksheet cells. put the following. Leave the intervening 8 cells blank
    $SCRIPT(PopExcelMain, USR_MY_MAIN_TBL)

    Save the file (as default xlsx as a backup) then save it again as a "Single File Web Page (*.mht, *.mhtml)". This ".mht" file will be your working file.

    Now you'll need a couple scripts. The first ("PopExcelAux") will expect 3 params: the name of the aux table, the record to read and the name of the field to read and output. These are the 3 parameters in the first $SCRIPT param above. All the script does is read that field and output it:
    Dim vrec, strFld
    Set vrec=Ext.CreateVarRecord( Ext.TableId(Shell.Params,Item("param1")))
    Call vrec.Read(Shell.Params,Item("param2"))
    Call vrec.GetFieldValue(Shell.Params,Item("param3"),strFld)
    Call Ext.WriteStream( strFld)

    To output multiple rows from the main table, you'll need to edit the ".mht" file generated by Excel. Find the section with the "PopExcelMain". Take the "tr" and "/tr" elements that start and end the main table data and each of the "td" and "/td" elements that formats one of the 10 fields. Cut this from the .mht file and save it. The 2nd script will need to output this text once for each row from your main table.

    The second script will need to read all the proper records from your main table and output (using Ext.WriteStream) a "chunk" of text that duplicates the "tr", "td", "/td" and "/tr" removed from the ".mht" file. This script is obviously much more complex.

    You can display the Excel using the URL format described in the AppScript guide for "HTML Template Direct Access Context"
    The reply is currently minimized Show
  • Accepted Answer

    Friday, October 26 2018, 03:09 PM - #Permalink
    If you are ready to invest a bit of time into this, you could create a C++ DLL for ModScript (or AppScript), wrap the C++ library SimpleXlsxWriter entrypoints, and generate the Excel spreadsheet via data and logic in ModScript. Yes, this would take some effort, but sometimes you just have to have a certain Excel document and you have to have it updated frequently, so it may be worth the effort.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, November 22 2018, 11:07 PM - #Permalink
    After the report is arranged and the names of every one of your parameters and quantities of every one of your markers are characterized, you can construct the Excel-based layout. This includes two or three stages. In the primary picture to one side, we have utilized example numbers to make the report in Excel in precisely the organization in which we need it to show up. We join text styles, hues, fringes, computations, and even diagrams which are ascertained off our or marker numbers when created.
    Telstra BigPond Phone Number
    The reply is currently minimized Show
Your Reply