Commercial Invoice

Epicor ERP 10
,
1

I’m interested in creating a commercial invoice and am struggling. I have created a custom SSRS report copying the PackSlip for this purpose. I have added all the Fields that I require. Everything looks great.
My problem is that I need the sum of the total amount shipped and the total weight shipped and the total lines. Unfortunately my report is summing all the lines (there are duplicates that are hidden/filtered) and the sum is wrong. I’ve tried different combinations, but cannot get the sum or count function to work. anybody else has run into this problem and found a solution. Please advise. Thank you

2

Are you guys using Quick Ship? If so, they have built-in Commercial Invoices.

3

I used a modified Packing Slip RDD/Style from Customer Shipment Entry to do a Commercial Invoice at my last company. I didn’t bring a copy with me so I can’t share, sorry.

4

If the grouping or built-in stuff was not working, it’s probably where I’d use custom functions inserted into the report sections to populate and increment a variable/s that I could use elsewhere in the report. But, I haven’t run into this issue specifically. Good luck with it.

5

What are the filtered/hidden duplicates? If you can edit the query directly to remove those, rather than hiding them from the report, it would probably solve your summation issue.

1 Like
6

Hello,

Have you used code in reports? I have one to sum distinct values.

Try to copy this code in your report by right clicking on the gray background outside the white page in Report Builder / Select Report Properties / Code:

Dim scopes As System.Collections.Hashtable
Function getDistinct(ByVal checkMe As Object, ByVal scope As String) As Boolean
Dim firstTimeSeen As Boolean

  If (scopes Is Nothing) Then
      scopes = New System.Collections.Hashtable
  End If

  If (Not scopes.Contains(scope)) Then
      scopes.Add(scope, New System.Collections.ArrayList)
  End If

  If (Not CType(scopes(scope), System.Collections.ArrayList).Contains(checkMe)) Then
      firstTimeSeen = True
      CType(scopes(scope), System.Collections.ArrayList).Add(checkMe)
  Else
      firstTimeSeen = False
  End If

  Return firstTimeSeen

End Function

** Then in a texbox inside your body you’ll use something like this:

=Sum(IIF(Code.getDistinct(Field you want your distinct by, YourGroup), CDbl(Field you want to sum), 0.00))
Examples:
=Sum(IIF(Code.getDistinct(Fields!InvoiceNum.Value, table1_Plant), CDbl(Fields!InvAmtBefTax.Value), 0.00))
=Sum(IIF(Code.getDistinct(Fields!BOLLine.Value, BOL), CInt(Fields!Packages.Value), 0))
1 Like
7

Hi Michelle. I tried this. I added the code as suggested to the Report Properties.
I then added an expression inside the body as follows
=Sum(IIF(Code.getDistinct(Fields!PartNum.Value,PartNum), CInt(Fields!TotalNetWeight.Value), 0))

I get the following error:
Error calling AddCatalogItem.
ErrorCode: InternalServerError(500)
Content: {
“error”:{
“code”:“1027”,“message”:“There is a syntax error in the Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’: ‘)’ expected.”
}
}

Can you please help. Thank you.

8

Not using Quick ship

9

Hello,

Seems like the chat didn’t group all the code together, just curious did you add the first statement and the “End Function” at the end as well?

Dim scopes As System.Collections.Hashtable
Function getDistinct(ByVal checkMe As Object, ByVal scope As String) As Boolean
Dim firstTimeSeen As Boolean

10

Yes, I did. I put it all together. I did that first and didn’t get any error. So that code works fine.

My expression in the body is where I’m having a problem.

11

Try adding double quotes to your Group Name, latest SSRS versions needs them:

=Sum(IIF(Code.getDistinct(Fields!PartNum.Value, “PartNum”), CInt(Fields!TotalNetWeight.Value), 0))

12

@MichelleCerda Code needs to have three Grave accents ``` before and after.

13

That helped. The error is gone, but the sum is still wrong.

14

You can try changing the field to Group by; you can also concatenate using different key fields to indicate your Distinct.
I would suggest using Pack Line instead of Part Number, not sure if it applies to you?

Also, the sum can vary based on the group where is located in.

15

Hi Michelle, I finally have some time to test this again. Can you please provide instructions on how to concatenate to indicate the Distinct field.