Solved

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

Posted on 2014-10-08
12
162 Views
Last Modified: 2014-10-16
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
Comment
Question by:SStroz
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 13

Expert Comment

by:PCIIain
ID: 40370201
Oooh, tricksy.

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

Open in new window


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

by:James0628
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

by:SStroz
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.

Let me know your thoughts

Thanks!

Steve
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40371400
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
   ""

Open in new window



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
   ""

Open in new window


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

by:James0628
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

by:mlmcc
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 7

Author Comment

by:SStroz
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

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

mlmcc
0
 
LVL 100

Assisted Solution

by:mlmcc
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

by:
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

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

Expert Comment

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

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now