AR Invoice - Sum DocDiscount

Kinetic ERP
, ,
1

I am having trouble summing the DocDiscount field at the bottom of the invoice reliably. It seems when an invoice is a single line with a miscellaneous charge (or two) the discount is doubled. So I actually set up two fields with visibility rules to handle a dataset with one line vs a dataset with multiples and that was working ok.

This is now happening with a dataset that contains multiple invoice lines and the discount on the last line is doubled and I’m not sure the best way to proceed. Feels like I’m just spinning my wheels.

=Sum(Fields!DocDiscount.Value) * - 1

invoice discounts

2

Looking further I suspect it may be a join, I found a visibility rule hiding the duped line. But if it was a bad join I would think more than just the final line would be duplicating.

FROM InvcHead_" + Parameters!TableGuid.Value + " T1
          LEFT OUTER JOIN InvcDtl_" + Parameters!TableGuid.Value + " T2
          ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum
          LEFT OUTER JOIN FSCallDt_" + Parameters!TableGuid.Value + " T3
          ON T2.Company = T3.Company AND T2.InvoiceLine = T3.Calc_InvoiceLine AND T2.InvoiceNum = T3.Calc_InvoiceNum
          LEFT OUTER JOIN FSCallMt_" + Parameters!TableGuid.Value + " T4
          ON T3.Company = T4.Company AND T3.CallNum = T4.CallNum AND T3.Calc_fCallLine = T4.Calc_fCallLine AND T3.Calc_InvoiceLine = T4.Calc_InvoiceLine AND T3.Calc_InvoiceNum = T4.Calc_InvoiceNum
          LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T5
          ON T1.RptLanguageID = T5.RptLanguageID LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6
          ON T1.Company = T6.Company AND T1.CustNum = T6.CustNum AND T1.InvoiceNum = T6.Calc_InvoiceNum
          LEFT OUTER JOIN FSContDt_" + Parameters!TableGuid.Value + " T7
          ON T2.Company = T7.Company AND T2.InvoiceLine = T7.Calc_InvoiceLine AND T2.InvoiceNum = T7.Calc_InvoiceNum
          LEFT OUTER JOIN InvcMisc_" + Parameters!TableGuid.Value + " T8
          ON T1.Company = T8.Company AND T1.InvoiceNum = T8.InvoiceNum AND T2.InvoiceLine = T8.InvoiceLine
          LEFT OUTER JOIN FSAExtData_" + Parameters!TableGuid.Value + " T10
          ON T2.Company = T10.Company AND T2.SysRowID = T10.ForeignSysRowID
          LEFT OUTER JOIN ( SELECT  temp.Company, temp.InvoiceNum, temp.InvoiceLine, MIN(temp.SeqNum) as MinSeqNum
          FROM InvcMisc_" + Parameters!TableGuid.Value + "  temp
          GROUP BY     temp.Company, temp.InvoiceNum, temp.InvoiceLine) T9
          ON T1.Company = T9.Company
          AND T1.InvoiceNum = T9.InvoiceNum
          AND T2.InvoiceLine = T9.InvoiceLine
         LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T11 On T1.OrderNum = T11.OrderNum AND T1.Company = T11.Company
		 LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T12 ON T2.PartNum = T12.PartNum 
	 LEFT OUTER JOIN UD14_" + Parameters!TableGuid.Value + " T13 ON T11.NewProgram_c = T13.Key1 
		 ORDER BY  T1.InvoiceNum, T2.Calc_NextLegalNumID, T2.InvoiceLine, T8.SeqNum"
3

I just copied the standard SSRS and the same behavior is present.

1 Like
4

I thought I had it good with this formula but the logic dies when mass printing invoices.

=IIf(
    Max(Fields!Calc_InvoiceDisplayLine.Value, "ARForm") <> CountRows("ARForm"),
    (Sum(Fields!DocDiscount.Value, "ARForm") - Lookup(Max(Fields!Calc_InvoiceDisplayLine.Value, "ARForm"), Fields!Calc_InvoiceDisplayLine.Value, Fields!DocDiscount.Value, "ARForm")) * -1,
    Sum(Fields!DocDiscount.Value, "ARForm") * -1
)
5

Are you sure your scope is correct for the logic above?

6

It is a scope issue but I get an error on the lookup function when specifying a different scope.

7

Lines are duplicated if there are multiple miscellaneous charges attached because those are held in a different table, so naturally the row would duplicate the same as in a BAQ. To tackle this I am having our AR person add the freight misc charge to the first line of the invoice instead of the last line because I was not having any luck returning the MAX line number.

So to make sure the DocDiscount field sums accurately after adding the freight misc charge I added a subquery to the SQL statement to return the DocDiscount value of the first line of the invoice.

 (SELECT T2_Sub.DocDiscount
     FROM InvcDtl_" + Parameters!TableGuid.Value + " T2_Sub
     WHERE T2_Sub.Company = T2.Company 
       AND T2_Sub.InvoiceNum = T2.InvoiceNum 
       AND T2_Sub.InvoiceLine = T2.InvoiceLine
       AND T2_Sub.DocDiscount IS NOT NULL) AS SubDocDiscount

The I subtract SubDocDiscount from the total of DocDiscount.

=(Sum(Fields!DocDiscount.Value) - Fields!SubDocDiscount.Value) * -1

This seems to be working and hopefully it stays that way.

1 Like