BAQ Report, filter based on Job Closed, Open or both

Epicor ERP 10
, ,
1

Hi there,

I’ve created a BAQ report to show jobs based on the criteria that the Jobs are due within 2 dates. I’ve created Option Fields for the due date so the user can select these 2 dates.

Now the need has arisen for the user to be able to filter the results based on whether the Jobs are Open, Closed or to display all.

I’ve tried a few methods but with no luck. The method I’m trying at the moment has an nvarchar Calculated Field that contains an nvarchar parameter. This Calculated Field is used in the Option Field. I’ve customised the field to display a drop down with values instead of a text box.

  • If the user selects ‘Open’ then it should display only Open Jobs.
  • If the user selects ‘Closed’ then it should display only Closed Jobs.
  • If the user selects ‘All’ then it should display both Open and Closed Jobs.

As you can see below, I’m using the Calculated column result in the WHERE clause. I know that normally you don’t need to use an Option Field in a WHERE clause, so I’m a bit unsure how to proceed.

and (JobHead.JobClosed = (case
  when IncludeClosedJobs = 'Closed' then 1
  else 0
 end)  or JobHead.JobClosed = (case
  when IncludeClosedJobs = 'Open' then 0
  else 1
end))
2

Have you tried just adding more option fields in the BAQ report designer?

3

No I haven’t.

For the sake of end user simplicity, I’ve been instructed to make it a single control :frowning:

I thought a drop down would be easiest to display.

4

This is a workaround using a drop down instead of a check box.

5

You will need two check boxes as options for open and closed. If none are checked then you get all jobs, else it depends on the option selected.

Vinay Kamboj

6

While it’s a valid option, it’s an annoying way to set it up for the user. If you check both check boxes, you get none, as well as the fact that you can’t easily default a checkbox to true when the dashboard opens up. Most users will immediately hit refresh before adding any filter, running the BAQ wide open and running returning every job you have ever done will now make them wait for that to load. Using the drop down hack, you can default the selection to open jobs so, which is what they are going to want a majority of the time anyways.

7

That can be an issue. In that case the drop down on the Dashboard tracker as you suggested would be a better option.

Vinay Kamboj

8

The question is for BAQ report and in report options the date fields always default to today’s date so you will get all jobs for today’s date only and no risk of getting a large data returned by the BAQ report.

However, dashboards are always better option and we always discourage reports.

Vinay Kamboj

9

I ended up using this method.

I added a label explaining the functionality to the user, so it’s a good enough solution for the problem.

Epicor should definitely add a way to filter booleans based on 1, 0 or both options.

10

ahh, I totally glossed over that detail. I push everything to dashboards like @Vinaykamboj so I don’t usually think about reports. Sorry about that.