Finding Maximum on a formula?

[BigDaddyE]

I have a report that does a count on a visit field that needs to ignore suppresed items. This formula is called @Count. Now I want to use the maximum @Count result in another formula but I do not know how. Please help, example below.

Patient Start End @Count Min Max Visit
John Doe
9/6/10 10/5/10 3 6
1 9/7/10
2 9/9/10

Attempting to take the maximum Count which is 2 in the example above, if it less than the Min then crRed else crNoColor.

Below is what I tried, but I got an error.

If {Table.Min}> Maximum({@Count}) and {Table.End} < CurrentDate then CrRed Else crNoColor

ERROR: This field '@Count' cannot be summarized.

 

[fisheromacse]

you could create a new formula to check and collect the maximum of {@Count}

{@MaxCount}
numbervar ct;
if ct < {@Count}
then ct := {@Count}
else ct := ct;
ct


You would also probably want to reset the variable between patients,correct? Create another formula and place it in the group header.
{@ResetMaxCount}
numbervar ct := 0;

 

[BigDaddyE]

This just gave me the same number that I already had. I need something to just return the Max @count so I can change the colors, but I continue to get the error

ERROR: This field cannot be summarized.

 

[fisheromacse]

please clarify for me.

Did you put the {@MaxCount} formula in the details section?

Did you want the # to reset? If so, is the {@ResetMaxCount} in the correct group header?

The {@MaxCount} should display the largest # it has found in the detail records up to the current record.

You then would use the {@MaxCount} to determine your formatting.

If {Table.Min}> {@MaxCount} and {Table.End} < CurrentDate then CrRed Else crNoColor

 

[lbass]

You have the sign backwards and you need the whileprintingrecords--in both formulas. Should be:

{@MaxCount}
whileprintingrecords;
numbervar ct;
if ct > {@Count}
then ct := {@Count}
else ct := ct;
ct

However, because the variable is based on a sequence of values--the maximum could change per record. So I think the best solution would be to create a SQL expression to return the count. Then you would be able to insert a maximum on the {#count}. For help with this, please show the content of {@count} and show your current "show SQL query".

-LB

 

[BigDaddyE]

The SQL options seems like the way to go.
I can email you a pdf that displays what I am attempting to do.
I have also attched the SQL for this report,

***And below is the content of the count.***********

Whileprintingrecords;
If ({visit.vst_date} in {FCDCycleHeader.CycleStartDate}) to ({FCDCycleHeader.CycleEndDate}) then
numbervar linecnt := linecnt +1;
****************************************************



***SQL BELOW***************************************

SELECT DISTINCT "patient"."med_rec_num", "patient"."last_name", "patient"."first_name", "visit"."vst_date", "substation"."sub_code", "vsrn"."vsrn_code", "episode"."team_code", "episode"."dschg_date", "episode"."delete_stamp", "patient"."delete_stamp", "substation"."delete_stamp", "vsrn"."delete_stamp", "FCDCycleHeader"."CycleEndDate", "FCDCycleHeader"."MinReq", "FCDCycleHeader"."MaxReq", "episode"."soc_date", "FCDCycleHeader"."CycleStartDate", "visit"."dscp_code"
FROM (((("EncoreClinP"."dbo"."visit" "visit" INNER JOIN "EncoreClinP"."dbo"."episode" "episode" ON "visit"."ep_key"="episode"."ep_key") INNER JOIN "EncoreClinP"."dbo"."vsrn" "vsrn" ON "visit"."vsrn_key"="vsrn"."vsrn_key") INNER JOIN "EncoreClinP"."dbo"."FCDCycleHeader" "FCDCycleHeader" ON ("visit"."dscp_code"="FCDCycleHeader"."dscp_code") AND ("episode"."ep_key"="FCDCycleHeader"."ep_key")) INNER JOIN "EncoreClinP"."dbo"."patient" "patient" ON "episode"."pat_key"="patient"."pat_key") INNER JOIN "EncoreClinP"."dbo"."substation" "substation" ON "episode"."sub_key"="substation"."sub_key"
WHERE "episode"."delete_stamp" IS NULL AND "patient"."delete_stamp" IS NULL AND "substation"."delete_stamp" IS NULL AND "vsrn"."delete_stamp" IS NULL AND "episode"."dschg_date" IS NULL AND "substation"."sub_code"='87' AND "episode"."soc_date" IS NOT NULL AND NOT ("vsrn"."vsrn_code"='F' OR "vsrn"."vsrn_code"='FM') AND "patient"."med_rec_num"='738160' AND "visit"."dscp_code"='CHAP'

*********************************************************