Unorthodox BAQ output

,

I need to make a BAQ with a rather unorthodoxed output. It needs to be CSV-like, but not exactly. It is for creating a file that will be used to upload data into another system.

The format consists of several line types: “Hdr”, “Inv”, Dtl". and the format for each line type varies. Making each of these is easy enough, but making a BAQ with all three is where I’m hitting a wall.

Imagine it’s just Invoice data, pulling from InvcHead, InvcDtl, and Customer. A normal BAQ output might look like:
image

And the desired output:

"Hdr",20200124,3,6000.00
"Inv",12345,2,"CUS01",1000.00,"NET30"
"Dtl","WIDGET101",3,200.00,600.00,"PG-66"
"Dtl","FUEL-ABC",1,400.00,400.00,"PG-FU"
"Inv",12346,1,"CUS99",2000.00,"NET15"
"Dtl","JETPACK",1,2000.00,2000.00,"PG-JP"
"Inv",12349,3,"CUS22",3000.00,"NET30"
"Dtl","ANVIL",3,500.00,1500.00,"PG-AV"
"Dtl","BIRDSEED",10,100.00,1000.00,"PG-BS"
"Dtl","MAGNET",1,500.00,500.00,"PG-RR"
Details of the above structure

image

That’s for 3 invoices:

  • Inv# 12345, having 2 lines, totaling $1,000.00
  • Inv# 12346, having 1 line, totaling $2,000.00
  • Inv# 12349, having 3 lines, totaling $3,000.00

The first thing that should jump out at you, is the need for more rows than the data would really make in a normal BAQ. The example output is 10 rows, but there’s only 6 real rows of data.

I thought I could make a bunch of subquries with just one output column - the whole line would be in one field. The TopLevel query would generate the "Hdr",20210124, ... line. No problem.

Next I’d make a subquery for the "Inv", 12345,... lines. Making that subquery a Union, tacked those lines under the "Hdr",... line. Giving me:

"Hdr",20200124,3,6000.00
"Inv",12345,2,"CUS01",1000.00,"NET30"
"Inv",12346,1,"CUS99",2000.00,"NET15"
"Inv",12349,3,"CUS22",3000.00,"NET30"

