Using a filter with a multilevel BAQ in BAQ Report designer

Epicor ERP 10
, ,
1

I created a BAQ to provide a pick list by bin number. My BAQ is a little complicated and has 3 subqueries and one top level. I can add table criteria in the top level to restrict to only specified job numbers.

I then created a BAQ Report - ideally, I want to use the filter option to pick the job numbers to print. But when I try to add it - the drop down has JobHead_JobNum three separate times. I don’t know which to use. I tried using each one individually and wasn’t able to get any to work.
image
I should also add that I removed the criteria from the BAQ when I was attempting to achieve this through the BAQ Report.

Does anyone have any ideas?

Thanks for the help! I’m new to Epicor and not very talented in this area!

2

Lets see your BAQ. I’m guessing you are displaying this same field multiple times on the top level.

3

Here are the display fields for the top level

image
image695×505 18.4 KB

4

I don’t see anything.

Do you have a subquery? I was expecting to see JobHead_PartNum and Subquery_PartNum

Nevermind, that is displaying correctly.

You could post screenshot of your top level diagram maybe.

5

My BAQ report designer does the same thing when sub-queries are used. I normally filter on the SSRS report, and havent on the designer in a long while.

Sorry if i added more confusion.

6

I have a subquery…

Here’s what I have

Innersubquery1 - Called “primarybin”. This uses the Erp.PartBin table and calculates a new field called primary bin as the lowest bin number.

Innersubquery2 - Called “SubQuery1” This combines the Erp.JobHead with Erp.JobAsmbl and Erp.JobMtl. This does have JobHead_JobNum as a display field

Innersubquery3 - Called “sumbypart”. This calculates the total pick qty by grouping a bunch of fields from SubQuery1. This also has JobHead_JobNum as a display field. (which I think I need for the group by total)

and finally - the Top Level - called “Combinejobandbin” and this merges sumbypart, primarybin, and PartBin1… and this also has JobHead_JobNum as a display field…

7

can I add a filter on the SSRS report that asks for user input? I want someone to be able to enter a JobNumber and get the associated pick list

8

Normally its this simple.

image
image905×213 14.1 KB

image

9

yeah! :rofl: That’s what I was hoping for!!! I can get all that - but when I submit the report I get an error -
BAQ execution returned errors:
Bad SQL statement.
Review the server event logs for details.

10

Can you execute your BAQ without the designer?

11

I can add criteria and execute in BAQ fine… but I won’t be the one printing daily pick sheets…

image
image771×158 7.68 KB

12

Sorry if you already know this:

Your error might be caused by a blank SSRS report.

So one thing with the BAQ Report is that you still have to edit the SSRS report in CustomReports folder. It creates a blank template upon saving in BAQ Designer.

Issue I haven’t got around is that every time you change the BAQ you have to create a new BAQ Report Design, which will create a blank SSRS. You can however copy the tables from one sheet to another.

image
image1060×464 18.4 KB

13

Thank you for the tip - but the SSRS report is not blank. If I add criteria to filter on BAQ and then run the BAQ Report without a filter it runs great. I just don’t want to have to add the criteria to the BAQ each time.

And I have found the BAQ Report to be very wonky when making changes to the query after the Report has been created.

Thanks for the help! I’ll keep plugging away at it… Where there’s a will there is a way!!!

14

You should not have to recreate the BAQ report after changing the BAQ. Sync Dataset should take care of that.

15

I would recommend avoiding the BAQ Report Designer. You will have much better luck/customizing options with a RDD BAQ report.

16

Learning curve on Report Data Definition is steep, at least for me.

I agree though it is better.

17

That is what I thought, till I did one myself. And now wonder why did I not use it in the first place.

1 Like