Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Posted on 2009-07-16
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

Question by:knausscpa

Author Comment

ID: 24871792
Added Note: Newtrack has several records per job # (records each scan to a job) NWPOTRK has 1 record per job number.

Expert Comment

ID: 24875070
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
LVL 34

Accepted Solution

James0628 earned 500 total points
ID: 24875619
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

 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).

LVL 100

Expert Comment

ID: 24879053
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.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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