Grouping based on Formula

[rpal]

Hello,

I have problem in grouping. I am grouping based on the formula.
MY FORMULA:
IF {GLAMOUNTS.ACCOUNT} IN [400001,400111,400201,400301,600001,600101,600111,600131,600151] THEN "COMMISSION ANALYSIS- DIRECT"
ELSE
IF {GLAMOUNTS.ACCOUNT} IN [400002,400202,400302,600002,600102,600132,600202,600222,600252,600262] THEN "COMMISSION ANALYSIS- ASSUMED"
ELSE
IF {GLAMOUNTS.ACCOUNT} IN [400003,400203,400303,600003,600103,600133,600203,600223,600243,600253] THEN "COMMISSION ANALYSIS- CEDED"
ELSE
IF {GLAMOUNTS.ACCOUNT} IN [400001,400111,400201,400301,400002,400202,400302,400003,400203,400303,
600001,600101,600111,600131,600151,600002,600102,600132,600202,600222,600252,600262,600003,600103,600133,600203,600223,600243,600253] THEN "COMMISSION ANALYSIS- NET"

PROBLEM: I am not able to get group on "COMMISSION ANALYSIS- NET" Bez it has all account ranges.
Can any body suggest me how to procedd with this.

 

[Ngolem]

***********
PROBLEM: I am not able to get group on "COMMISSION ANALYSIS- NET" Bez it has all account ranges.
***********

This is because Crystal slots the data into appropriate groups before printing the report and you have told it to slot the data into 2 different areas...which it cannot do.

But this is easily solved.

Basically what you do is create a formula with a constant for a result when you want all the values used....a constant never changes and normally you cannot group on a constant but when mixed into a formula Crystal doesn't know the result until the report is executed so it is allowed.

In order to do this you must create a parameter that the user will enter to that will tell Crystal if you want to group all values together or breakdown into separate groups

{?GroupBreakdown} the choices will be "Y" (break up into groups) or "N" (generate a report based on a single group.
you would choose the most common choice as a default....I will assume "N" is the default for this example.


Your formula would look like this

//@groupingFormula

if {?GroupBreakdown} = "Y" then
(
IF {GLAMOUNTS.ACCOUNT} IN [400001,400111,400201,400301,
600001,600101,600111,600131,600151] THEN
"COMMISSION ANALYSIS - DIRECT"
ELSE IF {GLAMOUNTS.ACCOUNT} IN [400002,400202,400302,
600002,600102,600132,600202,600222,
600252,600262] THEN
"COMMISSION ANALYSIS - ASSUMED"
ELSE IF {GLAMOUNTS.ACCOUNT} IN [400003,400203,400303,
600003,600103,600133,600203,600223,
600243,600253] THEN
"COMMISSION ANALYSIS - CEDED"; //NOTE THE SEMI-COlON
)
ELSE
"COMMISSION ANALYSIS- NET";

Now you would create a group using this formula. You would probably want to suppress the group if you want all the accounts lumped into a single group so in the conditional suppress for the group header and footer I would put in the formula

{?Breakdown} = "N"

hope this helps





Jim Broadbent

 

[synapsevampire]

I think what you mean is that because it has no uniqueness, it will never be the group.

You cannot place a single row into numerous groups, that isn't what a group is in SQL or Crystal, if that's your intent, you would need another group level (formula) to accomplish this.

Or if you want to group those that do not fit into those predefined here, an else might read:

else
"N/A"

You might be better off to provide example data and expected output.

-k

 

[rpal]

COMMISSION ANALYSIS- DIRECT
---400001
400111
400201
400301
600001
600101
600111
600131
600151

COMMISSION ANALYSIS- ASSUMED
---400002
400202
400302
600002
600102
600132
600202
600222
600252
600262
COMMISSION ANALYSIS- CEDED
---400003
400203
400303
600003
600103
600133
600203
600223
600243
600253
COMMISSION ANALYSIS- NET
---400001
400111
400201
400301
600001
600101
600111
600131
600151
400002
400202
400302
600002
600102
600132
600202
600222
600252
600262
400003
400203
400303
600003
600103
600133
600203
600223
600243
600253

proble comes with "COMMISSION ANALYSIS- NET" Bez it using all account ranges which I used in other 3 groups(CEDED,ASSUMED,DIRECT)

 

[Ngolem]

try my approach...it works

Jim Broadbent

 

[lbass]

Why not treat "Commission Analysis-Net" as an outer group which contains the grouping Commission Analysis (Direct, Assumed, Ceded). Just use the portion of the formula for "Net" to create Group 1, and the rest of the formula for Group 2. Then all calculations can be made at the Group1 and/or Group 2 levels.

If you really want all records repeated, you could insert a subreport for Commission Analysis - Net.

-LB

 

[Ngolem]

I agree...a subreport is needed if you want all results repeated...then of course you don't need the parameter and the formula would be modified down to

//@groupingFormula

IF {GLAMOUNTS.ACCOUNT} IN [400001,400111,400201,400301,
600001,600101,600111,600131,600151] THEN
"COMMISSION ANALYSIS - DIRECT"
ELSE IF {GLAMOUNTS.ACCOUNT} IN [400002,400202,400302,
600002,600102,600132,600202,600222,
600252,600262] THEN
"COMMISSION ANALYSIS - ASSUMED"
ELSE IF {GLAMOUNTS.ACCOUNT} IN [400003,400203,400303,
600003,600103,600133,600203,600223,
600243,600253] THEN
"COMMISSION ANALYSIS - CEDED";

then the subreport would be placed in the group footer where it would be executed once all other data has been printed

Jim Broadbent

 

[rpal]

I don't want to do subreport, If I write stored procedure using union query, can get this output?.

COMMISSION ANALYSIS- DIRECT
---400001
400111
400201
400301
600001
600101
600111
600131
600151

COMMISSION ANALYSIS- ASSUMED
---400002
400202
400302
600002
600102
600132
600202
600222
600252
600262
COMMISSION ANALYSIS- CEDED
---400003
400203
400303
600003
600103
600133
600203
600223
600243
600253
COMMISSION ANALYSIS- NET
---400001
400111
400201
400301
600001
600101
600111
600131
600151
400002
400202
400302
600002
600102
600132
600202
600222
600252
600262
400003
400203
400303
600003
600103
600133
600203
600223
600243
600253