BAQ Report - Y/N/All

Hi all,

I added a Y/N type Option Field in an existing BAQ Report called “Hide if complete” which hides customer order lines if: completed quantity = required quantity.
By default it is ticked as Y, which means the report only shows order lines which are not complete and/or partially complete.

If the user unticks this option, the report shows only the lines where completed quantity = required quantity.

So to summarise, I created the calculated field as a BIT type, which means I only have two options. Therefore, the report user can either see (1) completed orders, or (2) uncompleted orders.

Instead, I would like the user to be able to see (1) completed orders, or (2) ALL orders together. Is there any way to do this?

Thanks,
Himal

To get all, just add an OR to your selection "(your current condition) OR (not HideIfComplete)

I’m struggling to get my head around it.
I’ve got this for now in the BAQ calculated field:

if completed quantity = required quantity then 1
else 0
end


Would you suggest to modify this?
or in the report?

How are you prompting to include completed? A Parameter? If so, you want to put a selection on your query on the SubQuery Criteria.

image

The criteria would look like (with a calculated field assigned to your parameter field):

(qtyRemaining <> 0 and @excludeClosed) OR (not @excludeClosed)

1 Like

What I do, is to make a Calculated field, of typ nvarchar, that returns “YB” if the boolean field is true, and “NB” if it is false.

Then in Reports and Dashboards, you can filter on that field, with a filter type CONTAINS. Then the user can Enter a ‘Y’ to get records where the boolean field is true, ‘N’ for records where it is false, and ‘B’ for both.

Technically you don’t even need to Enter ‘B’, as leaving it blank also returns all records.

Edit

Check out the following for more details

2 Likes

Thanks guys. sorted.