Hello,
I am editing the ARForm to try and add a ‘Shipped Qty Total’.
My expression is: SUM(Fields!SellingShipQty.Value)
This seems to work most of the time but I occasionally get an invoice that seems to double the qty.
Not sure of the difference between the two invoices.
Can someone tell me what they have used to do this or the best way to get the total?
Any help is appreciated!
Can anybody point me in the right direction please?
Does anyboday have an idea they can throw at me please?
Anything would be a help.
What would cause an invoice detail line to be counted twice by the SUM function?
Could there be more than one release on the line that is duplicating your invoice line amount?
Also check for Misc charges on the invoice. Basically, any table that has records and that links to InvcDtl could be duplicating your total. Check out the Report Data Definition to see the Report Relationships and Data Sources.
Where and how did you add this expression to the SSRS?
I added it to the bottom of the report.
Do you know a good way to sum the qty up outside of the details group?
You may want to consider writing a Subquery in the ARForm Select Statement to sum the quantity.
Epicor is doing some VB magic in the background because the invoice is typically printed as a Group (with many invoices). I have struggled with this before and found it was just as easy to add a subreport in the RDL Select statement.
i.e.
(Select SUM(T100.SellingShipQty) FROM InvcDtl_" + Parameters!TableGuid.Value + " T100 WHERE T2.Company = T100.Company AND T2.InvoiceNum = T100.InvoiceNum AND T2.InvoiceLine = T100.InvoiceLine) AS InvcDtl_SellingShipQty
Disclaimer: I have not tested this - just scratched it out.
DaveO
OOPS i said subreport - WRONG it is a SubQuery (late friday) 
Hello,
I tried the statement you put in your reply.
It doesn’t blow up but it only shows the line 1 qty…
If I put it in a sum, them it does the same as the original.
It adds the lines together, then doubles it.
Any ideas on why the statement you created doesn’t add them all together before showing?
I truly appreciate your help!
Is your SUM supposed to be line based or are you trying to get the number of pieces shipped for the whole invoice?
Whole Invoice.
The textbox that displays it is not in the lines part of the report. It is below, if that makes sense.
There is a screenshot above circling where the textbox is, if that helps (I think post #7?)
Did you make any changes to the RDD or to the Query in the RDL?
You have to be more specific.
This report has been heavily modified for years.
Recently, I added the expression from a few posts up to see if that helped.
I can try copying the base report and making the one change from above to it to see if that also does it.
I will post back after I try that.
I would try and pinpoint when the value gets doubled. Is it on invoices with more than 1 line, is it invoices with misc charges, etc. My guess is that it happens intermittently because the majority of invoices have an empty table.
I was asking about changes because I have seen instances where a join gets duplicated by accident.
Mr. Shawn: try removing the reference to the Iinvoice line.
i.e.
(Select SUM(T100.SellingShipQty) FROM InvcDtl_" + Parameters!TableGuid.Value + " T100 WHERE T2.Company = T100.Company AND T2.InvoiceNum = T100.InvoiceNum) AS InvcDtl_SellingShipQty
I think that worked!
I guess the expression was getting too specific?
Once I removed the invoice line part it worked perfectly (so far).
I will have the employee test and let meknow if they find another that is not calculating correctly.
I appreciate you walking me through this.
