Count instances of string created in formula at end of report 1

[blevy33]

I have created a formula in CR7 that looks at detail and returns a string value if the number in the formula is a certain value. For example

if {@count}=1 then "Honors Certificate" else
if {@count}=2 then "Honors Medal" else

and so on. My problem is that I also need to place a total at the end of the report which counts how many "Honors Certificates" I need to order and how many of each of the other awards I need to order. I surmise that some type of formula of summary should be placed in the footer of the report, but nothing seems to work.

Can any one help?

 

[lbass]

The simplest approach would be to insert a crosstab into the report footer and use your unnamed formula above as the row, and then insert the formula again as the summary field, and choose "Count" as the summary.

Alternatively, you could create a formula for each formula result, e.g.:

if {@yourformula} = "Honors Certificate" then 1 else 0

...and insert a grand total sum on this. Repeat for each potential formula result.

-LB

 

[blevy33]

I thought of that but neither of my formulas called "count" or "award" appear in the fields that I can select for the cross tab even though they are in the report. The formula "count" is

Sum ({@hr},{@sort} )

that formula is then referenced by the formula "award"

Have I created a convoluted mess?

 

[lbass]

Come totally clean --what's in {@hr} and {@sort}?

-LB

 

[lbass]

Actually, my apologies--I should have realized your formula was a summary. Try the following:

Create a formula {@sumaward} and place it in the group header or footer where you are identifying the award (substituting your own variable and award names):

whileprintingrecords;
numbervar BlueR := BlueR + (if {@award} = "Blue Ribbon" then 1);
numbervar RedR := RedR + (if {@award} = "Red Ribbon" then 1);
numbervar WhiteR := WhiteR + (if {@award} = "White Ribbon" then 1);

Then create a second formula {@displayawards} and place it in the report footer:

whileprintingrecords;
numbervar BlueR;
numbervar RedR;
numbervar WhiteR;
stringvar output;

output := "Blue Ribbons: "+totext(BlueR,0,"&quot + chr(13)
+"Red Ribbons: "+totext(RedR,0,"&quot + chr(13)
+"White Ribbons: "+totext(WhiteR,0,"&quot

Then select the formula->right click->format field->common->and check "Can Grow". This will give you the following:

Blue Ribbons: 5
Red Ribbons: 3
White Ribbons: 7

-LB

 

[blevy33]

@hr
if {StudGrades GPAMC.GPA Entry}>=3.5 then 1

//this allows me to count the instances of GPA above 3.5 during a student's 4 years

@sort
{Student.Grade Level Now}+{Student.Last Name}+{Student.First Name}+{Student.Import ID}

//this is used to sort records for 1100+ students

I'm not sure that @sort belongs in the formula "count" but I created it using an example from the help section of CR7 and I am not opposed to change. I realize that some things work when they shouldn't or that they create problems later.

Thanks for your help!

 

[lbass]

You must be grouping on {@sort} for your formula {@count} to work. Creating {@count} (which is actually a sum--a count wouldn't work here) so that it sums separately for each instance of {@sort} seems fine as long as it returns only one record per student throughout the four-year period. You wouldn't want to give more than one award per student, right? As long as {Student.Grade Level Now} doesn't create multiple records or limit the return of records over the four-year-period, it should be fine.

Did our posts cross? My previous suggestion should work.

-LB

 

[blevy33]

Our posts must have crossed, but your assumption was correct and your solution was exactly what I needed. I now understand a little more about CR and how to create certain reports. Thanks for your help.