Return 0 instead of blank field in BAQ for PartTran

Epicor ERP 10
1

I have created a BAQ where I am seeing how much of a product we have sold over the past couple of years, broken down into a weekly figure.
However, if we have not sold anything for that week, it does not return anything at all whereas I would like a 0 in its place, because it is not calculating the averages sales correctly.

Below is query I have so far. I believe I need to do something with ISNULL, but no matter what I try, I cannot get it to work.

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[Obsolete_c] as [Part_Obsolete_c],
	[Part].[NonStock_c] as [Part_NonStock_c],
	[Part].[InActive] as [Part_InActive],
	(DATEADD(dd, -(DATEPART(dw, PartTran.TranDate)-1), PartTran.TranDate)) as [Calculated_WeekComm],
	(Sum(PartTran.TranQty)) as [Calculated_TotalQty]
from Erp.Part as Part
inner join Erp.PartTran as PartTran on 
	Part.Company = PartTran.Company
	and Part.PartNum = PartTran.PartNum
	and ( PartTran.TranDate >= dateadd (week, -120, Constants.Today)  and not PartTran.PONum > 0  )

where (Part.Company = 'DD01')
 and (PartTran.TranType = 'DRP-CUS'  or (PartTran.TranType = 'STK-CUS'  or PartTran.TranType = 'STK-MTL'  or PartTran.TranType = 'STK-KIT' ))

group by [Part].[PartNum],
	[Part].[PartDescription],
	[Part].[Obsolete_c],
	[Part].[NonStock_c],
	[Part].[InActive],
	(DATEADD(dd, -(DATEPART(dw, PartTran.TranDate)-1), PartTran.TranDate))
order by Part.PartNum, WeekComm

Current output data

28/02/2021 - 2
07/03/2021 - 2
14/03/2021 - 5
21/03/2021 - 5
04/04/2021 - 10

I am missing 28/03/2021 where I would want it to be 0.

Can anybody help?

2

Welcome @KHudson. I would try.

COALESCE(Sum(PartTran.TranQty), 0)

And a friendly tip on this forum, put your code between 3 backticks (the key above Tab). So, ``` CODE ```.

Some people get grumpy if you don’t.

1 Like
3

probably need to make the join to the PartTran as an OUTER join. An Inner join tells the query that the data must exist in the other table… but the outer join should still give you a value of zero (i think).

4

Thank you for the tip, I will remember in future.

5

Personally, I’d look at InvcDtl instead of PartTran. PartTran tells you the product was shipped to the customer, but that doesn’t mean you sold it. AR shows what you actually billed to the customer, which is usually the closest definition to what people think of as “sold”. Not just ordered (short/over ships happen). Not just what pieces moved out of the warehouse (RMA’s, samples, etc. are a thing).

Also the correct syntax of ISNULL would be

ISNULL(YourField, 0)

Just make sure the field is actually null, which because you have no outer joins, is unlikely to be the case here. Also, if this is the entirety of your query, when you have zero sales for a week, the week will be missing regardless of your aggregate settings. You need to generate a list of dates or weeks and then outer join the ERP tables to see if there’s any corresponding records in that bucket.

As to how you’d get a list of dates, I’ve never done that in a BAQ personally. I usually script up a loop in SQL. You could probably set up a UserCode, fill it with dates, and join on that. I wouldn’t burn a whole UD table on it.

1 Like
6

I’d have a hard time explaining this on phone but you could hack something together with row_number probably.

relevant:

7

Yeah, that would totally work.

DATEADD(D, -ROW_NUMBER() OVER(ORDER BY PartTran.TranNum) +1, GETDATE())

Should start from today and count backwards for however many days you put into the TOP clause.

1 Like
8

I knew you wouldn’t let me down.