Filtering Formula Based on Two Fields

Crystal Reports 11.5.3.417 / Windows 7

I need help with a formula to filter specific data from a report I'm using.

I have a company report for purchase orders. One of the fields on the report is 'PO.STATUS' (STRING), to which I only have 'approved' or 'close' purchase orders showing in Select Expert.

However, I also have the field 'PO.TOTALCOST' (NUMBER) on the report.

The problem I'm having is that 'PO.TOTALCOST' is bringing in all PO totals, despite my filters for 'PO.STATUS' of 'approved' or 'close'. Is there a way to only show 'PO.TOTALCOST' amounts for 'approved' or 'close' filters used in 'PO.STATUS'?

Thanks in advance.

How are you doing the filtering?  I would seem that is you are using the Select Expert?  That should have put an AND between each conditional.

Thanks for the suggestion.

The Select Expert filter I'm using is this:

{PO.CONTRACTREFNUM} = "4400005536" and
{PO.STATUS} like ["APPR", "CLOSE"]

However, upon closer inspection, I changed it to this:

{PO.CONTRACTREFNUM} = "4400005536" and
{PO.STATUS} like ["APPR", "CLOSE", "REVISD"]

The problem I'm having is that if a PO is revised (REVISD), the database still keeps record of the closed (CLOSE) PO's. Is there a way to filter the records so that if a PO is revised, it eliminates the closed PO's?

For the report, the PO.TOTALCOST is bringing in all data for both REVISD and CLOSE PO's, which makes PO.TOTALCOST inaccurate.

are you summing PO.TOTALCOST or is it an already summed value?

I'm using two Running Total Fields for PO.TOTALCOST

Type of summary for both are SUM.

One is set For Each Record.

One is set for On Change Of Group.

However, the individual PO's that are revised are showing the revised (REVISD) total, and the original total (CLOSE).

Using the Running Total Field, is there a way to apply a filter so that if a PO is revised (REVISD), it eliminates the closed (CLOSE) PO amount in PO.TOTALCOST?

You can use an evaluate formula where you have 'for each record'
something like
PO.status = 'REVISD'


Edited by DBlank - 18 Sep 2017 at 9:11am

Sorry I didn't describe this well enough and I really appreciate your help with this.

Since my report is pulling in multiple PO's, there are many with a status of CLOSE that I need to show on the report.

However, there are only a few that are REVISD. With the REVISD PO's there is also a copy of it with a status of CLOSE. So with this said, every REVISD PO is pulling in double the PO.TOTALCOST (REVISD & CLOSE).

For my report, I need to pull in all PO's with a CLOSE status, except for the REVISD PO's, where I need to eliminate the CLOSE copy, and show the REVISD.

Sorry for any confusion and thanks again for your time.

Not knowing the actual data it is a bit difficult...
I assume first that you cannot exclude the duplicates via a stored proc or the like.
Secondly I assume that each row has the same PO # but a different PO.Status for the 'duplicates'.
You should be able to group on the PO # and create a group "flag" to indicate which PO has a revised data set. I think you are filtering by just those two statuses...
insert a summary as the max(po.status,po.po#)
any group where the max = 'REVISD' is now your grouping indicator.
IN your Running total use that in your evaluate condition.
(max(po.status,po.po#) = 'REVISD' and status = 'REVISD')
or
(max(po.status,po.po#) = 'CLOSED' and status = 'CLOSED')

Thanks for your suggestions. With your help I've been able to correct the issues I was having.