Sorting $MTD String Ascending/Descending

[dreman]

Using CR 8.5:
Created a parameter @sort_by to sort report by:
Name, Type, State, Region...
But How would I go about adding in the parameter a new sort by $MTD (Ascending), $MTD (Descending). Any hints, please advise.
Thank you.
dré

 

[synapsevampire]

Use a YYYYMMDD format and make sure that you have the string padded with zeroes.

Since the parm must have a desc/asce in the date selection to allow selecting the order, you might build into the formula to multiply it by -1 for the descending option.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dreman]

K:
$MTD are $sales/per month (ie:$12,324.89) and not a date. They are based on date figure but the result must be taken account for it.
thank you.
dré

 

[kenhamady]

Is this a subtotal of a group or a formula field in a single record? 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]

Ken:
It is a subtotal of a group based on a formula:
if {table.invoice_date} in MTD then
mtd = mtd + {table.sales}
Else
0
Simple formula.
However I would like the choice to sort the group by name, state... or $MTD Ascending or Descending.
Right now A quick fix is by having 2 reports that one sort sorts by chars (Name, state, Region, Marketing code) the other report is an exact copy to allow user to sort by: $MTD, $YTD, $Profit Ascending or descending. I still do not know how to combine the two.
Thanks you.
dré

 

[kenhamady]

How are you getting the second report to sort? TopN based on the sum of a formula? 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]

Ken:
Yes the second report (Sort by MTD, YTD, Profit) is based on Top/N which is based on formula upon user selection (Parameter Sort_by:
1-MTD $ (Asc)
2-MTD $ (Desc)
3-YTD $ (Asc)
...

Formula @Sort_Order:
Select {Sort_by}
Case '1':
{@MTD Sales}
Case '2':
(-1)*{@MTD Sales}
Case '3':
{@YTD Sales}
...

Top/N selection by @sort_Order formula and voila.
Kind of tricky/complex but it works like a charm.
But How do I get to sort by Name, State or Region (String) within...
Thank you.
dré

 

[kenhamady]

I assume that your first parameter is really a group, not a sort. Is that correct? When you use the TopN feature is the group always by Name, or could it be any of the 4 optional group fields. 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]

Ken:
Report # 1 is grouped#1 by salesman, goup#2: sort_by, group#3: customer ID. sorting is by @sort_order
(sort_order
select {sort_by}
case 'ID':
{table.customer_id}
Case 'Name':
{table.Name}
...

Report # 2 is grouped by group#1 salesman and by group#2 customer_id in order to get sum(sales). Top/N selected all by @sort_Order.
Thank Ken
dré

 

[kenhamady]

I pulled an old technique out of my bag for this one. I call this "group cloaking" and I think I will write it up for my next newsletter.

In this technique you can make an entire group level disappear by changing the group field to match the group above it, and then suppressing the GH and GF. Your report needs to have 4 groups, but you will only see 2 or 3 at one time, depending on the user's choice. Here is how it would work:

1) Take the original report and expand the parameter choices to be:
Customer Name -Alpha
Type - Alpha
State - Alpha
Region - Alpha
Customer MTD-Asc
Customer YTD-Asc
Customer MTD-Desc
Customer YTD-Desc

2) Add the formula that for your TopN subtotal to this report, but use the expanded parameter above to control what it returns. You can decide what field to subtotal on the Alpha options.

3) Group 1 (Salesman) doesn't change.

4) Add more cases to your Group2 formula so that when the user selects any of the TopN options (5-8), the formula returns Salesman. Also suppress the GH and GF when the report is a TopN. When the user selects a TopN option Groups 1 and 2 are the same, so Group2 has no effect on the report.

5) Add a new Group3 formula that returns Salesman on the TopN options (5-8) but returns Customer ID for the Alpha options. You can also suppress GH and GF for this group when the report is a TopN. For a TopN, the first 3 groups are the same.

6) Group 4 will always be Customer ID. The subtotals for this level will have the TopN applied to them. Of course, when Group3 is Customer ID in Alpha order, Group4's TopN will have no effect on the report. It will only have an effect if there is more than customer in each Group3. If the report is an Alpha report you can suppress GH and GF for this group. 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