Solved

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

Posted on 2014-10-08
165 Views
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
0
Question by:SStroz
• 4
• 4
• 3
• +1

LVL 13

Expert Comment

ID: 40370201
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

LVL 34

Expert Comment

ID: 40370300
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

LVL 7

Author Comment

ID: 40371274
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.

Thanks!

Steve
0

LVL 100

Expert Comment

ID: 40371400
It can be done using the "trick" James suggested.

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

LVL 34

Expert Comment

ID: 40371597
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

LVL 100

Expert Comment

ID: 40371663
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

LVL 7

Author Comment

ID: 40371957
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

LVL 100

Expert Comment

ID: 40372083
Not a problem.  It just takes 2 formulas.

mlmcc
0

LVL 100

Assisted Solution

mlmcc earned 250 total points
ID: 40372149
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

LVL 34

Accepted Solution

James0628 earned 250 total points
ID: 40372255
... 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

LVL 7

Author Closing Comment

ID: 40384197
James & Michael - thank you so much.  You guys are awesome!
0

LVL 34

Expert Comment

ID: 40384747
You're welcome.  Glad I could help.

James
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial shâ€¦