In SSRS some Values are multiplied by the Quantity of pieces

Epicor ERP 10
1

I got a BAQ that gave me the PackID, PO, PO Line, Serial Number, Quantity and Weight but at the moment to us this BAQ to create a report in SSRS some quantities like PackID, Quantity and Weight are multiplied by the Quantity of pieces that contain the Pack ID as example if my:

PackID = 10
Qty= 2
Weight = 5

In the BAQ, when I tried to create the SSRS report, first of all I notice that those values in SSRS appear as SUM (Sum(Fields!ShipHead_PackNum.Value) and the result in the report once is tested appear the info like this:

PackID = 20
Qty= 4
Weight = 10

For some reason, I do not why is doing a multiplication between those fields and the Quantity.

Do you have an idea about how to prevent this operation?

Thank you,
Luis

2

Take the SUM function out of the expression.

3

There might be a record for each qty, but you only see one, because of grouping.

4

I guess it is not multiplying by quantity. There are multiple rows in BAQ which is summing up.
In this case, there are 2 rows for this pack.

5

So I was close… getting multiple records - which dont show because of grouping. Add a calc field:

(COUNT(Fields!ShipHead_PackNum.Value)

to confirm.

Now to figure out why you get multiple records.

6

Already did it but do not works, at the moment to upload the SSRS report in EPICOR show up this error message:

" The Value expression for the text box ‘Textbox5’ refers directly to the field ‘ShipHead_PackNum’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope. "

7

Screen shot of your BAQ, or copy and paste the SQL?

8

It sounds like you may want to go through the SSRS training first.
It seems likely you did not add all this in a Tablix first?

9

Yes, there is a record for each qty because I am calling the SN, QTY and WEIGHT for each PACKID and i am grouping because if not, the BAQ result show up the info of each SN repeated for the qty of the shipment.

10

Since S/N’s have to be unique, you will get a record for each count of the qty (you were correct in your original post , about it being multiplied by the qty).

If you remove the S/N field, you’ll get a single record for each part, and can use the SUM(qty).

Or you can remove the SUM(qty) and just make it qty, and get a line for each S/N.

What you might want is to combine the S/N’s into a single field. Like:

Line PartNum  Qty  S/N  
  1  XYZ-123   1   1234567
  1  XYZ-123   1   1234568
  1  XYZ-123   1   1234569
  1  XYZ-123   1   1234570

to

PartNum  Qty  S/N's
XYZ-123   1   1234567, 1234568, 1234569, 1234570
11

It were added all to a tablix but even if I change in the to “First” all the values show up are SUM when I add the expression.

12

This is my BAQ

image

13

So what do you want that number to be? The total number of Packers in the query result? Total number of lines, total qtys per line, per part number, per S/N?

14

Even if I remove the SN appear a single line of each of the PO lines that contain the PACK ID like:

PACID PO LINE REL PART QTY
01 1 1 3 1425 5
01 1 2 3 1425 2
01 1 3 2 1425 1

But I need the SN for my report, so if I add the SN will work but when I am doing the report in SSRS the info from PACKID, LINE, REL, QTY, WEIGHT and TRACKING NUMBER appear as SUM in SSRS report builder.

15

For report purpose I want that (Fields!ShipHead_PackNum.Value) can be without SUM.

For the report I need the TOTAL NUMBER OF THE PART IN THE PACKID, SN of each part in the packid, the PACKID number, TRACKING NUMBER and WEIGHT. That already got but at the moment to work with this info in the report it present the issue that I commented before.

16

Okay. Leave your BAQ as shown in your screenshot (no Calc fields). It will generate a row for each SN. Then in the SSRS Report Builder, group on PackID, and PartNum. Put a footer in the PartNum group, and have your sum expression in there.

17

thank you ckrusen for your help