1211433 - Displaying only the record with the closest date to a parameter, for each group

How can you set up a report to prompt users to enter a date via a parameter, then have each group in the report display only the record with the closest date to the parameter? 

The date field value for the record displayed must be before or equal to the date in the parameter.

To have each group display only the record with the closest date to a user-specified date, where the record's date must be before or the same as the date in the parameter, create a formula that flags how close each record is to the parameter date value.  

You can then filter and sort the report by this flag formula. Next, create another set of formulas to count how many records are in each group, and conditionally suppress the details section when this counter formula returns a value greater than one.

To do this, follow the steps below.

1. In the report, create a date parameter:

· On the 'Insert' menu, click 'Parameter Field'.  

· Click 'New'. This opens the Create Parameter Field dialog box.

· Type a name and prompting text for the parameter.

· In the 'Data type' box, select the Date data type.

· Click 'OK' to return to the report.

2. Insert the date parameter into the report header section.

3. Create the following formula to check whether the current date is the same, later than,

or earlier than the date parameter:

· On the 'Insert' menu, click 'Formula Field'.  

· On the list, click 'Formula Fields' then click the 'New' icon.

· Enter a name for the formula, then click 'OK'. This opens the Crystal Reports Formula Editor.

· Enter this formula:

//@Flag

//Checks whether the current date is the same, later than,

//or earlier than the date parameter

NumberVar flag;

//If current date is the same as the date parameter,

//flag record with 0

if {date field}={?date parameter} 

then flag:=0 

else

//If current date is later than date parameter,

//flag record with 6666

if {?date parameter} - {date field} < 0 

then flag := 6666 

else

//If current date is earlier than date parameter,

//flag record with number of days difference between

//date parameter and current date

flag:= {?date parameter }- {date field}

4. Place this formula in the details section of the report, beside the date field. This formula ranks each record by how close the date field is to the parameter value (with 0 being the closest, and 6666 indicating the date field occurs after the date parameter value)

5. Sort the records within the group by this formula field, in ascending order:

· On the 'Report' menu, click 'Sort Records'. The 'Record Sort Order' dialog box appears.

· In the 'Report Fields' box, click the flag formula field, and click 'Add' to copy it to the 'Sort Fields' box.

· In the 'Sort Direction' section, click 'Ascending'.

· Click 'OK' to return to the report.

The report now displays the records where those with dates closest or equal to the date parameter value are at the beginning of the group. Records with dates that occur after the date parameter display at the end of each group.

6. Create a record selection formula to filter out records with dates that occur after the date parameter value:

· In the 'Report' menu, click 'Edit Selection Formula' then point to 'Record'. This opens the Record Selection Formula Editor.

· Enter the following formula:

//Excludes all records where the Date field 

//has a later date than the date parameter value.

NumberVar flag;

flag<>6666

· Save the formula and close the Formula Editor.

When you next preview this report, only those records with date field values that occur on or before the date parameter value are returned.

7. Create two formulas that will create a counter variable. This counter flags when Crystal Reports has already printed the first record for a group:

//@Reset

//Resets counter for each new group

whileprintingrecords;

numbervar counter:=0

//@counter

//Increments the counter for each record in a group

whileprintingrecords;

numbervar counter:= counter + 1

· Place {@Reset} into the group header section on the report.

· Place {@Counter} into thedetails section on the report.

8. Format the details section to conditionally suppress when the counter is greater than 1: 

· On the 'Format' menu, click 'Section'. This opens the Section Expert dialog box.

· On the 'Sections' list, click the details section.

· On the 'Common' tab, click 'X+2' beside the 'Suppress' check box. The conditional formatting formula editor appears.

· Enter the following formula:

//conditionally suppresses section

//when counter is greater than 1

whileprintingrecords;

numbervar counter;

counter > 1

· Click OK to return to the report.

The report now displays only the record with the date field closest to the date parameter value, for each group.

closest date dates parameter Seagate Crystal Reports Ranking records using formulas Limiting groups to display only x number of records using conditional suppresion counter formulas , c2005926 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports all versions