Report Values Doesn't Tally with GL Values

I have a Sales report that is filtering Value Entry table using the following FILTERS.

a) Item Ledger Entry Type = Sale
b) Gen. Bus. Posting Group = eg COAST
c) Inventory Posting Group = eg SEC
d) Posting Date = eg 01/01/07 31/12/07

The Values that am getting from this report doesn’t tally with the values of GL while still using the above filters.

What could be the problem to this.

please assist me

The problem is you are not looking at the same tables. Obvious I know, but what I would recommend is you filter this by day until you find a difference, then go from the ILE to the VLE, you will find the VLE has an entry outside your ILE date range, but is filtering up to the cost etc of the ILE and making the difference. I would guess [:D]

Ok this what i exactly mean. My report is designed like this:

There is only one dataitem = Gen. Business posting Group

These are the codes:

Gen. Business Posting Group - OnAfterGetRecord()

InvPostingGrpTotals := 0;
GenBusiPostingGroupTotals := 0;
valueEntry.RESET;
valueEntry.SETCURRENTKEY (“Item Ledger Entry Type”,“Gen. Bus. Posting Group”,“Inventory Posting Group”,“Posting Date”);
valueEntry.SETFILTER (valueEntry.“Item Ledger Entry Type”,‘Sale’);
valueEntry.SETRANGE(valueEntry.“Posting Date”,SalesDateFilter,SalesDateFilter1);
valueEntry.SETFILTER (valueEntry.“Gen. Bus. Posting Group”,“Gen. Business Posting Group”.Code);
Idx := 1;
REPEAT
GenBusiPostingGroupAmt[Idx] := 0;
valueEntry.SETFILTER(valueEntry.“Inventory Posting Group”,InventoryPGrp[Idx].Code);
IF valueEntry.FIND(’-’) THEN
REPEAT
GenBusiPostingGroupAmt[Idx] += valueEntry.“Sales Amount (Actual)”;
UNTIL valueEntry.NEXT = 0;
GenBusiPostingGroupTotals += GenBusiPostingGroupAmt[Idx];
InvPostingGrpTotals += GenBusiPostingGroupAmt[Idx];
TotalValue[Idx] += GenBusiPostingGroupAmt[Idx];
Idx += 1;
UNTIL InventoryPGrp[Idx].Code = ‘’;
IF GenBusiPostingGroupTotals = 0 THEN
CurrReport.SKIP;
GrandTotalValue += GenBusiPostingGroupTotals;

What this report produces for each Gen. business posting Group is wrong.

NB: Report is running well. ONLY values are wrong. Please can you review the codes and tell me what i need to do further. Thank you

I am not a devleoper, so I can be of little use from here on in.

What I would check though is if you remove the filters on the VLE and then apply the ones you use on the report does the filter on the screen match your report. If it does there is nothing wrong with your code or your report. What is wrong is your assumption the GL would tally, and your issue is with what is in your GL!

it should match, but this case is frequent if “direct posting” is allowed on the sales g/l account.

then someone might have posted a G/L entry directly to the account.

other case, if someone chaged the posting groups of an item/vendor… or the posting account was changed in the posting group might give you a diffrence.

I have finally come to a conclusion though not very sure. What i have realised is that the GL amount is inclusive of discount amount. So i went ahead and also included discount amount in my report. Infact the table that am using is Value Entry Table. I was summing up field Sales amount (actual), but have as well included field Discount amount.

Now my Qs is, where on setup do one setup so that sales amount is posted together with the discount amont on the GL

Thank you

sales and receivalbe setup, discount posting

Look at the General Posting Setup, in here the combination of Business and Product is set. For each combination you will see an entry for the sales line discount amount and the sales invoice discount amount, they will presumably be pointing to the same account as the sales account.

Thank you Romeo for the direction but may be one more Qs.

  1. If in any case somebody changes the Gen. Bus Posting Groups or the Account for an Item/vendor/Customer all this, How would it reconcile with the Gl during the reconciliation?

  2. if a G/L entry is posted directly to the account how can i reflect it to the rest of the vendor/item/customer tables?

Thank you

  1. You cannot because of the way you are running the reports. I am not sure I know of anyone that reconciles in this manner, but obviously if they do they need to be aware of changes in codes. Overall it will reconcile.

  2. The purpose of the direct to GL entry will be to cut out the customer/vendor/item, so you cannot reflect it. I would recommend any account you reconcile on in a report-gl manner you prevent direct posting too, have a separate account to handle the direct posting, makes it an awful lot easier to reconcile as well.