sort problem by group

[dreman]

I am using CR 8.5:
report is grouped by salesman and within salesman it is grouped by cust id to get $MTD, $YTD.
Salesman A
Customer 1A .... $MTD, $YTD
Customer 2A .... $MTD, $YTD
Customer 3A .... $MTD, $YTD
Customer 4A .... $MTD, $YTD
Salesman B
Customer 1B .... $MTD, $YTD
Customer 2B .... $MTD, $YTD
Customer 3B .... $MTD, $YTD
Customer 4B .... $MTD, $YTD
Need a question (parameter) to sort by:
1- $ MTD
2- $ MTD Descending
3- $ YTD
d- $ YTD Descending

How do I do it, please advice thank you.

 

[Naith]

Dre,

This has been tackled here:

thread767-93784 luck,

Naith

 

[dreman]

Naith:
Yes that will resolve the problem however another question is that my report is grouped by salesman + customer, customer total is printed to get cust MTD & YTD $amount.(detail is suppressed in order to get Sum of $amount in MTD or YTD)
The question is how do I sort by $ MTD or $YTD using parameter. I tried writing this formula
if {?sort_by}=1 then // sort by $MTD
Sum(MTD, customer_id)
elseif {sort_by}=2 then // sort by $YTD
Sum(YTD, customer_id)
I keep on getting an error for recuring...
Any thought, thank you.
dré

 

[Naith]

I'm not sure I understand what you mean when you say you get an error for recurring.

Having looked at your formula, it doesn't look like sorting will be allowed on this field because you're trying to run a sum function. Summed fields can't be used to sort by, unless you're using the TopN function.

 

[kenhamady]

TopN can't be conditional, but the formula in the subtotal CAN be conditional. So, create a hidden formula that either carries MTD values or YTD values, based on your parameter choice. Then subtotal this formula and use this subtotal in the TopN. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[MrBillSC]

Hi Dreman,

Since you want to sort by numeric fields, your ascending/decending problem might easily be solved by creating a formula field called SortValue as follows:

If {?SortOption} = 1 then
{$mtd} else
If {?SortOption} = 2 then
({$mtd} * -1) else
If {?SortOption} = 3 then
{$ytd} else
If {?SortOption} = 4 then
({$ytd} * -1)

Set the formula field SortValue to ascending in the sort options. By multiplying the numeric value by -1 the values should reverse to decending order.

bill

 

[dreman]

Ken:
You said to carry MTD and YTD into another formula. Are you suggesting to make formula2 to be SUM{MTD, customer_id}
since MTD=SUM{MTD,customer_id} and then hide formula2

Thank you.

 

[kenhamady]

No the formula I had in mind would be something like:

If {?prompt} = "MTD" and {Date} in MonthToDate
then {Your.Amount} else
If {?prompt} = "YTD" and {Date} in YearToDate
then {Your.Amount} else 0

It could have more options if you need them.

Then you would subtotal this formula using Insert-Summary, and use the resulting subtotal in your TopN. If you do the subtotal within a formula you can't use it for TopN.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dreman]

Finally, Finally, Finally, Finally, Finally, Finally,
Ok:
I was able to resolve it after few weeks.
This is What Ive done.
the group is by customer ID, I have several sum fields.
I created a sort_by formula:
select {Sort}
case '1':
{sales}
case '2':
-1*{sales}
case '3':
{Profit}
case '4':
-1*{Profit}
Then I place the formula in the detail section, Insert total & summary for this formulait based on this formula.
then in crystal , click report, sort records, and insert the formula {sort}. It works.
thanks for all your input.
dré