group formula

[zsyed]

I've a column in my data source with lot of duplications. I do have two columns put under group option. One need a maximum value and other need sum of all those maximum values. I can get first one by placing formula field as shown below, but don't know how to put the other one using the first one. Any help is appreciated.

if {?Dropdown_Group} = 2 then
maximum({CX_GL_SALESBYDIV.budgetamount},{@GroupOption})

Example data

custid partgrp budget
ab001 xyz 100
ab001 xyz 100
mc001 xyz 200
mc001 xyz 200

Groupoption
1: customer
2: partgrp


desired output

custid partgrp budget
grp1 ab001 xyz 100
grp1 mc001 xyz 200
------------------------------
grp2 xyz 300
-------------------------------

 

[Madawc]

You could have got the maximum without the need to code. The use of Crystal's automated totals is outlined at FAQ767-6524.

But for the maximum of maximum, I think you'll need a variable or two to sum them.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[zsyed]

Thanks Madwac, I got it on how to use automated totals. However, I've little complexity that group option is not fixed. moreover, I've two levels of group options. It seem like Im would need many combination of evaluation formaulas.

 

[IanWaterman]

Create 3 formula

@reset\\ place this in group header and suppress
whileprintingrecords;

global numbervar total:=0

@eval \\ place in group footer and suppress
whileprintingrecords;

global numbervar total:=0

if {?Dropdown_Group} = 2 then
total:=total + maximum({CX_GL_SALESBYDIV.budgetamount},{@GroupOption})

@display \\place in report footer
whileprintingrecords;

global numbervar total;

Ian

 

[zsyed]

Thanks Ian. This formula works in one direction. i.e. If I choose to group by customer then group by partgroup. However, If I choose to group only by customer how would I summarize only discrete values from each cusotmer and part group combination.

For example:

custid partgrp budget
ab001 xyz 100
ab001 xyz 100
ab001 pqr 200
ab001 pqr 200
Groupoption
1: customer
2: nothing ('')


desired output

custid partgrp budget
grp1 ab001 '' 300

Like wise there might be a possibility of looking the report to see group only by partgroup.

 

[zsyed]

If I could have a formaula something like this, then I could use this in ' eval ' with condition:
total:=total + sum(maximum({CX_GL_SALESBYDIV.budgetamount},by customer by partgroup),{@GroupOption});

 

[IanWaterman]

You will need two different vars which sum at different points depending on your group structure.

I assume your group option formula uses a parameter so incorporate that into the eval and diplay of the two vars.

Ian

 

[zsyed]

Hi Ian,
Thanks for the suggestion. Fortunately or Unfortunately it did not worked. Multiple variable won't work in my case because of Group changes from part group to customer, and moreover, I've two levels of groups.
The good thing is I managed to resolve this conundrum by eliminating duplicates in the data. That reduced the need of Maximum value and hence automatically fixed the summaries according to groups in two levels.

Regards,
Z