Hi, I have read here in the forum, and followed the Epicor instructions for creating a BOM listing using a CTE query. The BAQ is giving me exactly what I want when I have the main part number set inside the query. This is normally how I test a BAQ before creating a BAQ report. With this BAQ though, when I setup a new BAQ report, and either an option field or a filter on the main part number, I am only getting the top level of the BOM. When viewing it in the BAQ, I get everything. So, I tried adding a subquery for the partrev, and then doing an inner join to the CTE subquery. Again, works fine in the BAQ, but when I try to filter or option, I get an error:
BAQ execution returned errors:
Invalid column name ‘PartRev_PartNum’.
Invalid column name ‘PartRev_RevisionNum’.
Is it possible to filter or use an option field with a CTE BAQ in a BAQ report?
@IronMB – I realize this is almost a year old, but I’m wondering if you had made any positive headway here. In my case, the underlying CTE BAQ I’m using fires off in a matter of milliseconds (when a subquery parameter is used). However, when that parameter is removed from the BAQ and recreated on the RDL instance, it takes 5 minutes and 45 seconds to merely return the passed parameter - and no other data - on the report.
I’m merely curious if you have any new info to share in your case.
Hi, I never did figure out how to use the CTE BAQ, or use a CTE query in SQL and then take advantage of that in Epicor. I ended up building a BAQ that just steps down through the levels of a Bom , because we know that our deepest Bom is 6 levels. It’s not super clean, and I can only use it with SSRS because I have to deal with the repetition. I’d be happy to send it or post it here next week, I’m at Insights right now.