Solved

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

Posted on 2009-07-16
5
594 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
[X]
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
5 Comments
 

Author Comment

by:knausscpa
ID: 24871792
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
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
0
 
LVL 35

Accepted Solution

by:
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
  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 101

Expert Comment

by:mlmcc
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.

mmlmcc
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

717 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