External BAQ setup

Epicor ERP 10
1

Greetings,

I’m attempting my first external BAQ with no real idea what I’m doing. I’m trying to follow directions from Epicare, but you know how that goes…

I’ve been unable to create the query I need in Epicor, it’s either not possible or I just can’t figure out the subquery/grouping madness. I was able to create a working query in SSMS, so switching gears to try and pull that view into a query and then into a dashboard.

So far I have a view created in SSMS

image
image701×402 17.6 KB

I created a datasource type:

image
image764×473 15.1 KB

Created a new whatever this is:

image
image804×494 27.7 KB

Enabled the datasource:

image
image671×336 7.32 KB

Created a new Ext BAQ. I do not see the view I created in SSMS. I’m not sure what I expected to see here, but this surely isn’t it :slight_smile:

image
image611×439 14.4 KB

Can anyone tell where I went wrong based on the screen shots provided?

2

Does not look like ERP database, are you sure you point connection string to it and not to master for example.

2 Likes
3

That’s quite possible! Let me go and check, thank you!

4

Looks like that was my problem, I did not have the initial catalog set to our live DB.

image
image573×451 11.7 KB

Another hurdle cleared, thank you so much, Olga!

1 Like
5

If you want to post your query from SSRS we can probably walk you through how to do it in BAQ land.

2 Likes
6

That would be awesome, I never mastered the subquery shift from E9 where I could just summarize a table. The query is for a material shortage tracker, and I was attempting to pull in the next due open PO release. I was able to bring in the next POrel due date with a subquery grouped by partnum, but unable to also pull in the PONum, line, release and promise date due. I then tried setting the subquery to the top row sorted by rel due date, but that didn’t work either. Anyways, thanks for offering, I can always use more examples of how to navigate the subquery approach!

Here is the query from SSMS:

SELECT 
    [JobHead].[JobNum]              AS [JobHead_JobNum],
    [JobHead].[DueDate]             AS [JobHead_DueDate],
    [JobHead].[PartNum]             AS [JobHead_PartNum],
    [JobHead].[ProdQty]             AS [JobHead_ProdQty],
    [JobMtl].[PartNum]              AS [JobMtl_PartNum],
    [Part].[PartDescription]        AS [Part_PartDescription],
    [JobMtl].[RequiredQty]          AS [JobMtl_RequiredQty],
    [JobMtl].[IssuedQty]            AS [JobMtl_IssuedQty],
    [JobHead].[TravelerLastPrinted] AS [JobHead_TravelerLastPrinted],

    [NextPO].[PONum]                AS [NextPO_PONum],
    [NextPO].[POLine]               AS [NextPO_POLine],
    [NextPO].[PORelNum]             AS [NextPO_PORelNum],
    [NextPO].[DueDate]              AS [NextPO_DueDate],
    [NextPO].[PromiseDt]            AS [NextPO_PromiseDt]

FROM Erp.JobHead AS JobHead
INNER JOIN Erp.JobMtl AS JobMtl
    ON JobHead.Company = JobMtl.Company
   AND JobHead.JobNum  = JobMtl.JobNum
   AND JobMtl.IssuedQty < JobMtl.RequiredQty
   AND JobMtl.PartNum  <> ''
   AND JobMtl.RequiredQty <> 0

LEFT OUTER JOIN Erp.Part AS Part
    ON JobMtl.Company = Part.Company
   AND JobMtl.PartNum = Part.PartNum

OUTER APPLY (
    SELECT TOP (1)
        PODetail.PONum      AS PONum,
        PORel.POLine        AS POLine,
        PORel.PORelNum      AS PORelNum,
        PORel.DueDate       AS DueDate,
        PORel.PromiseDt     AS PromiseDt
    FROM Erp.POHeader AS POHeader
    INNER JOIN Erp.PODetail AS PODetail
        ON POHeader.Company = PODetail.Company
       AND POHeader.PONum   = PODetail.PONum
    INNER JOIN Erp.PORel AS PORel
        ON PODetail.Company = PORel.Company
       AND PODetail.PONum   = PORel.PONum
       AND PODetail.POLine  = PORel.POLine
    WHERE 
        PODetail.PartNum    = JobMtl.PartNum
        AND POHeader.OpenOrder   = 1
        AND POHeader.Approve     = 1
        AND PODetail.OpenLine    = 1
        AND PORel.OpenRelease    = 1
    ORDER BY PORel.DueDate
) AS NextPO

WHERE 
    JobHead.ClosedDate  IS NULL
    AND JobHead.JobReleased = 1
    AND Part.TypeCode = 'P'
7

BAQ does not have OUTER APPLY for subqueries. You can replace it with LEFT JOIN and partition.

here is what LLM says:

Suppose you have

SELECT a.Col1, b.Col2
FROM TableA a
OUTER APPLY (
    SELECT TOP 1 Col2
    FROM TableB b
    WHERE b.AId = a.Id
    ORDER BY b.Date DESC
) b;

Here:

  • TableA is the outer query.
  • The subquery returns one row per a.Id.

Equivalent with LEFT JOIN

You can rewrite it using a LEFT JOIN with a derived table or window function:

SELECT a.Col1, b.Col2
FROM TableA a
LEFT JOIN (
    SELECT b.AId, b.Col2
    FROM (
        SELECT b.*,
               ROW_NUMBER() OVER (PARTITION BY b.AId ORDER BY b.Date DESC) AS rn
        FROM TableB b
    ) b
    WHERE b.rn = 1
) b ON b.AId = a.Id;
2 Likes