Grouping on formula 1

[AdamsHC]

I am trying to group sales data in CR9 based on varying criteria. Basically i want each group shown on the report to represent one sales division. I have created a formula for each of my sales divisions i.e '@group_01' is

{SALES.TERRITORY} = "HO" and
{SALES.ANALYSIS_CODES2} in ["SCOT", "NIRE"] and
not {SALES.ALPHA} in ["OCWIPEL", "OC8 CLEANI", "OC8", "CEANEL", "QUINODERM", "Hydromol", "HYDROMOL", "HYDROM", "CHLOROCLE", "CHLOROCLEN", "CHLOROCLENS", "CYCLENS", "PHARMACLE", "PHARMACLEN", "PHARMACLENS", "PHENO", "PHENOCLE", "PHENOCLEN", "PHENOCLENS", "QUATCLEN", "QUATCLENS", "SPIRICLEN", "SPIRICLENS", "PHARMAQUIP"]) and
{SALES.CLASS} in ["HOSP", "PH"]

another division is based on different criteria: (@group_02)

{SALES.PRODUCT} in ["1ANDARIK"]

I then call on all of my 46! sales division formulas from a formula called 'sales_groups' which looks something like this:

if

{@group_01}

then "01 - Home - IC - Hospital - Rep 01"



else if

{@group_02}

then "02 - Andarik"


else if

{@group_03}

then "03 - Home - IC - Hospital - Rep 03"

else if

{@group_04}

then "04 - Home - IC - Hospital - Rep 04"

etc etc.

I use the formula 'sales_groups' as my top level group. The problem is that although my sales division formulas cover every possible transaction scenario, i get a load of transactions that should appear in several of my divisions appearing in an un-named group. In the example above, the @group_2 does not populate with records, instead all of the records go into this un-named group.

 

[lupins46]

I would start by forcing the evaluation of the @sales_groups formula to be after all the other formulas using:

EvaluateAfter({@group_01});
EvaluateAfter({@group_o2})

etc

However, I'm not sure if this action will then prevent you from grouping on this formula. If so, then I think you will have to derive the group name for each record in one formula (using If statements) rather than several formulas.

 

[AdamsHC]

Sorry but where would the evaluate sfter statement sit - i put it into the sales_groups formula at the top but kept getting a compilation error.

I did original use if statements in one formula but had the same problem then also. Thats why i moved onto multiple formulas called from my group formula.

 

[lupins46]

You put the evaluateafter entries at the top of the formula.

I've just tried a simplified version of my suggestion and it seems to be fine in that the grouping is allowed.

This what my simple formula looks like:

EvaluateAfter ({@ddd});
if right({@ddd},1) >"m" then
"group1"
else
"Group2