XLSX configurations ignored when using grouped by content

[Michael L.]

Hi,

I have a report that uses some grouping to show titles and totals in it.  It works great in PDF, however, in Excel it seems to ignore the instructions to merge cells and also to merge sheets.  I end up with merged cells if no data output to them (I can get around this by putting in (unknown) or something like this to get around it), but it also splits the data up into sheets, and in a seemingly random manner (ie. grouping A and part of B will be on sheet 1, grouping B is continued on sheet 2...).  When running the report to Excel, we set the following properties:

       CALL fgl_report_configureXLSXDevice (NULL,  -- fromPage
                                            NULL,  -- toPage
                                            FALSE, -- removeWhitespace
                                            NULL,  -- ignoreRowAlignment
                                            NULL,  -- ignoreColumnAlignment
                                            NULL,  -- removeBackgroundImages
                                            TRUE   -- mergePages
                                           )
       CALL fgl_report_setXLSXMergeCells(FALSE)
and this does work most of the time, but this might be the first time we try it with groupings though.

I have attached a screenshot of my report structure as well.  Does anyone know any way to get around the sheet splitting issue?  Also, is there something with using groupings that prevents XLSX output to behave properly?

Thanks,

Michael Laporte

[Alex G.]

Hi Michael,
it might be that you are experiencing bug GRE-661 "Excel output: Empty columns are ignored making vertical alignment corrupted". It has been fixed and is available in the 3.10.07 maintenance release.
To be more certain I would need to see the PDF output and perhaps a screen shot of the misaligned Excel output.
Did you have a look the chapter in the documentation that deals with page merging in Excel output (http://4js.com/online_documentation/fjs-gst-manual-html/#gst-topics/c_grw_xls_single_sheet.html) to rule out issues with group headers and such?

Best regards,

Alex Geller
 

[Michael L.]

Hi Alex,

Thanks for the reply!

I had a look at issue GRE-661, but I don't think it applies as my columns all seem to be lined up correctly and empty cells are being merged, rather than shifted.

I had a look at that documentation page as well, seems like misalignment and other problems might be due to using the Group totals and such in the Excel Output, which I tried to avoid altogether, keeping the Excel output as simple as possible (ie. column headings and column data only).  I have the VisibilityCondition set on the PDFTypeOfWarrant group page as well as the GroupSubTotals group page.  I think the problem might be that I am trying to do two different things depending on the output, groupings and totals in PDF, and just a straight table for Excel.

I have attached a sample xlsx file (zipped) to show what is happening.  The PDF is coming out correctly and is too large to attach, but essentially it shows all groupings with titles and totals along with ReportTotals at the end.  The XLSX is nearly correct, it just merges the blank cells on the last column, and is splitting the data up into 8 sheets.

I also attached an expanded view of the report structure (as much as I can show on one screen).

Thanks,

-Mike

[Alex G.]

Hi Michael,
thanks for the additional information. I am a little at loss why the cells are still merged and sheets are not merged when you have called the corresponding API calls.
Are you sure that the program flow passes by these calls? If yes, then I suggest that you send us a GREDATAFILE and the .4rp to support and we have a look at that here.

Best regards,

Alex

[Michael L.]

Thanks Alex.  I will do what you say and send the information to support.

Cheers!

-Mike

[Michael L.]

Hi Alex,

After 4js support confirmed they could get it to work properly in GRE 3.10 and then in GRE 3.00, I went back to our code and reviewed it more carefully, and you were right Alex, we weren't calling the appropriate XLSX API functions as I thought we were.  I made the adjustments necessary to make those calls and it is working correctly.  I'll have to be more attentive when reviewing our code and logic next time  :).

Cheers,

-Mike

[Alex G.]

Hi Michael,

thanks for the getting back. Glad that I had the right of it and could help.

Best regards,

Alex