Solved

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

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

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 100

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports  Formula to Command 4 82
Store total calculated duration sessions. 44 82
Crystal Reports 2008 6 32
Null as value for Dynamic parameter in CR2008? 9 46
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

777 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