Solved

Filtering Crystal Records - Show all Records  if any record matches a criteria

Posted on 2009-07-16
5
576 Views
Last Modified: 2013-11-15
I am trying to modify an existing report to show all scans if there was a scan in the last month. Database is Access 97.  The DB linking SS is attached, as well as my current filters.  Logically it seems like it should be something like this: "If {newtrack.job} has a {Newtrack.Date}  in LastFullMonth then show all records for all dates for that {newtrack.job}"

Any help turning that into crystal language would be appreciated. My main problem is when i try and filter Newtrack.date It ends up filtering out just those records that occured last months and displays only those. I need it to display stuff previous to that as well but only if there is a date in this month preferably a date that has a station 100 code in the last full month.  From the link SS newtrack.station would have code 100 nwpotrk.laststation (last scan to newtrack.station) and Station.number would also have code 100 (reference list)
Current Filters:

 {Newtrack.Station} <> 10001 and

not ({STATION.Station_Name} startswith ["IN BILLING", "INTERRUPTED", "LUNCH", "Lunch IN", "LUNCH OUT", "PARTIAL", "RESUME", "SHIPPED", "STAGED"]) and

{NWPOTRK.Dollar_Amt} > $2499.00 and

{NWPOTRK.Last_Station} in [100, 16] and

{NWPOTRK.Ordered} > DateTime (2008, 06, 01, 00, 00, 00) and

(IsNull({NWPOTRK.Requested}) OR {NWPOTRK.Requested} > DateTime (2008, 12, 01, 00, 00, 00)) and

not ({STATION.Station_Number} in 90000 to 99999) and

{Newtrack.Date} in LastFullMonth

Open in new window

Capture.jpg
0
Comment
Question by:knausscpa
5 Comments
 

Author Comment

by:knausscpa
Comment Utility
Added Note: Newtrack has several records per job # (records each scan to a job) NWPOTRK has 1 record per job number.
0
 
LVL 5

Expert Comment

by:jgbreeden
Comment Utility
Seems like you will need to use a sub report.  If you put a sub in the detail section of the main report, then you could pass the newtrack.station to the sub, then in the sub use a similar filter but set newtrack.station=?PM-newtrack.station and remove the part about Date in LastFullMonth
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
First of all, LastFullMonth is the previous calendar month.  For example, as of today, it would be June 1 - June 30.  Is that what you mean by "in the last month", or are you looking for dates within the past month, ending with today (eg. June 17 - July 16)?

 If you have a group on {newtrack.job}, I think you can get just those jobs to show by using a group selection formula.  Create a formula like the following (call it whatever you like):

if {Newtrack.Date} in LastFullMonth then
  1

 All that does is output a 1 if Date is in LastFullMonth.

 Then go to Report > "Selection Formulas" > Group and enter the following:

Sum ({@formula created above}, {newtrack.job}) > 0

 That tells CR to only show a job group (ie. job) if the total from that first formula is > 0 (ie. where at least one Date was in LastFullMonth).

 If you try that, don't forget to take the {Newtrack.Date} in LastFullMonth out of your record selection formula.

 Note that a group selection only suppresses the groups that don't meet the criteria.  The report will still read all of the records and any regular summaries in the report will still include them.  Also, if the report is displaying the group tree, the groups (jobs) that aren't shown will still be listed in the tree, but if you try to select one of them, you'll just be taken to the nearest job that is _not_ suppressed.

 I'm not sure exactly what you were trying to do with the "code 100" stuff, but if you only want to check the date on certain records, you can probably handle that by adding the appropriate tests to the first formula above, so that, for example, it only outputs 1 if the date is in range _and_ newtrack.station = 100 (or whatever).

 James
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Agree.  I cant think of any way to include all records if there is a current record except through a subreport.

You might be able to do it through some fancy SQL code.

mmlmcc
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now