BAQ Report SUM() help needed

Epicor ERP 10
,
1

I’ve been tasked to create a $ shipped vs due report. I was able to create a BAQ that has the following expected results.

image

In my BAQ report, when I sum the Calculated_ReleaseAmount field to get the total amount due, it comes up with a total of $152880 when it should be $54480. The report is grouped on Order/Line/Release and it appears that SSRS is including the duplicated release amounts when I only need the first occurrance. How can I get the final totals to come out correct?

I’m fairly new to SSRS Report Builder and this is driving me crazy.

Thanks in advance.

2

Option 1: Get rid of any dupes in your dataset. (You mention “duplicate release amounts” and it’s not entirely clear what you mean by that). Basically, rework your dataset to work with the basic SUM function.

Option 2: nest a iif function in your sum. Sum(Iif(Rel = x,ReleaseAmt,0))

3

John,
I’m not sure if this helps, but the report needs to look something like this:

Order Line Release Release Qty Release Amount Pack Num Pack Line Ship Date Qty Shipped Shipped amount
15994 1 1 80 49200 5000 1 1/4/2021 1 615
5000 2 1/4/2021 29 17835
50017 1 1/11/2021 50 30750
15970 1 1 16 5280 50001 1 1/5/2021 16 5280
Totals 54480 54480

Option 1 - If you look at the sample data I provided, Order# 15594 Line 1 Release 1 has 3 shipments so the Release Amount is being duplicated/repeated on all 3 rows. How do I get rid of the duplicate release amounts from the BAQ results? I already hide the duplicates on the BAQ Report rdl but they are still being included in the totals.

Option 2 - What are you meaning x to represent in the Iif statement? As with the Release amount the Release number is repeated for each row as it is one of the “group by” fields.

4

“X” can be a number of things. That’s why it’s important to see the dataset, so thank you for that.

Can I assume you’re using Row Groups in your tablix? If not, group things by OrderNum, Line, Release, with the individual shipments in the detail level. Then you can use

=Sum(iif(RowNumber("Release") = 1,ReleaseAmt,0))

Where “Release” is the same name as your group. And yes, put the group name in quotes. RowNumber will count rows, resetting the count when your report jumps to the next release. Thus, with this embedded in your sum(iif)), it will zero out every row subsequent to the first, regardless of how many there are.

5

I put your expression into my rdl, but when I tried to upload the rdl, I received an error: The Value expression for the textrun ‘Textbox107.Paragraphs[0].TextRuns[0]’ contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

After further research I found that the following expression gave me exactly what I needed:

=Sum(MAX(ReleaseAmt, “Release”))

Thanks for your assistance.

6

Wendy - “GroupBy” can be thought of as - "consider identical lines (when All GroupBy fields match), as a single line.

Starting with the following data

OrdNum  OrdLine  OrdRel  PartNum  RelQty  ExtCost
1000       1        1    ANVIL      5         500
1000       1        2    ANVIL      7         700
1000       2        1    JETPACK    1        2500
1001       1        1    BB's      500        250
1002       1        1    MAGNET      1        900
1003       1        1    ANVIL       3        300

If my BAQ displays just one column OrderNum, my output would be

OrdNum  
1000       
1000       
1000       
1001       
1002       
1003

If my BAQ displays just one column OrderNum and GroupBy is selected, my output would be

OrdNum  
1000       
1001       
1002       
1003

(it put all the records whose OrdNum was the same in a single row)

If I add the PartNum field (but not Grouped by) and still have OrderNum Grouped by, I’d get:

OrdNum  PartNum
1000       ANVIL
1000       ANVIL
1000       JETPACK
1001       BB's
1002       MAGNET
1003       ANVIL

Even though the first two are the same, they aren’t combined because the PartNum column is not GroupBy.

Setting GroupBy on the PartNum column to would yield

OrdNum  PartNum
1000       ANVIL
1000       JETPACK
1001       BB's
1002       MAGNET
1003       ANVIL

The first two rows are combined because OrdNum and PartNum are the same on each.

Back to just having the OrdNum s the only column (and GroupBy set for it), adding column for Line Count (COUNT(OrdLine) as LineCnt, could be any field really), yeilds

OrdNum  LineCnt
1000       3
1001       2
1002       1

That first one is 3, and not the expected 2, because there are three records that have the same OrdNum of 1000.

Hope that helps

7

If you want to compare the total shipped quantity (without details of each shipment) to the released order amount, I would create a subquery to summarize the shipment quantities grouped by order / line / release. The main query can be outer joined to the subquery through order / line / release and compare the ordered quantity against the sum of the shipments.