Conditionally change sort order in formula 3

[WestView]

I have a formula (@Date_Sort) that I use as a group in my report. It’s used to conditionally sort the group by a date field.

If {tabel.stringfield} = "Cond1" Then {tabel.datefield1} Else
If {tabel. stringfield} = "Cond2" Then {tabel.datefield1} Else
If {tabel. stringfield} = "Cond3" Then {tabel.datefield2} Else
If {tabel. stringfield} = "Cond4" Then {tabel.datefield2} Else
If {tabel. stringfield} = "Cond5" Then {tabel.datefield2}

The group is sorted in Descending order.

However, I’ve been tasked to have the report sort by date in Ascending order when one of the conditions (Cond3) in the formula is met.

Is this possible?

Thanks for any/all suggestions and/or help!!!

- Tom

 

[rosemaryl]

Dear Westview,

Create a second group based upon a formula as you did above just changing the name to Date_Sort_Cond3_Desc.

Add the two formulas to the Report/Sort Records

First formula grouped on ... choose Ascending.

Second formula grouped on choose Descending.

Now, suppress group 1 in the section expert:

{Table.Stringfield] = 'Cond3'

Now, on the second group, in the section expert suppress it for the following condition:

{Table.Stringfield} <> 'Cond3'


So in essence you will have a Date_Sort that = Asc and the second formula you are grouping on would Be Date_Sort_Desc.

Regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

Try:

If {tabel.stringfield} in ["Cond1","Cond2"] Then
val(totext({tabel.datefield1},"yyyyMMdd"))
Else
If {tabel. stringfield} = "Cond3" Then
val(totext({tabel.datefield2},"yyyyMMdd")) *-1
Else
If {tabel. stringfield} in ["Cond4","Cond5"] Then val(totext({tabel.datefield2},"yyyyMMdd"))

Use this formula for grouping/sortation only, use the datefield for display purposes.

-k

 

[lbass]

Try the following:

Create two formulas:

//{@descsort}:
If {table.stringfield} in ["Cond1","Cond2"] Then {tabel.datefield1} Else
If {table. stringfield} in ["Cond4","Cond5"] Then {table.datefield2} Else ""

//{@ascsort}:
If {table. stringfield} = "Cond3" Then {table.datefield2} Else ""

Add {@descsort} with "Descending" checked, and {@ascsort} with "Ascending" checked.

-LB

 

[rosemaryl]

Dear SV and LB,

Don't you like the fact that all three of us came up with a different solution?

regards to you both,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

Yes, although some were quicker than others!

-LB

 

[synapsevampire]

Yep, 3 entirely different spins there, all seemingly effective and about the same performance.

-skol-

-k

 

[rosemaryl]

Dear SV,

I agree ... although I would change to a case statement ... I find them so much easier to read.

Dear LB,

I would really like to see the formula as a case statement, they are so much easier to read.

Select (Table.Stringfield)
Case "Cond1", "Cond2" : {tabel.datefield1} Case "Cond3", "Cond4","Cond5" : {tabel.datefield2}


I like that better.

Regards,

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

I agree, Ro.

Since they had it as an IF, I just modified it
I use Cases in SQL a lot, but the yntax is different and I always have to go look it up in CR, so I just skip it fairly often...

-k

 

[WestView]

Hello All,

Thank you all for such prompt and excellent replies!!! I’ve tried all three and, as synapsevampire predicted, there are no real performance issues with any of them.

Like Rosemary, I am slightly more comfortable with SQL than Crystal syntax and therefore would like to use her solution. However, I’m not clear on how I would get "Cond3" to sort in Ascending order this way (while leaving all the other conditions to sort in Descending order).

Again, thank you all for your replies!!!!

- Tom

 

[rosemaryl]

Dear Tom,


Actually, Tom, I would like to think I am comfortable in both Crystal and SQL <smile>. The Select Case statement in Crystal while functioning similarly to Case When in SQL is really a different animal and has different syntax requirements.

My solution requires two formulas (both using the crystal's select Case statement ... which isn't like SQL's at all ..) upon which you would create two groups.

You would then add each of those formulas to the Report/Sort Records Expert. On the first one choose Ascending and on the second one Choose Descending.

Since you conditionally suppress group 1 when = Cond3 and conditionally suppress group2 <> Cond3 you get what you wanted.

Regards and hope that better explains things.

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[WestView]

Got it.

Thanks Ro!!!!

- Tom

 

[jraheja]

You guys are really good and prompt!