Group on Formula (WhilePrintingRecords; )

[Bunting]

I have a formula which commences with the statement (WhilePrintingRecords).

I need to insert a group on the result of this formula.

I understand that selection for the grouping occurs before the formula is evaluated, thus making it impossibe for crystal to perform what i'm asking.

Are there any options open to me ?

 

[IanWaterman]

It would be helpful to know what you are trying to achieve.

Ian Waterman
Crystal Programmer UK

 

[Bunting]

Thanks for your prompt reply.

What I am trying to achieve is to create a group based on a formula.

The problem is that I cannot reference the formula in the group expert because it is evaluated after the grouping occurs.

I'm not sure what other information I could give to assist?

 

[Naith]

An example of your data, and how you would like to see it grouped.

 

[Bunting]

I have two formulas in my report.

Formula one shows the "number" of working days passed between the (currentdate) and another date field in my report{v_debt.dt~datinstr}.

FORMULA ONE
-------------
WhilePrintingRecords;
DateVar Start := {v_debt.dt~datinstr};
DateVar End := currentdate;
NumberVar Weeks;
NumberVar Days;
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1
+ (if DayOfWeek(Start) = 1 then -1 else 0)
+ (if DayOfWeek(End) = 7 then -1 else 0);
Weeks + Days

Formula Two looks at the results of Formula One.

FORMULA TWO
---------------

if {@FORMULA ONE} in 30 to 60 then "Chase 1"
else
if {@FORMULA ONE} in 61 to 90 then "Chase 2"
else
if {@FORMULA ONE} > 90 then "Chase 3"

What I am then looking to do is group on FORMULA TWO so that I will have three groups
GROUP ONE = (CHASE 1)
GROUP TWO = (CHASE 2)
GROUP THREE = (CHASE 3)

When I go to insert a group (@Formula Two) does not appear in the wizard because I believe that grouping occurs before the formula is evaluated.

Are there any options open to me so that I can effectively group on the result of Formula Two?
Thanks for your help !

 

[Naith]

Change Formula One to

((Truncate (CurrentDate - dayofWeek(CurrentDate) + 1 -
({v_debt.dt~datinstr} - dayofWeek({v_debt.dt~datinstr}) + 1)) /7 ) * 5)
+
DayOfWeek(CurrentDate) - DayOfWeek({v_debt.dt~datinstr}) + 1
+ (if DayOfWeek({v_debt.dt~datinstr}) = 1 then -1 else 0)
+ (if DayOfWeek(CurrentDate) = 7 then -1 else 0)

Naith

 

[Bunting]

Many Thanks Naith - That worked fine