Sorting a summary

[ajxmas]

Hello All,
I am using CR XI. I'm totally new to CR. I am creating a Sales report with many different parameter and sort options for the user to select from. One of the sort options is descending by total $ sold per customer. Because of CR's multi-pass processing I cannot sort on a running total or a summary. So I tried creating a formula to calculate the total $ but it doesn't work quite right.

Here are the formulas I have now:

@total - placed on Detail(will be suppressed)
global numbervar total := total + {VENDOR_SALES.AMT_SOLD}
(If I use WhilePrinting I get the right $ but I still can't sort on it. If I use EvaluateAfter I can sort on it but the final $ isn't quite right)

@reset total - placed on Group Header
WhilePrintingRecords;
global numbervar total := 0

@disp total - placed on Group Footer
WhilePrintingRecords;
global numbervar total

Any suggestions or better alternatives are appreciated.

 

[dgillz]

Dump the variable formulas and just insert simple summaries. You do this by right clicking on the field in question and select insert, summary.

Then use the Top N expert to sort.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[ajxmas]

I tried that but then my other sort options get ignored.

 

[lbass]

Please lay out the sort options you are using, along with their datatype and the parameter you are using for the sort options.

-LB

 

[ajxmas]

The user enters a parameter (?Sort Field) to pick which way he wants to sort.

?Sort Field = 1(Dollar) or 2(Zip) or 3(cust name) or 4(cust #)

@sort- formula:
if {?Sort Field} = '1' then
totext({@sum amt}) else
if {?Sort Field} = '2' then
{VENDOR_ACCOUNT.ZIP_BASE} & {VENDOR_ACCOUNT.ACCT_ID} else
if {?Sort Field} = '3' then
{VENDOR_ACCOUNT.ACCT_NAME} else
if {?Sort Field} = '4' then
{%acct_tochar}

Option 1 is for descending Total $ and is a numeric field. All the other options are string fields & will be ascending.

In my Group Header I sort on @sort with the following formula:
If {?Sort Field} = '1' then
crDescendingOrder
else
crAscendingOrder

When I use the Group Sort Expert it always sorts on the Total $ even if I pick one of the other options.

 

[lbass]

So the contents of {@sum amt} are? And do you have a current group or were you hoping to group on the sort formula?

-LB

 

[ajxmas]

I'm sorry, @sum amt is actually @total.

global numbervar total := total + {VENDOR_SALES.AMT_SOLD}

Yes, I have a current group sorted on @sort with the following formula:
If {?Sort Field} = '1' then
crDescendingOrder
else
crAscendingOrder

 

[lbass]

You cannot use variables in this way if you want to do a group sort.

I was not asking about your sort formula, but about what groups you have. Let's just say you have a group of some kind.

Set up two sort formulas:

//{@sortbyamt}:
if {?Sort} = '1' then {VENDOR_SALES.AMT_SOLD}

Insert a sum on this formula at the group level. Then go to report->group sort->choose "sum of {@sortbyamt}" descending as your group sort field. If {?Sort} <> '1' the group sort will have no effect.

//{@sort}:
if {?Sort Field} = '2' then
{VENDOR_ACCOUNT.ZIP_BASE} & {VENDOR_ACCOUNT.ACCT_ID} else
if {?Sort Field} = '3' then
{VENDOR_ACCOUNT.ACCT_NAME} else
if {?Sort Field} = '4' then
{%acct_tochar}

If you want the sort field to take precedence over any existing groups, then insert a group on {@sort} and make it your Group #1. If you just want it to sort within your existing group, add it to report->sort records.

I could be more specific if I knew what you were grouping on.

-LB

 

[ajxmas]

I will try this method.

For clarification my groups are:

Group 1: - Sorted by Service Type
Group 2: - Sorted based on User entered parm (@sort)
Group 3: - Sorted by sales description

So Group 2 is the only group that should change sort order based the parm entered.

 

[lbass]

Do you want Group 1 or Group 3 sorted by the sales total if {?sort} = '1'? Insert the summary at the appropriate group level, and then choose that summary in the group sort.

-LB