Filtering Formula Based on Two Fields
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22451
Printed Date: 07 May 2024 at 2:09pm
Topic: Filtering Formula Based on Two Fields
Posted By: jgarner
Subject: Filtering Formula Based on Two Fields
Date Posted: 14 Sep 2017 at 10:28am
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.
|
Replies:
Posted By: kevlray
Date Posted: 18 Sep 2017 at 5:12am
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.
|
Posted By: jgarner
Date Posted: 18 Sep 2017 at 6:06am
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.
|
Posted By: DBlank
Date Posted: 18 Sep 2017 at 7:07am
|
are you summing PO.TOTALCOST or is it an already summed value?
|
Posted By: jgarner
Date Posted: 18 Sep 2017 at 8:05am
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?
|
Posted By: DBlank
Date Posted: 18 Sep 2017 at 9:10am
You can use an evaluate formula where you have 'for each record'
something like
PO.status = 'REVISD'
|
Posted By: jgarner
Date Posted: 18 Sep 2017 at 9:30am
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.
|
Posted By: DBlank
Date Posted: 21 Sep 2017 at 3:11am
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')
|
Posted By: jgarner
Date Posted: 22 Sep 2017 at 4:45am
|
Thanks for your suggestions. With your help I've been able to correct the issues I was having.
|
|