If I do the same for the `“Dtl”,…" lines, I end up with:

"Hdr",20200124,3,6000.00
"Inv",12345,2,"CUS01",1000.00,"NET30"
"Inv",12346,1,"CUS99",2000.00,"NET15"
"Inv",12349,3,"CUS22",3000.00,"NET30"
"Dtl","WIDGET101",3,200.00,600.00,"PG-66"
"Dtl","FUEL-ABC",1,400.00,400.00,"PG-FU"
"Dtl","JETPACK",1,2000.00,2000.00,"PG-JP"
"Dtl","ANVIL",3,500.00,1500.00,"PG-AV"
"Dtl","BIRDSEED",10,100.00,1000.00,"PG-BS"
"Dtl","MAGNET",1,500.00,500.00,"PG-RR"

How can I make it so that the "Dtl",... lines follow the appropriate "Inv",... line?

Or is there another way to structure the BAQ so that summary lines can be generated by groups?

Would you be willing to post your BAQ? My first thought when you say grouping is a Window function and partition by group at the top level but I have no idea if that would actually work.

I think you could maybe fudge it using 3 different queries grouped differently then unioned. A query for the HDR, INV, and DTL. The only thing would be is you have to find a way to get them to sort the way you need them to in the end. Looks like maybe every query would need to include the date, then the INV and DTL ones the invoice num. I would set the invoice num field to 0 in the HDR query so it always sorts at the top of the group of a given date.
Then you have to figure out how to handle the column counts that would have to be done when you actually generate your file after you get it back from BAQ land.
I think that could work handedly. Reminds me of our postive pay BS we had to make.

Or break up the problem into manageable pieces:

  • BAQ to get the group and it’s data
  • PowerShell calls the BAQ and formats the data.
  • If it’s getting posted to a web service, pipe the results to another PS script to post the result
  • Finally, check the results of the post and update a UD field on the group in Epicor if the transfer was successful or not. Maybe even include a date time stamp…

Decomposition (computer science) - Wikipedia See also: Component-based software engineering - Wikipedia

All the cool kids are doing it. :wink:

I’m currently doing this as a SSRS report, passing the “raw” BAQ data, and using groupings in the RDL. But this only generates a PDF, and it needs to be a text file. Currently, the user runs the report, opens the PDF, does a Copy all, Launches Notepad, Pastes data, and saves the file.

I’m sure I simulate the BAQ in code using LINQ, and format the output to my hearts content. But was trying this in a BAQ more as an exercise than anything else.

@jgiese.wci - yes. It is the order of the rows. And it took multiple levels of subqueries, because having a COUNT and a SUM in the calculated field prevented me from doing a Group By on that field.

So I made a sub-sub-Query (InnerSubQ) that got me the desired output, but had multiple lines. So that was the source table of the sub-Query that had just the Calc line from the sub-sub, and could use Group By to remove the extras.

Here’s subqueries:

Lots of graphics in here

image

image

One Display Field
image

SubQuery#2: hdr_Subquery
image
(the criteria is just the InvoiceDate)

hdr_Subquery Display Columns
image

Calc_HdeLine:


Calc_InvcTots = SUM(InvcDtl2.ExtPrice)
Calc_Inv_Count = Count(DISTINCT InvcHead2.InvoiceNum)

That’s just the start of it. But you’ll get the gist (I hope)…

I guess the million dollar question is if I can specify the order of the Unions

I was thinking about adding a column to each sub-query row, to indicate it’s order. Hdr would 0, the first Invc 1, the first line of the invoice would be 1.1, the second line 1.2, the next invoice 2.0, and so on… But that might break the Group By that I use for removing duplicates.

Can you use functions or a BAQ with an empty query that just fires off code? If you need a flat file anyways maybe building the whole thing in pieces in code would be best and less fragile than a BAQ. Epicor does it for some of their reports there’s no shame LOL

1 Like

What do you get if you chose Output Format: CSV in the SSRS Print Preview window, instead of PDF?

image

The CVS format gives me something really funky…

My SSRS report basically has 2 Groups and a detail section. Here’s a sample of part of the PDF rendered output:

(Note that my actual data format varies from my prior examples. But the general need for intermediate group headers (the "I" lines) is the same.

image

When I choose CSV, it automatically opens in excel and appears as 3 columns One each for the three printable fields in my SSRS RDL.

I had tried CSV way back when I first made the report, but ran into some real problems. I think it mostly had to do with the numbers needing to be exactly 2 decimal places. CSV would strip the trailing decimals.
"Hello",10.00,"World" would be rendered in the CSV as "Hello",10,"World"

I needed it to be "Hello",10.00,"World"

If I tried making the value as string (like Format(cost, '####0.00') ), it would render as:
"Hello","10.00","World" And that "10.00" would fail the import.

Just curious what you are importing to?

The data in your example kind of reminds me of something I had to do for American Express… a long time go in V8.
Where we ended up building the files via a BPM, custom code…
e.g. example section related to Invoice Lines…
/************** Start 0301 INVOICE Fields **************************/
For Each APTran where APTran.Company = CheckHed.Company and APTran.Headnum = CheckHed.HeadNum no-lock.
If Available APTran then DO:
ACCUMULATE APTran.InvoiceNum(COUNT).
DEFINE VARIABLE cInvcTranCD AS CHARACTER NO-UNDO FORMAT “x(4)”.
DEFINE VARIABLE cInvcID AS CHARACTER NO-UNDO FORMAT “x(35)”.
DEFINE VARIABLE cInvcAMT AS CHARACTER NO-UNDO FORMAT “x(11)”.
DEFINE VARIABLE cInvcCred AS CHARACTER NO-UNDO FORMAT “x(50)”.
DEFINE VARIABLE cInvcEnd AS CHARACTER NO-UNDO FORMAT “x(1)”.
cInvcTranCD = “0301”.
cInvcID = STRING(APTran.InvoiceNum).
cInvcAMT = STRING(ApTran.DocTranAmt * 100,“99999999999”).
cInvcCred = FILL(’ ‘,50).
cInvcEnd = FILL(’ ',1).
PUT UNFORMATTED
cInvcTranCD At 1
cInvcID At 5
cInvcAMT At 40
cInvcCred At 51
cInvcEnd At 900.
/
End Invoice Lines 0301 */

Can you make it in a view or a TVF and then use an External BAQ?

It’s the home brewed accounting system of the parent company.

They gave me very detailed info on the structure of each line type, Including:

Sequence   Description  Note   Size  Type   Value
10         A/R Type       g      1     A

Note g - Can be specific to each division, many are uniform. Used to determine the A/R General Ledger Control Account. Some possibilities: C - Contract Bill / O - Other / T - Trade A/R / A - Advance Payment. Must be a valid code on file in J&E system.  * WILL ALWAYS BE C "

There are several “working solutions”. I was trying to see how to do it in a way that is most maintainable (primarily all within Epicor).

In a nutshell, my question is …

Given the data set

InvcNum   CustID  InvTotal  Terms
123450    XYZ123  1,000.00   Net30
123451    XYZ123  2,000.00   Net30
123452    PDQ999  3,000.00   Net15

Can a BAQ generate:

"H","2021-01-25", 3, 6000.00
"I", 123450, "XYZ123", 1000.00, "Net30"
"I", 123451, "XYZ123", 2000.00, "Net30"
"I", 123452, "PDQ999", 3000.00, "Net15"

Points to note, 1) number of columns depends on the line type. 2) more rows than the original data set

If you currently have a BAQ outputting what you need, why not just use the Sort Order tab to order the output?

Because there isn’t a column to sort on.

The output looks like:

image

The bubble numbers are the order the row should be in.

Header
  Inv 1
    Inv 1, Line 1
    Inv 1, Line 2
    ... remaining lines of Invoice 1 ...
  Inv 2
    Inv 2, Line 1
    ... remaining lines of Invoice 2 ...

You don’t have to use an output column to use the Sort Order tab.

image

I’ve used it before but have also come across some BAQs done by other people where it didn’t work. So I am not too sure what it’s limitations are.

Yes but that tab requires you select a column available to the TopLevel query.

And since I’m using UNION subqueries to add the details to an Invoice Row, and then another UNION to add the Invoice Rows to the Hdr row, I can’t see anything in those subqueries joined via UNION’s, and thus can’t use their data for sorting.

Add in the data to the queries that you need and then have the union be a CTE so you can put a top level over the CTE and sort the output.

So, have your first query out put this

Output Invoice Num Invoice Date Invoice Line Sort
“Hdr”,20200124,3,6000.00 0 1/24/2021 0 44220-0-0

Second query

Output Invoice Num Invoice Date Invoice Line Sort
“Inv”,12345,2,“CUS01”,1000.00,“NET30” 12345 1/24/2021 0 44220-12345-0

Third query

Output Invoice Num Invoice Date Invoice Line Sort
“Dtl”,“WIDGET101”,3,200.00,600.00,“PG-66” 12345 1/24/2021 3 44220-12345-3

And then make a top level where you can sort on the sort column

Output Invoice Num Invoice Date Invoice Line Sort
“Hdr”,20200124,3,6000.00 0 1/24/2021 0 44220-0-0
“Inv”,12345,2,“CUS01”,1000.00,“NET30” 12345 1/24/2021 0 44220-12345-0
“Dtl”,“FUEL-ABC”,1,400.00,400.00,“PG-FU” 12345 1/24/2021 1 44220-12345-1
“Dtl”,“WIDGET101”,3,200.00,600.00,“PG-66” 12345 1/24/2021 3 44220-12345-3
“Inv”,12346,1,“CUS99”,2000.00,“NET15” 12346 1/24/2021 0 44220-12346-0
“Dtl”,“JETPACK”,1,2000.00,2000.00,“PG-JP” 12346 1/24/2021 1 44220-12346-1
“Inv”,12349,3,“CUS22”,3000.00,“NET30” 12349 1/24/2021 0 44220-12349-0
“Dtl”,“MAGNET”,1,500.00,500.00,“PG-RR” 12349 1/24/2021 1 44220-12349-1
“Dtl”,“BIRDSEED”,10,100.00,1000.00,“PG-BS” 12349 1/24/2021 10 44220-12349-10
“Dtl”,“ANVIL”,3,500.00,1500.00,“PG-AV” 12349 1/24/2021 3 44220-12349-3
1 Like

I think there is a BAQ example knowledgebase on EpicCare - for the sorting of indented BOMs using hierarchy and Ind
image

Yes, but @ckrusen is not doing a recursive CTE, he is trying to sort 3 unioned queries.

1 Like