General Journal - Test report malfunctioning in base code

Andy_Smith

Report 2 "General Journal - Test" - RTC Only
I've seen this post, this post and this MSDN hotfix but none of these posts appear to offer answers that fix my particular issue.

Test case (can be repeated on Cronus):

- Go into a cash receipts journal and create a new batch (or use an existing one, it doesn't seem to matter) with a balance account type of bank account and any valid bank account.
- Enter three customer entries for any customer. I used amounts of $50, $100, and $150.
- Actions | Posting | Test Report...

The resulting report does correctly display the individual lines, but when you look at the total instead of the expected $300, you will see $1000. Note that in the reconciliation section, the amount to reconcile is correctly shown. Also note that per the second linked post above, it doesn't matter whether you select to show dimensions or not on the request page.

Perusing to "About this report" we see that each entry is actually doubled. Looking at the RDLC, we can actually see on the detail line where the "hidden" property essentially says "if you're the same as the previous line, hide this line", however the total simply takes a SUM call of the LCY values which doesn't distinguish between hidden and shown lines. To further complicate the issues, the amounts shown on the report line are not the LCY amounts but the unmodified amounts while the totals are based on a sum of the LCY amount. To further further complicate the issue, the LCY amounts appear to be calculating incorrectly as a running total (so the first two lines have an LCY of $50, the next two lines have an LCY of (100 + 50)=$150 and the third set of double lines have an LCY of (150+150)=$300) which because of the doubled entries totals to $1000 (50+50+150+150+300+300).

Given the likely heavy use of this report, I'm somewhat startled that there isn't more information about this bug.

EDIT: The fix below isn't adequate to fix the root issue. See followup post with a more comprehensive fix.

I was able to patch a local copy (but wouldn't mind feedback on the patch) by applying the following changes:

In Gen. Journal Line - OnAfterGetRecord() (very bottom of function) find the code:

CheckBalance;
IF ISSERVICETIER THEN BEGIN
  AmountLCY := AmountLCY + "Gen. Journal Line"."Amount (LCY)";
  BalanceLCY := BalanceLCY + "Gen. Journal Line"."Balance (LCY)";
END;

and replace with:

CheckBalance;
IF ISSERVICETIER THEN BEGIN
  //AmountLCY := AmountLCY + "Gen. Journal Line"."Amount (LCY)";
  AmountLCY := "Gen. Journal Line"."Amount (LCY)";
  BalanceLCY := BalanceLCY + "Gen. Journal Line"."Balance (LCY)";
END;

And to solve the "double line" issue in RDLC I simply divided the sum by 2 in the expression of Textbox157

=(SUM(Fields!AmountLCY.Value)/2)

JDVyska

Don't forget to also adjust BalanceLCY code in the same sections in the same way or you'll get some odd results in the Balance columns.

Andy_Smith

After having some rather belated feedback from the client, I determined that the fix above doesn't work, partly because I didn't understand why it was breaking in the first place.

Cause of bug:

The issue is that the report total, which is a sum of AmountLCY, often reflects duplicate counts per line. The reason this occurs is that the AmountLCY is a line amount for the Gen. Journal Line, but the Gen. Journal Line object in the report is not the "detail" line item in the RDLC version. Instead, the three sub-indents for Gen. Journal Allocation, DimensionLoop (integer), and ErrorLoop (integer) are the detail lines. What this means is that in the base report every one of those lines will have an AmountLCY, which often duplicates the existing one which in turn throws of the SUM calculation in the footer section of the group. The conversion to the flat file for RDLC is what throws this off.

Microsoft will supply a fix for the report that zeros out the AmountLYC (and BalanceLCY) in the DimensionLoop and ErrorLoop PreDataItem trigger (see code at the end of this post). Unfortunately on some recurring journals with multiple allocations, this will still not fix the issue because there will still be multiple allocation lines.

Proposed Fix:

The issue is that the flat file nature duplicates the values. Thus, we need to ensure that for the RTC version we only have one line with a given value. The Microsoft fix above will take you part of the way there, but isn't complete. What we need to do is check to see if a given allocation line is the first line and if it is the first allocation line then we carry over the AmountLCY (and do the BalanceLCY calculation) and if it isn't, we set those values to zero.

1) Declare a global variable FirstLine of type Boolean.
2) In OnAfterGetRecord of the Gen. Journal Line object set FirstLine := TRUE;
3) In OnAfterGetRecord of the Gen. Jnl. Allocation Object insert the following code:

IF FirstLine THEN BEGIN
  FirstLine := FALSE;
  IF ISSERVICETIER THEN BEGIN
    AmountLCY := "Gen. Journal Line"."Amount (LCY)";
    BalanceLCY := "Gen. Journal Line"."Balance (LCY)";
  END;
END ELSE BEGIN
  AmountLCY := 0;
  BalanceLCY := 0;
END;