Union BAQ filtering

Kinetic ERP
1

Hi,

Need advice. I have top level and union all query, I need to use this BAQ in BAQ report with PartNum criteria. The problem is that if I filter TOP level partnum I get additional partnums from UnionAll, so how can I tell that UnionAll results would be the same partnums as in TOP level. I tried to add subquery parameter in UnionAll and added criteria that partnum should be equal to TOP level partnum, but I get an error that The multi-part identifier “Part.PartNum” could not be bound.

2

Can you share your BAQ please?

3

Hi,

It’s not so urgent now since when you add PartBin_PartNum filter in baq report screen it seems filters values as it should, but still I am curiouse how to do the same in baq. I know I can build third level as top and filter out partum, but that would be an additional calculation for sql. My query:

select 
	[PartBin].[Company] as [PartBin_Company],
	[PartBin].[PartNum] as [PartBin_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
	[PartBin].[BinNum] as [PartBin_BinNum],
	[PartBin].[OnhandQty] as [PartBin_OnhandQty],
	[Part].[IUM] as [Part_IUM],
	('Before') as [Calculated_Before]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on 
	PartBin.Company = Part.Company
	and PartBin.PartNum = Part.PartNum
where (PartBin.OnhandQty > 0)
union all
select 
	[CCTag].[Company] as [CCTag_Company],
	[CCTag].[PartNum] as [CCTag_PartNum],
	[Part1].[PartDescription] as [Part1_PartDescription],
	[CCTag].[WarehouseCode] as [CCTag_WarehouseCode],
	[CCTag].[BinNum] as [CCTag_BinNum],
	[CCTag].[CountedQty] as [CCTag_CountedQty],
	[CCTag].[UOM] as [CCTag_UOM],
	('After') as [Calculated_After]
from Erp.CCTag as CCTag
inner join Erp.Part as Part1 on 
	CCTag.Company = Part1.Company
	and CCTag.PartNum = Part1.PartNum
where (CCTag.FullPhysical = 1  and CCTag.CountedQty > 0  and CCTag.CCYear = @Year)
4

I’m still not sure what you’re trying to accomplish here. When you say “filter TOP level partnum” do you mean you want to return rows where the PartNum equals or is like a particular value?