display only 3 most recent records 2

[timISST]

I have a report that is grouped by person and I need to show there 3 most current dates. It also needs to eliminate people with LESS than 3 dates.I am using Crystal 2008. Example would be

John Smith:
6/6/12
5/3/12
2/7/12

Thanks!

 

[hilfy]

Getting the three most current is fairly simple.

1. Group by person.
2. Sort by date descending.
3. Create a running count of the date field that will reset at the change of the person group.
4. For the details section that displays the dates, go to the Section Expert and set the suppress formula to suppress the section when the date count > 3.

Filtering out those that have less than three dates is a bit more complicated and I can think of a couple of ways to do it. Here is one that just uses Crystal features (as opposed to setting up a command in SQL):

1. Create a formula that gives you the count of the dates per person (this is different than a running count!)
2. Go to the Group Selection Expert and set it up to filter out data where the formula created in step 1 < 3.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[timISST]

how would i set up the suppress to ignore greater than 3 like this? DCount>3 sorry still fairly new to crystal

 

[timISST]

I may have missed part of what i needed though I DO need it to show the 3 dates

 

[timISST]

I came up with this formula to ignore the data I didnt want but it still shows up ({#Dcount})>3 and ({@LessCount})<3

 

[hilfy]

Change the "and" to "or" and try it again. That should fix the issue.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[timISST]

WHILEPRINTINGRECORDS;

SHARED NUMBERVAR N;

IF ({x}) = 'v' then
N := N+1 else
IF ({x}) = 'y' then
N := N+1 else
IF ({x}) = 'z' then
N := N+1 else
N := N+0;

out put of the items
null
1
2
3
the total formula which is simply
WHILEPRINTINGRECORDS;

SHARED NUMBERVAR N;
N;
when i run the report it will sometimes throw back a 4 and if I have one that is all nulls it will throw a 1?? any ideas

 

[Madawc]

There's an easier way. First define a Formula Field to count your 'First Three':

if {x} in ["v", "y", "z"]
then 1
else 0

Display it on a temporary detail line, something that shows what is happening and can be hidden or removed once you have the output you want.

Right-click on the field and let Crystal create a running total. The use of Crystal's automated totals is outlined at FAQ767-6524, and you'd find them useful.

Display the running total, confirm that this too works and is re-set for each group. Then do the line suppression.

Incidentaly, Shared Variables are only needed for communication between a subreport and its main report. Elsewhere you just say 'Variable'. But the automatic totals are mostly more useful.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[timISST]

that worked thank you!! now just have to tweak it to run between "A" start date to end date and have "A" give me the last 3 dates as well!!

 

[timISST]

I have this working so far BUT I need it to only pull the data based on the event happening today and back the 2 dates
IE
6/18/2012 as the start date
5/18
3/18

Right now it pulls everything that has had something happen from today back to the beginning of time any ideas?

 

[Madawc]

timISST, have you started a new thread on this topic? If so, I have just answered this, but it is bad practice.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP