I have my parts grouped by part number and revision number. Now I want to exclude all parts with all their revision that have ink in them. Not just ink by itself. How do I do that in SSRS or even in a BAQ?
For BAQ:
For your TopLevel, include the PartNum table
Then add a Subquery, use the PartMtl table
Add a Table Critera to PartMtl: MtlPartNum IN specified constant list and include only your Ink Part #s
Go to Display Fields, and just bring over the PartNum (the parent PartNum, not the MtlPartNum)
Go back to Top Level, add a SubQuery critera, something like this: NOT PartNum IN field from Specified Subquery and choose the PartNum field from SubQuery2
1 Like
So if this is my query/BAQ so far:
select
[ECOMtl].[Company] as [ECOMtl_Company],
[ECOMtl].[PartNum] as [ECOMtl_PartNum],
[ECOMtl].[RevisionNum] as [ECOMtl_RevisionNum],
[ECOMtl].[MtlSeq] as [ECOMtl_MtlSeq],
[ECOMtl].[MtlPartNum] as [ECOMtl_MtlPartNum],
[ECOMtl].[QtyPer] as [ECOMtl_QtyPer],
[ECOMtl].[EstScrap] as [ECOMtl_EstScrap],
[ECORev].[Plant] as [ECORev_Plant],
((case when ECORev.Plant = ‘M’ then ‘WC’ else ‘WC’ end)) as [Calculated_GroupID],
((case when ECOMtl.MtlPartNum Like ‘%ink%’ then ‘Y’ else ‘’ end)) as [Calculated_Ink]
from Erp.ECOMtl as ECOMtl
inner join Erp.ECORev as ECORev on
ECOMtl.Company = ECORev.Company
And
ECOMtl.GroupID = ECORev.GroupID
And
ECOMtl.PartNum = ECORev.PartNum
And
ECOMtl.RevisionNum = ECORev.RevisionNum
And
ECOMtl.AltMethod = ECORev.AltMethod
group by [ECOMtl].[Company],
[ECOMtl].[PartNum],
[ECOMtl].[RevisionNum],
[ECOMtl].[MtlSeq],
[ECOMtl].[MtlPartNum],
[ECOMtl].[QtyPer],
[ECOMtl].[EstScrap],
[ECORev].[Plant],
((case when ECORev.Plant = ‘M’ then ‘WC’ else ‘WC’ end)),
((case when ECOMtl.MtlPartNum Like ‘%ink%’ then ‘Y’ else ‘’ end))
I would just make my sub query with the same table, correct?
Why not something like.
select
[ECOMtl].[Company] as [ECOMtl_Company],
[ECOMtl].[PartNum] as [ECOMtl_PartNum],
[ECOMtl].[RevisionNum] as [ECOMtl_RevisionNum],
[ECOMtl].[MtlSeq] as [ECOMtl_MtlSeq],
[ECOMtl].[MtlPartNum] as [ECOMtl_MtlPartNum],
[ECOMtl].[QtyPer] as [ECOMtl_QtyPer],
[ECOMtl].[EstScrap] as [ECOMtl_EstScrap],
[ECORev].[Plant] as [ECORev_Plant]
from Erp.ECOMtl as ECOMtl
inner join Erp.ECORev as ECORev on
ECOMtl.Company = ECORev.Company
And
ECOMtl.GroupID = ECORev.GroupID
And
ECOMtl.PartNum = ECORev.PartNum
And
ECOMtl.RevisionNum = ECORev.RevisionNum
And
ECOMtl.AltMethod = ECORev.AltMethod
Where (ECOMtl.MtlPartNum not like ‘%ink%’ or ECOMtl.MtlPartNum = ‘ink’)
1 Like
@knash I had tried that first, but it just eliminates the line. I want every part that has not ink in any of their revisions. Some of our parts have up to 20 revisions, so I hit 10K lines fast. But I need a way to have Epicor tell me, looking at each part and all their revisions, which ones (parts and/or revision) do not have ink in them.
What @lgraham mentioned seemed to give me quite a few things to look at and it did pull parts that had no revisions, which for us is very odd.
You are trying to return parent parts that don’t have INK in the BOM?
Yes
You will need to add revision to this if it looks like it work, this return Parts that don’t have ink in any revision.
select
[ECOMtl].[Company] as [ECOMtl_Company],
[ECOMtl].[PartNum] as [ECOMtl_PartNum],
[ECOMtl].[RevisionNum] as [ECOMtl_RevisionNum],
[ECOMtl].[MtlSeq] as [ECOMtl_MtlSeq],
[ECOMtl].[MtlPartNum] as [ECOMtl_MtlPartNum],
[ECOMtl].[QtyPer] as [ECOMtl_QtyPer],
[ECOMtl].[EstScrap] as [ECOMtl_EstScrap]
from Erp.ECOMtl as ECOMtl
where ECOMtl.PartNum NOT IN (
select
[ECOMtl].[PartNum] as [ECOMtl_PartNum]
from Erp.ECOMtl as ECOMtl
Where (ECOMtl.MtlPartNum like ‘%INK%’ and ECOMtl.MtlPartNum <> ‘INK’))
This is a bit better
select
[ECOMtl].[Company] as [ECOMtl_Company],
[ECOMtl].[PartNum] as [ECOMtl_PartNum],
[ECOMtl].[RevisionNum] as [ECOMtl_RevisionNum],
[ECOMtl].[MtlSeq] as [ECOMtl_MtlSeq],
[ECOMtl].[MtlPartNum] as [ECOMtl_MtlPartNum],
[ECOMtl].[QtyPer] as [ECOMtl_QtyPer],
[ECOMtl].[EstScrap] as [ECOMtl_EstScrap]
from Erp.ECOMtl as ECOMtl
where NOT EXISTS (
select 1 from (Select [ECOMtl].[PartNum] as PartNum ,[ECOMtl].[RevisionNum] as Rev
from Erp.ECOMtl as ECOMtl
Where (ECOMtl.MtlPartNum like ‘%083’ and ECOMtl.MtlPartNum <> ‘000-083’)) as partrevs
Where partrevs.partnum = ECOMtl.PartNum and partrevs.Rev = [ECOMtl].[RevisionNum]
)