How do you count open releases

Epicor ERP 10
1

First I have looked up previous questions concerning “counting open releases” but unfortunately I’m not understanding what they have done.

I’m creating a BAQ that returns the total dollar amount on all open orders. I have been asked to report the number of open releases.

I have tried COUNT (OrderRel.OpenRelease) but it is not working and I have a feeling it might have something to do with the Data Type?

Any idea’s?

2

You are just counting the number of total rows. COUNT() doesn’t do a count if true or anything like that. So what you’ll want to do a sum with a case statement.

sum(
case when OrderRel.OpenRelease = true then 1
else 0
end
)
2 Likes
3

We often get asked for this kind of data as well. Remember that your unit price is at the order line level, and the quantity at the release level determines the total potential value of the sales order. Sometimes they really want that remaining value on the order, because some of it shipped out already. In this case, you have to subtract the shipped amount from the release quantity to see how much is left on each release. Multiply by your unit price to see remaining sales order value. How this actually aligns with your open jobs, and purchase orders is a while different story!
Good luck!

4

@Banderson what would be the Data Type (Bit? NVCHAR?)

What would this error mean?
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “OrderRel.OpenRelease” could not be bound.
Invalid column name ‘true’.

5

the data type would be int (you’re summing something)

You can (and probably should) use 1 for true (this is SQL)

I was just trying not to confuse you. Epicor will usually translate for you, but 1 and 0 for bit fields is how SQL stores them.

6

@Banderson good to know about “integer”. I keep receiving the following error:
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “OrderRel.OpenRelease” could not be bound.
Invalid column name ‘true’.
The multi-part identifier “OrderRel.OpenRelease” could not be bound.
Invalid column name ‘true’.

7

Change true to 1. In computer language, 1 is true and 0 is false.

sum(
case when OrderRel.OpenRelease = 1 then 1
else 0
end
)

(and yes, you might be able to get away with summing the field to add up all the 1’s, but that’s generally not a great idea)

8

@Banderson

select distinct
	(sum( SubQuery2.Calculated_OpenQty )) as [Calculated_TotalOpenQty],
	(sum( SubQuery2.Calculated_OpenValue )) as [Calculated_TotalValue],
	[SubQuery2].[Calculated_TotalOpenRlse] as [Calculated_TotalOpenRlse]
from Erp.OrderHed as OrderHed
inner join  (select 
	[OrderRel1].[OrderNum] as [OrderRel1_OrderNum],
	[OrderRel1].[PartNum] as [OrderRel1_PartNum],
	[OrderRel1].[ReqDate] as [OrderRel1_ReqDate],
	[OrderRel1].[NeedByDate] as [OrderRel1_NeedByDate],
	((OrderRel1.OurReqQty - OrderRel1.OurJobShippedQty - OrderRel1.OurStockShippedQty)) as [Calculated_OpenQty],
	(((case when  (OrderDtl1.PricePerCode = 'M')  then  ((((case when  (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else  0 end))/ NULLIF(1000, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0))))  else ((case when  (OrderDtl1.PricePerCode = 'C')  then ((((case when  (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.OurStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else  0 end))/ NULLIF(100, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0))))  else ((((case when  (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty - OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else  0 end))/ NULLIF(1, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) end)) end))) as [Calculated_OpenValue],
	(sum( case when OrderRel1.OpenRelease = 1 then 1 else 0 end )) as [Calculated_TotalOpenRlse]
from Erp.OrderDtl as OrderDtl1
inner join Erp.OrderRel as OrderRel1 on 
	OrderRel1.Company = OrderDtl1.Company
	and OrderRel1.OrderNum = OrderDtl1.OrderNum
	and OrderRel1.OrderLine = OrderDtl1.OrderLine
	and ( OrderRel1.OpenRelease = Y  and OrderRel1.OurReqQty > 0  and OrderRel1.ReqDate < @EndDate  )

group by [OrderRel1].[OrderNum],
	[OrderRel1].[PartNum],
	[OrderRel1].[ReqDate],
	[OrderRel1].[NeedByDate],
	((OrderRel1.OurReqQty - OrderRel1.OurJobShippedQty - OrderRel1.OurStockShippedQty)),
	(((case when  (OrderDtl1.PricePerCode = 'M')  then  ((((case when  (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else  0 end))/ NULLIF(1000, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0))))  else ((case when  (OrderDtl1.PricePerCode = 'C')  then ((((case when  (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.OurStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else  0 end))/ NULLIF(100, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0))))  else ((((case when  (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty - OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else  0 end))/ NULLIF(1, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) end)) end))),
	(sum( case when OrderRel1.OpenRelease = 1 then 1 else 0 end )))  as SubQuery2 on 
	SubQuery2.OrderRel1_OrderNum = OrderHed.OrderNum

I’m receiving the following error - and have tried multiple ways to get rid of it…

Severity: Error, Table: , Field: , RowID: , Text: Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 5058.7648 ms.
9

Uncheck the group by on your aggregated calculated field.

2 Likes