| Author |
Message |
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
   Topic: Filtering Formula Based on Two Fields
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.
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Sep 2017 at 7:07am |
|
are you summing PO.TOTALCOST or is it an already summed value?
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
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?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Sep 2017 at 9:10am |
|
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
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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')
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
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.
|
|
IP Logged |
|
|
|
