BAQ: more than one Rev is approved

Epicor ERP 10
,
1

I have created a BAQ which returns all approved Revs, counts the approved Revs per Part, then shows how many Revs are approved. It looks like this (code below).

I would like this to be emailed on a daily basis for all parts where the Approved Rev count > 1. The issue that I am running into is that the COUNT function only executes at the end, so I am not able to filter by COUNT>1 so the email will contain all parts (more than 100,000 parts).

Normally I would do this filter in a Dashboard, but because they want this emailed each day I am seeking a way to do the filter in the BAQ. Is this possible? I tried a subquery but it fails, I assume for the same reason (COUNT executes at the very end). Any suggestions?

select 
	(COUNT(PartRev.RevisionNum) OVER(PARTITION BY PartRev.PartNum)) as [Calculated_RevCount],
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartRev].[RevShortDesc] as [PartRev_RevShortDesc],
	[PartRev].[ApprovedDate] as [PartRev_ApprovedDate],
	[PartRev].[ApprovedBy] as [PartRev_ApprovedBy],
	[PartRev].[EffectiveDate] as [PartRev_EffectiveDate]
from Erp.PartRev as PartRev
where (PartRev.Approved = 1)
order by RevCount Desc
2

You could use a group by and having clause right?

3

SELECT partrev.company,PartREv.PartNum
from Erp.PartRev as PartRev
where (PartRev.Approved = 1)
Group by partrev.company,partrev.partnum
Having Count(distinct PartRev.RevisionNum) > 1

2 Likes
4

Genius, thank you Utah. That works great!

5

Ok, so this code works as expected in the BAQ Designer Analyze tab. It shows all parts and their count where count>1.

However, when I go to create a BAQ Report using this BAQ, it only shows the first part number in the list and shows the total count of all Revs. It doesn’t show all parts and their counts like it does in the BAQ. I don’t understand why the behavior is different in BAQ Report Designer than it is in the BAQ Designer. Any ideas?

6

Here is what we use.

As for the having count query. I am guessing it isn’t showing part revs when there is only one item.

(Select a.PartNum, a.RevisionNum, a.RevShortDesc
                  From [PartRev] a
                  Inner Join ( -- Use Left Join if the records missing from Table B are still required
                      Select partnum, SysRevID, RevisionNum,
                          ROW_NUMBER() OVER (PARTITION BY partnum ORDER BY  ApprovedDate DESC) As _RowNum
                      From [PartRev]  where Approved = 1 and EffectiveDate <= getdate() 
                              ) b On b.partnum = a.partnum and a.RevisionNum = b.RevisionNum
                  Where 1 = 1 and b._RowNum = 1 
                  ) as RealPartRev
1 Like
7

I don’t know if I have ever experienced that… your BAQ report should list all the same rows that come in the BAQ dataset…

8

Well, after playing around with BAQ Report Designer for a bit, this issue is happening regardless of what BAQ I use. Only the first result is being displayed. Something is funky in BAQ Report Designer land.

9

Just in case anyone else has the same issue with BAQ Report Designer, here is the fix:

1 Like
10

Yes, definitely need a table or matrix.

1 Like