Sage 100

We have a client that upgraded from Sage 100 2104 PREM to 2018 PREM PU1. The Financial Reports from the GL Reports menu do not show any values, nor do they show line items. Never seen anything like this and thought I would run it by the group before contacting Sage. Other GL reports are fine. Seems to be an issue for all existing financial reports as well as any newly generated reports. Anyone else perform a 2018 PREM upgrade and were Financial Reports OK? TIA!

I had the same exact issue after installing S100 2018 Premium + PU1 on the ABX company and before performing a migration from the client's previous version. Support said they won't look into it because they are too busy, but they will if it occurs after the migration. We haven't done the migration yet, but it looks like they have a ""feature failure"". This is the Support Ticket I opened with Sage Support: 8006564874.

I'm just through the test migration stage of a Premium upgrade, and tried printing a standard balance sheet and it was blank. Looking at the work tables, the detail table is blank. Looks like a bug in how they populate the work tables with data.

I just posted the issue to Sage City (with a case opened already, I don't need to waste time with that...). https://sagecity.na.sage.com/support_communities/sage100_erp/f/sage-100-core-financial-modules/106448/financial-reports-blank-on-2018-premium-pu1

I am not sure if this is related but I had an issue with financial statements in 2012 converting from 4.5 ProvideX to 4.5 SQL. G/L financials duplicating cash account on Balance Sheet. Determined to be a bug in Sage data conversion from ProvideX to SQL. SY_SQLOptimize.m4t in the MAS_SYSTEM directory has an option that sets the work file Gl_financialreportdetailwrk to be set up as a pvx file. With out this option set to 9 it creates the work file in the SQL tempdb. The tempdb files cannot be read and rewritten. If a record is rewritten it creates a duplicate record. It doesn't overwrite the original. The SY_SQLOptimize.m4t file was blank.

@RebekahVeuger along the lines of what @DougHiggs just mentioned, as a test you can try the following workaround. I'm very familiar with how SY_SQLOptimize works on Premium and what Doug says is very relevant: It this works, the Financials will print slower than normal but also mean Sage would eventually provide the correct ""optimization cache"" records so you could have accuracy and speed. 1. Make a backup of SY_SQLOptimize.m4t in MAS_System folder! 2. DFDM SY_SQLOptimize.m4t and find all records where ObjectName = GL_FinancialReport_rpt 3. Delete these records. 4. Try printing Financials again. 5. If you get same blank data, then restore SY_SQLOptimize.m4t from the backup in Step 1 6. If you get data, client can verify it then let them know slow financials are better than no financials.

@DougHiggs and @AlnoorCassim - I will give that a try this evening and report back! I did connect with Sage and it is a reported issue, initially captured by Sage on Friday 1/19/18, but no KB yet. Sage engineering has it, so a solution should be fourth coming.

** EDIT ** - In my response, changed ""all 3 records"" below to ""all records"" because there's probably more than 3 to delete that start with GL_FinancialReport_rpt

@AlnoorCassim What in the world would we do without you? You're amazing! I reported this issue to Sage on the 19th, got a call back that it was an issue & was in engineering, but didn't have a work around. It ONLY happens when you apply PU1 to a 2018 Premium install. BUT after following your directions, the reports are fixed! I deleted only one at a time & it seems to be specifically the ""GL_FINANCIALREPORT_RPT.GL_FINANCIALREPORTDETAILWRK"" record in DFDM. Once that one record is deleted, the reports work as expected.

Good detective work Sam. You deleted what we call the ""9"" record for the OptimizationType field which always points to a worktable and is normally a good thing to have in there. I have 1 warning which I'll share with you via a recent experience. One of our reports on Premium/SQL intentionally didn't have a ""9"" rec but duplicate records appeared on the report output. It was because the report program was WRITEing to the worktable twice in a nested loop. In SQL TempDB, Sage's worktable doesn't get created with a primary key so if you try to UPDATE the same record you already wrote to, a new one gets INSERTed instead (now you have dupes). So, instead of adding the ""9"" rec (which would've also fixed the dupe problem), we fixed the program to WRITE once only at the end of the loop. My point is on the Financials, since the ""9"" rec is gone now, you really want the client to verify their Financials are correct, that there are no duplicates (in case the Sage programmer does a 2nd WRITE themselves) or missing data. I think you'll find the data will be OK but just verify.

Reply on Sage City: ""Hi Kevin, We are aware of the issue and should have a hot fix shortly. John Nichols Sage

@AlnoorCassim you were correct, deleting the '9' record does indeed make the financials incorrect. Any other suggestions on how to get around this? Sage doesn't have anything for me yet. If I re-install the original v2018, it fixes financials, but breaks payroll.

Could you just take SY_SQLOptimize.m4 from 2018 and copy it into 2018.1? I don't know if this is possible so just wondering aloud.

My original thought had been to delete ALL records stating with GL_FinancialReport_rpt in SY_SQLOptimize. If that doesn't work, restore the file and delete all but the 9 record.

@WayneSchulz I did try that, but it didn't change anything. @AlnoorCassim, I'm sorry, I didn't realize removing all 4 would change that. Thank you so much, removing all 4 did fix the reports without making them incorrect!