Excluding Null Dates in MINIMUM Summary Function

[teddles]

CR8.5/Oracle

I have a formula field in a report that holds dates of which some equate to a null value via date(0,0,0). The report details are grouped and I want to determine the minimum date per group for this formulated date field - ignoring the null dates. As it stands, if I do a Minimum summary on this formulated field it returns null.

 

[lupins46]

You could use a variable in a formula to do this.

The basic logic is:

datevar MinDate;
if (not isnull({mytable.myfield})) and ({mytable.myfield} <MinDate) then Mindate = {mytable.myfield};
""

In the report header you need another formula to set a high value for MinDate:

DateVar MinDate:= datevar("31/12/2199");
""

Then where you want to show the min value you have a third formula just outputing the variable:

Datevar minDate;

 

[Madawc]

Or you could try a running total - I assume you don't know about them.

If you use running totals, they are working out at the same time as the Crystal report formats the line. This means you can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

If you use summary totals, they are taken directly from the data and can be shown in the header. They can also be used to sort groups, or to suppress them.

To get a running total, right click and choose Insert and Running Total. Or else use the Field Explorer, the icon that is a grid-like box.

Madawc Williams (East Anglia, UK)

 

[bdreed35]

You could also use a formula and a summary:

Create this formula, place it in the Details section, and Suppress it:

if IsNull({table.date}) then
Date(9999,12,31)
else
{table.date}

Next, right click this field on the report and choose Insert , Summary.
Change the Summary type to minimum, and the location to the appropriate group.

~Brian