Using a UBAQ in a BAQreport - filtering the data

Epicor ERP 10
1

I needed to write a query to interrogate attachments and determine for which ones the file paths were broken in some way.
I thought a UBAQ might be employed and found a thread on here from a little while ago detailing how to implement just such a technique and so avoiding a load of head scratching on my behalf.
Basically a GetList post processing directive on the UBAQ manipulates dummy calculated columns. Thank you - works great!

Anyway, then comes the next stage; Can it be made into a BAQ report with ā€˜filtersā€™ on the computed columns.
This doesnt seem to work. I sort of expected this might happen. The report option I guess is being applied to the SQL query before the post processing directive alters the resulant dataset

Any thoughts how i might get around this?

2

Is your goal to use a report parameter (input on the Report form, not an actual SSRS report paramater),as an input to the uBAQ, to be used to calculate a new value for an updatable field in the uBAQ?

Like if you wanted to replace part of the path in the image stores. Where input1 is the old path and Input2 is the new path.

Imagine your regular BAQ shows:

ImgID  Description  File name  Path
=====  ===========  =========  ==================
00001  Nut, 1/4-20  nt420.png  c:\Images\Parts
00002  Nut, 3/8-20  nt3820.png c:\Images\Parts
...
00100 Nut Assy 101 asy101.png  c:\Images\SubAssys
00101 Nut Assy 201 asy201.png  c:\Images\SubAssys

And
you need to update the Path of all the sub assemblies to be
c:\Images\Subs

So your uBAQ takes replaces any instance of Input1 with Input2 in Path giving you:

ImgID  Description  Filename   Path                Calc_NewPath
=====  ===========  =========  ==================  ==================
00001  Nut, 1/4-20  nt420.png  c:\Images\Parts     c:\Images\Parts
00002  Nut, 3/8-20  nt3820.png c:\Images\Parts     c:\Images\Parts
...
00100 Nut Assy 101 asy101.png  c:\Images\SubAssys  c:\Images\Subs
00101 Nut Assy 201 asy201.png  c:\Images\SubAssys  c:\Images\Subs

And then have the Path be updated with the new path?

3

Or do you just need the report to be filtered after the calc fields calculations are complete? If its that, then use a Sub-query Criteria in the uBAQ.

To be honest, Iā€™m not sure where the ā€œupdatableā€ part of your BAQ comes in to play.

4

Heā€™s running C# code in the BPM on the updateable part which is filling in information in the grid after the query runs.

@richardh, yeah, youā€™re going to have problems with that in the report. Those BPMā€™s donā€™t usually run the same way when you are running a report. Iā€™m not sure how to get around it though.

5

Iā€™d just stick with an updatable dashboard

6

You donā€™t even need an updateable dashboard. A regular one will do fine. The BPMā€™s on an updateable BAQ will still run. The updateable part of the dashboard lets you change things on the dashboard. Which he probably isnā€™t doing here.

1 Like
7

Yes. it is a non-updating updateable BAQ! I am just using a post processing directive to re-write a column in the returned dataset. It is quite a neat techinique really.

I came up with one solution for the BAQ report option; I added a pre-defined ā€œReportParamsā€ - Check01
These are exposed to the report definition. as the dbo.BAQReportParameter_xxxxxxxxxxxxxx table

Then i have modified the query for the dataset to filter on this value.

Itā€™s a rather inefficient process in SQL terms - the BAQReportResult_xxxxxxxxxxxxxxxxx table could contain 1000s of rows which are then filtered in the report generation.