Can you group on a formula field?

[Lhuffst]

I have a report that calculates the status of an organization. This part works fine. What I need to do now is create a group using that status but when I do insert group, the formulas I've created are not available.
ex.

Detail lines look like this.
101 Andrews Compliance
2 Rockwood compliance
399 Pepsi Non-Compliance

What I need is to do
The following users have a status of compliance.
101 andrews
2 Rockwood

The following users have a status of Non compliance.
399 Pepsi

Each New Status will start on a new page and total the number of users meeting that status category.


Can I do this using grouping?
thanks
Lhuffst

 

[tpeters1]

I just created a formula and grouped on it. Works for me.

What is the code for your formula?

Are all the inputs for the formula available within the current row?

 

[lbass]

You'll have to show us the content of your formula for compliance.

-LB

 

[Lhuffst]

The formula I'm using is:

if {@monthlychro%}>=66.6 then
if {@monthlytrc%}>=33.3 then monthly_status:='SNC Chronic, TRC'
else monthly_status:='SNC Chronic'
else if {@monthlytrc%}>=33.3 then monthly_status:='SNC TRC'
else if {@monthlychro%}>0 then monthly_status:='NC'
else monthly_status:='C'


Does the fact that it uses other calculated formulas prevent you from grouping on it?
Thanks everyone.
Lhuffst

 

[tpeters1]

This formula is dependent upon two other formulas

{@monthlychro%}
{@monthlytrc%}

Please post the code for those as well.

 

[Lhuffst]

Here are all the monthly formulas that were created.
initmonthly
shared numbervar monthlyobs:=0;
shared numbervar monthlychro:=0;
shared numbervar monthlytrc:=0;

monthlyChro
whileprintingrecords;
shared numbervar monthlychro;
if not isnull({REPORT_LINE.E_CONC_AVG}) then
if {REPORT_LINE.E_CONC_AVG}>0 then monthlychro:=monthlychro+1;

monthlychro
whileprintingrecords;
shared numbervar perc_monthlychro;
if {@print_monthlyobs}>0 then
perc_monthlychro:=({@print_monthlychro}/{@print_monthlyobs})*100

monthlyjobs
whileprintingrecords;
shared numbervar monthlyobs;
if not isnull({REPORT_LINE.CONC_AVG}) then monthlyobs:=monthlyobs+1;

monthlystatus
whileprintingrecords;
shared stringvar monthly_status;
if {@monthlychro%}>=66.6 then
if {@monthlytrc%}>=33.3 then monthly_status:='SNC Chronic, TRC'
else monthly_status:='SNC Chronic'
else if {@monthlytrc%}>=33.3 then monthly_status:='SNC TRC'
else if {@monthlychro%}>0 then monthly_status:='NC'
else monthly_status:='C'



Monthlytrc
whileprintingrecords;
shared numbervar monthlytrc;
if not isnull({REPORT_LINE.E_TRC_CONC_MAX}) then
if {REPORT_LINE.E_TRC_CONC_MAX}>0 then monthlytrc:=monthlytrc+1;

monthlytrc%
whileprintingrecords;
shared numbervar perc_monthlytrc;
if {@print_monthlyobs}>0 then
perc_monthlytrc:=({@print_monthlytrc}/{@print_monthlyobs})*100

Print_monthlychro
whileprintingrecords;
shared numbervar monthlychro

Print_monthlyjobs
whileprintingrecords;
shared numbervar monthlyobs;

print_monthlytrc
whileprintingrecords;
shared numbervar monthlytrc;

Perc_monthlychro
whileprintingrecords;
shared numbervar perc_monthlychro;

Thanks for any suggestions/help
lhuffst

 

[tpeters1]

The fact that the formula dependencies have the command "whileprintingrecords;" may preclude the grouping of the data on that forumula.

"whileprintingrecords;" forces the program to evaluate the formula while it is printing database record data. I would assume that whatever grouping has been defined in in the reports has already been processed at "printing" time and that the data cannot be re-grouped based on "compliance" at the time of "printing".

Do you really need all of these formulas to evaluate "WhilePrintingRecords"?

 

[Lhuffst]

Not really sure. I inherited a report some someone who left and of course there wasn't any documentation as to the real intent. I have the dubious honor of taking this information and reformatting it differently. Since the report was previously working with the formulas, I figured if I could just change the groups then my problem was solved. Since I have to print by status, I will go back to the drawing board and try to figure another way out.
thanks everyone
lhuffst

 

[lbass]

The big question is whether shared variables were used because they were coming from subreports. It looks like they weren't, so you can probably just convert these formulas to eliminate the variables and the "whileprintingrecords" and then you will be able to do the grouping.

-LB

 

[Lhuffst]

Thanks to everyone. I just changed whileprintingrecords to whilereadingrecords and it seems to run fine. I did have to change the shared variables to regular numbervar and so far it look great. Thanks again to everyone who responded.
Lhuffst