Sage 100

​Customer has Sage Standard Version 2017.1.  SI consolidation reports were written in V4.5 and when Sage upgraded to V2017, I exported then reimported them into current version.  His many reports are very large, and each report has about 20 tabs, so saving each tab individually isn't practical.  His Excel is V2016 and I've already put down the fix for that known issue (KB ID 71541 fix).

He wants to export (not use the email feature) each workbook with static values to send to his outside accountant, who does not own Sage 100. (Getting the workbook to have static values and not formulas so the outside accountant is the goal; it doesn't matter how we get there.) Sage told me to do that with the Distribution feature (which is very cool, except it doesn't work for him!).  With his reports, it doesn't export the reports cleanly.  For example, many hidden columns in column A-D show up on his export in rows 28-40 in columns I-Q.  But there's MANY more issues than that.

I went thru Level 1 support and Level 2 support with Sage.  They are bumping it to Engineering.  Still, I was wondering if anyone has suggestions that fixes this issue or other suggestions to get him his workbooks with static values, not formulas.  (We also tried the export within the designer, but that exports with formulas)

I have sample good report and exported bad report attached.  I hid the descriptions because the customer's name is contained in them.

Thank you.
Roberta



------------------------------
Roberta Chase
Enterprise Software Consultant--Sage 100, Crystal Reports, Sage Intelligence, FAS, Starship
CompuData, Inc.
Philadelphia, PA 19154
215-969-1000, ext. 277
------------------------------

In case anyone else finds this issue... It might be related to writing the SI report with a prior to Excel 2016 version and then upgrading to Excel 2016, but we're not exactly sure of the cause.

The issue happens when there is a column filter on the row.  Rows were filtered out for when the total amount in a particular column = 0, a perfectly reasonable filter.  However, this made a mess of the export.

Quick fix:  remove the excel data filter, export the report, reapply the filter. 

A better fix:  In SI, choose the report, check the "Show Advanced" box. Choose Run Add-Ins, choose the ellipsis, and then choose specify new,  Fill out the sheet name, the column to check (that is the column that you want to filter on), choose exact match Y/N, choose to remove or hide rows that are being filtered.  There are other options, but those are the minimum.  Save.   Now the report runs from SI the way you want and you can then just use the BI tools to export static values.

------------------------------
Roberta Chase
Enterprise Software Consultant--Sage 100, Crystal Reports, Sage Intelligence, FAS, Starship
CompuData, Inc.
Philadelphia, PA 19154
215-969-1000, ext. 277
------------------------------