Counting one record out of mulitiples 1

[lholodnak]

Hi all, using crystal 10, with oracle tables. My enduser needs a report that shows all service requests that have a work order was responded to within 2 hours but that also has a work code of "RPR". One Service Request can have many work orders attached to it. I have a formula that calulates the date diff to find all the response times (If {@Convert date to number} <= 2 then 1 else 0) Example of my records are:

sr 1423
wo1 work code INV 1 (1 from the formula)
wo2 work code RPR (null from the formula)
wo3 work code ISP 1 (1 from the formula)

I need to distinctcount all sr's that have a work code of RPR but also has a work code of INV that was responded to in 2 hours or less. Wo2 above does not have a date because the crew has not been assigned to the job, so I get a null field for RPR. Just can't seem to get this one. Thanks, Lisa

 

[lbass]

First group on {table.svcreq}. Then create two formulas for the detail section:

//{@INV}:
if {table.workcode} = "INV" and
{@datediff} <= 2 then 1 else 0

//{@RPR}:
if {table.workcode} = "RPR" then 1 else 0

Then if you want to display only those groups that meet your criteria, go to report->edit selection formula->GROUP and enter:

sum({@INV},{table.svcreq}) > 0 and
sum({@RPR},{table.svcreq}) > 0

If this doesn't work, then it might have something to do with the contents of your {@convertdate} formula, so please share it.

Also, if you don't want to display only the groups that meet the criteria, instead of using a group select, you could create a formula that identifies the group as meeting the criteria:

if sum({@INV},{table.svcreq}) > 0 and
sum({@RPR},{table.svcreq}) > 0 then "Meets criteria"

-LB

 

[lholodnak]

Hi LB, I created the two formulas and then because I don't want to display only the groups that meet my criteria, I did you other formula. They all work like a charm. My only other question is how do I create a running total using the sum formula, it is not a option when I try to do a running total. thanks, again, L

 

[lbass]

Then you would use a manual running total. Change the formula to (or add another formula like the following) to be placed in the service request group header or footer:

if sum({@INV},{table.svcreq}) > 0 and
sum({@RPR},{table.svcreq}) > 0 then 1

Let's call the formula {@meetscrit}. If the count is for the report footer, then use two formulas:

//{@accum} to be placed in the service request group header or footer:
whileprintingrecords;
numbervar cntmeetscrit := cntmeetscrit + {@meetscrit};

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar cntmeetscrit;

If you want to summarize at a higher order group instead of at the report level, then add a reset formula in the higher order group header:

//{@reset}:
whileprintingrecords;
numbervar cntmeetscrit := 0;

And place the display formula in the higher order group footer. If you also wanted to have a report total then you would have to add a separate variable to accumulate and display for the total count.

-LB

 

[lholodnak]

Thank you LB this got me where I needed to be. Have a great weekend. L