Question Status

Suggested Answer
Sheila Ochoa asked a question on 20 Mar 2013 8:47 AM

If I run a report 'Cross-Reference by Journal Entry' it runs fine the first time on a brand new option.  I am not restricting it by anything and it pulls in all accounts showing nothing for the beginning account number and nothing for the ending account number.  However, once I click save and save the option name, when I run the report again, the ending account number has y's in it with a few dots about the y.  So what this is doing is making the ending account number "Y".  This is an issue becuase they have account numbrs starting with "Z" so it is dropping the journal entries for those accounts.

As long as I DON'T save the report option and creaete a new one every time, I get the values I need.  But once the report option is saved, it doesn't work.  I have tested this in the customer's GP10 and GP2013 test environment since we are upgrading them and it does the same in both.  I also looked at the GL70500 table and do see the yyyy in the columns for that report and others.  

When I test this on my computer though, it doesn't do this so seems like this is an issue for my customer's data only, maybe something in the table.  When I look at the Fabrikam company the YYYs are in that table too GL70500 yet the ending account number still stays blank when the option name is saved.  So I am not seeing this in Fabrikam.  

I have re-created the reports.dic many times, plus security is looking at the original report.  

Any thoughts on how to correct this?

Reply
Suggested Answer
Sheila Ochoa responded on 20 Mar 2013 2:02 PM

Thanks Tom for the article, I appreciate it.  I did look at their setup and that wasn't the issue.  I did find though that the table GL70500 had a length of 5 for the ending account numbers columns (EDGACNUM_1...).  So in a test company, I made some backups o the GL70500 and GL70501 tables.  Then I dropped and recreated the tables and stored procedures (using SQL Maintenance) for General Ledger Report Options and General Ledger Report Options Temporary.  After doing this the report ran fine when saved.  And when the table was re-created the length of the ending account numbers in that table are 9, not 5.  I am not sure why the length of that field was at 5 and not 9 but believe that has something to do with the way the report is working.

Something to consider though, if you drop these tables and stored procedures and re-create them, you will lose all of the Financial report options that have been saved.  This might not be something they want to lose.

Reply
Tim Foster responded on 20 Mar 2013 11:15 AM

See the exciting KB at:

mbs2.microsoft.com/.../KBDisplay.aspx

Tim

Reply
Sheila Ochoa responded on 20 Mar 2013 11:49 AM

I read the article and I understand what it is saying.  However, this report works and pulls all accounts including the "Z" accounts when you run it the first time. It excludes the accounts with "Z" after the report option is saved.  I guess at that point it is using the last value saved in this table for that report which is the Y.  But this report does not allow you to enter the range for accounts so you aren't even able to put in Z as an ending account number.  Reports - Financial - Cross Reference - Journal Entry.

Reply
Suggested Answer
Sheila Ochoa responded on 20 Mar 2013 2:02 PM

Thanks Tom for the article, I appreciate it.  I did look at their setup and that wasn't the issue.  I did find though that the table GL70500 had a length of 5 for the ending account numbers columns (EDGACNUM_1...).  So in a test company, I made some backups o the GL70500 and GL70501 tables.  Then I dropped and recreated the tables and stored procedures (using SQL Maintenance) for General Ledger Report Options and General Ledger Report Options Temporary.  After doing this the report ran fine when saved.  And when the table was re-created the length of the ending account numbers in that table are 9, not 5.  I am not sure why the length of that field was at 5 and not 9 but believe that has something to do with the way the report is working.

Something to consider though, if you drop these tables and stored procedures and re-create them, you will lose all of the Financial report options that have been saved.  This might not be something they want to lose.

Reply
Leslie Vail responded on 20 Mar 2013 4:22 PM

Which sorting order is their SQL using?

Leslie Vail, CPA, MVP, MCT, MCITP, MCP, MCITS
ASCI, Inc.    *    PO Box 600965   *   Dallas, TX 75360    *   972-814-8550   *   leslievail@earthlink.net

Reply