# Can you "sort" the detail area based on a "group" value?

Gurus,

Not sure of this as it's never been asked of me before.

Is there a way to change the detail sort order based on a group value?

For instance, sort "Company" ascending if the group name is "Customer" and sort by "City" descending if the group name is "Prospect"?

Any ideas?

Thanks

Steve
LVL 7
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Oooh, tricksy.

Adding a formula which says :-
``````if({groupfield} = "Customer")then
{Company}
else
{City}
``````

and then sorting by that is fairly simple, but changing the sort order not so much.

The only thing that comes directly to mind is to 'invert' the city name. Loop through the characters one by one, and replace them with the opposite end of the alphabet so A=Z, B=Y, C=X.... M=N,N=M,....etc

I think that would give the required output.

Iain
0
Commented:
What do you mean by "group name"?  Normally that would be the field name, so Customer and Prospect would be 2 different fields, used for 2 different groups.  But you also used "group value" at one point, and it doesn't seem like you'd really have a problem if you were actually talking about 2 different groups.

I am assuming for the moment that you have 1 group on field X, and "Customer" and "Prospect" are 2 different values that can be in that field.

Which version of CR do you have?  More recent versions have the option to change the group sort order (ascending or descending) using a formula.

If your version of CR has that sort option, you could create a formula like the one that PCIIain posted, create a group on that formula, and use a formula to make the group sort ascending or descending.

Another option is to simply create two formulas similar to the following, and sort on both of them:

// Sort by Company
if {group field} = "Customer" then
{Company field}
else
""

// Sort by City
if {group field} = "Prospect" then
{City field}
else
""

Each formula only produces sorting values when the group field has the corresponding value.  Otherwise, it just produces "" for every record, so it doesn't affect the sort order.

Sort by both formulas, and make the first sort ascending, and the second sort descending.

James
0
Author Commented:
Gurus,

Sorry I should have been more clear.  The detail "area" contains several fields and a subreport.  For instance:

Company    Contact    Address Subreport   Phone    Field 1    Field 2

The detail is grouped by another "contact" field called "Type" (which holds values, customer, prospect, etc).

So based on the Group, if the group is "customer" they want the detail sorted one way and if the group is "prospect" they want it sorted another way.

Can Crystal do that?  I don't see a way to do it.

Let me know your thoughts

Thanks!

Steve
0
Commented:
It can be done using the "trick" James suggested.

Group on your field (Type)

Create formulas that return the field you want to sort on when TYPE is a particular value

James provided examples

Add each of the formulas to the RECORD SORT under the REPORT menu
Set the Ascending/Descending appropriately.

You indicated TYPE has values Customer and Prospect.  If it has other values you can include them in the formulas

For instance you might also have Cold Contact as a value and want to sort by City

``````// Sort by City
if {TYPE field} = "Prospect"  or {TYPE field} = "Cold Contact" then
{City field}
else
""
``````

If you want to sort descending when type is Prospect or Cold Contact but use different fields you could use

``````// Sort Descending
if {TYPE field} = "Prospect"   then
{City field}
else if  {TYPE field} = "Cold Contact" then
{ContactName}
Else
""
``````

The only requirement on the formulas is the fields they return all have to be the same data type.

mlmcc
0
Commented:
Yeah, it seems like the idea that I suggested, and mlmcc expanded on, should work.

If there's some reason that you think it won't work, or something that you don't understand, let us know.

James
0
Commented:
I have used that method so a report could be sorted based on a parameter value and the fields could be different types.  So the same basic idea should work in a group

mlmcc
0
Author Commented:
Gurus,

Rats!  This looks like such a good workaround but I do have a problem - one of the sort fields is "character" and the other sort field is "date".

Ugh

Steve
0
Commented:
Not a problem.  It just takes 2 formulas.

mlmcc
0
Commented:
Look at the attached files

I built a report based loosely on your data.
Companies are type - Prospect or Customer

Prospect is sorted by the Company rating field (number) (descending)
Customers are sorted by the State field (character) (ascending)

mlmcc

Note - You may have to right click the file and choose the SAVE AS option to download it.
DualSort.rpt
DualSort.xls
0
Commented:
... one of the sort fields is "character" and the other sort field is "date".

Another way that you might be able to handle that is to convert the date to a string.  For example, something like:

if {group field} = "Customer" then
// Sort by company
{Company field}
else
if {group field} = "Region" then
// Sort by date
CStr ({date field}, "yyyy/MM/dd")
else
""

CStr converts the date field into a string.  I formatted the string as year/month/day for sorting purposes.  The slashes aren't required for sorting.  They're just there for readability, in case you decide to look at the values at some point.  If the date field is actually a datetime and the time is significant, you could add it to the string.  You can look up CStr in the CR Help to find the format options.

James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
James & Michael - thank you so much.  You guys are awesome!
0
Commented:
You're welcome.  Glad I could help.

James
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.