Crystal Formula...tricky

[rhoneyfi]

I have a report that shows Customer Complaints. The main report shows the date the complaint was submited and the status of the complaint(In Process, Complete, etc...) The subreport shows all the actions that were taken regarding the complaint, along with the date of the action. I would like to create another report that shows Customer Complaints that were completed yesterday. What I need is a formula that says "If the status = Completed AND the last action date = yesterdays date then include this record (and the subreport)on the new report. How do I go about doing this? Keep in mind that I don't have a "last action date" field...I only have "Action date" in the subreport. I am using CR 10 connected to a SQL server. Thanks

 

[kskid]

More technical information would be more helpful along with the narrative

Samnple of your input data
Sample of the expected output (in each section of your main report)
Subreport input data, links and expected output

 

[scottostanek]

Make a formula:
@LastActionDate

Whileprintingrecords;
global DataVar LastDateActed;

If the status = Completed then
LastDateActed :={Action date};
LastDateActed

and another in the header of the group to reset it.
@ResetActionDate
Whileprintingrecords;
global DataVar LastDateActed := '1/1/1900'
//or something to represent blank

Then reference the @LastActionDate whenever you want to test.

Scotto the Unwise

 

[lbass]

Insert a subreport in the report header. In the subreport, group on complaint ID and create a formula:

whileprintingrecords;
shared datevar array max := date(0,0,0);
redim preserve max[distinctcount({table.complaintID})];
shared numbervar counter := counter + 1;
shared numbervar array y := 0;
redim preserve y[ubound(max)];
shared stringvar result;

y[counter] := {table.complaintID};
max[counter] := date(maximum({table.actiondate},{table.complaintID}));
result := result + totext(y[counter],0,"") +" "+ totext(max[counter],"MM/dd/yyyy")+", ";

Place this formula in the group footer of the subreport. Suppress all sections of the subreport.

In the main report, go to the section expert(format->section)->group header/details/group footer->suppress->x+2 and enter:

whileprintingrecords;
shared datevar array max;
numbervar counterx := 0;
shared stringvar result;
stringvar resultx;

for counterx := 1 to distinctcount({table.complaintID}) do(
if {table.complaintID} = val(split(split(result,", ")[counterx]," ")[1]) then
resultx := trim(split(split(result,", ")[counterx]," ")[2]));
resultx <> currentdate - 1 or
{table.status} <> "Completed"

There are probably simpler formulas to accomplish this, but I think this should work.

-LB

 

[baldr1c]

An easier way would be to copy your current report and in the record selection have complaint status = completed and have the action date = today -1

 

[lbass]

baldr1c--

The issue is that the date is ONLY available in the subreport.

-LB