Count Formula with Group

[rbh123456789]

CR 11.5

I need a method in Crystal (perhaps a formula) what will produce the same number of records that my below SQL statement does:
-----
select caseid from schema.view where status = 4
group by caseid
-----
Basically, i want a total count of 'caseids' with a 'status' equal to 4. The records must be grouped by caseid because there are sometimes multiple rows per record.

Thanks everyone

 

[MCuthill]

rbh123456789,

I beleive the following RunningTotal will return the results you seek. As I do not have Crystal Reports in front of me at the moment, there may be some fine tuning needed on the syntax.

{@VariableReset_UniqueCases} -- Place in Report Header

[blue]WhilePrintingRecords[/blue];
[blue]Shared NumberVar[/blue] UniqueCases:=0;

{@VariableIncrement_UniqueCases} -- Place in Details

[blue]WhilePrintingRecords[/blue];
[blue]Shared NumberVar[/blue] UniqueCases;

[blue]IF[/blue] {Table.Status} = 4 [blue]AND[/blue] {Table.CaseID} <> [blue]Next[/blue]({Table.CaseID}) [blue]THEN[/blue] UniqueCases:=UniqueCases+1 [blue]ELSE[/blue] UniqueCases;

{@VariableDisplay_UniqueCases} -- Place in Report Footer

[blue]WhilePrintingRecords[/blue];
[blue]Shared NumberVar[/blue] UniqueCases;

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

Thanks Mike,

I found that adding a SQL Expression with the following code did the trick:

(
select count(distinct caseid) cd from VIEW where status = 4
)

This gave me the proper calculation.

Thanks again!