BAD BAQ: Can't Combine these Subqueries into one

Epicor ERP 10
1

Need a BAQ that will sum 3 separate BAQ’s. Problem is I can’t figure out how to properly combine them into one. GOAL is to develop a dashboard by Target Job (calc field) for Net WIP balance & Total Job Costs. We have multiple WIP GL accounts so in order to audit my WIP GL Balances, the GL Acct is critical. By themselves, each subquery runs perfectly. I just can’t them to combine into one file. Note: we have special posting rules where all of the Job 2 job trans are supposed to post regardless if they are to the same GL acct or not. Did not always work so SQ3 below had to consider that.

1st Subquery as InnerSQ incls: Title:PartTrans = Parttran+TransGLC+Job Head.
Table criteria is Trantype <> WIP-MFG & Only show if posted to the GL (unposted is handled separately)
Fields incl: Related to File, JobNum (aka target job), Fiscal Yr, GLAcct Context (Must know if WIP, WIP2 or Blank), TranDate, NetGL$ (Trans Debit-Tran Credit), Job.ProdCode (drives WIP Accts), GL Acct (Seg 1), TransType (MFG-STK, etc).

_2nd Subquery as InnerSQ incls: Title: LaborTrans = LaborDtl +TransGLC+JobHead.
NO Table criteria but will only show if posted to the GL (unposted is handled separately)
Fields incl: Related to File, JobNum (aka target job), Fiscal Yr, GLAcct Context, TranDate, NetGL$ (Trans Debit-Tran Credit), Job.ProdCode (drives WIP Accts), GL Acct (Seg 1).

3rd Subquery as InnerSQ incls: J2J = Parttran+TransGLC+Job Head. Looks the same as Parttrans but is not.
Table criteria is GLAcctcontext = isnull constant + Trantype = MFG-WIP only
Fields incl: Related to File (calc field=J2J), Parttran.JobNum2 (aka target job), Fiscal Yr, TranDate (part.trandate-since no GL), NetGL$ (Part.ExtCost), TransType (MFG-WIP).

In a combined or Top Query, i need all trans from all 3 queries. Then in my dashboard, I intend to sum the NetGL$ by Target Job by GL Acct so I know by GL acct if what my balances or totals are by job. At the moment, nothing like this exists if you need to have multiple GL accts based on the Job.ProdCode.

HELP!!!

2

If you can normalize the columns (same number of columns in the same order - use dummy/constant values if you need to for columns that are valid in one query and not the others) then the three can be combined as UNIONs. Keep in mind that UNION will remove duplicate rows while UNION ALL keeps them.

Make SQ1 a CTE, SQ2 a UNION, SQ3 a UNION and then add a TOP LEVEL that just references SQ1. Add in any grouping/summing on the TOP LEVEL query and it will be an aggregate of the combination of SQ1, SQ2, and SQ3.

image

1 Like
3

Ok but how do i link them in the phrase builder for the Top? Or what does the Phrase Builder look like for the Top?

4

Click on the SubQueries icon above the list of tables and SQ1 will automatically be the only option to bring in.

image

The key here is that the UNIONs combine the results from SQ1, SQ2, and SQ3 and then having SQ1 as a CTE allows the top level query to see the combination as essentially a separate table.

1 Like
5

If you haven’t worked with CTEs before, here’s a quick rundown on the basic concepts:

They’re really handy for creating temporary datasets that get used in a query, similar to a temp table if you were working directly in SQL.

1 Like
6

This is perfect I think. It at least populates but now that I am proving it out, I think I need a table criteria that I am not getting right. How do I ask for only blanks when the table is NVarchar? For the SQ3 I need only GLAcctContext that are blanks. IsNull or Isblank is populating nothing yet there should be over 5k lines.

7

Add a Table Criteria for GLAcctContext equal to specified constant. Then click on the “specified” text and click OK on the resulting dialog box without entering anything. The value of “Empty string” is automatically supplied for the constant and filters on blank values.

8

You have been extremely helpful but this layout is NOT bringing in any trans from SQ2 which are the labor trans. I had to update the Unions to Union ALL because there are a number of duplicates that are required. They are not really duplicates but Epicor is seeing them as duplicates. It is mainly because WIP-MFG and MFG-WIP usually do not post so the part trans id # assigns bogus trans id #'s, believe or not. We have custom posting rules to post ALL trans in details. We just have too many different WIP accts for R&D and Production. Anyway, by having to use Union all my numbers are so much closer but not right still. Not sure if the UNION ALLS is causing the problem or what may be but I need the labor trans for the jobs to be included. Thoughts???

9

BAQ%20WIP%20Bal
BAQ WIP Bal.png1602×869 40 KB

See the Labor Trans setup. I had to create calculated fields to make them match part trans for the Union and I think they are accurate. But perhaps I am wrong.

10

oK. I figured it out. I had a stupid subquery filter on Labor Trans aka SQL2. I desperately need to hire someone internally for this. As the CFO, CPA, I am not educated for this stuff. Thank you for all your help.