Crystal Reports: change sorting order based on a user selection

[former_member548403]

I am creating a report that needs to differentiate the order of month based on a user's selection. If a user selects "fiscal year", then the report needs to list the months starting from July. I tried to import this setting from the data source with SQL, but the report is not taking it.

I think I need to create a formula that starts something like:

If {?YearType} = "Fiscal" Then....

A screen shot of current report:

[DellSC]

Yes, you do. And here's what it might look like if you are working with date fields:

if {?YearType} = 'Fiscal' then
  Switch(  
    Month({mytable.date}) = 7, '01-July',
    Month({mytable.date}) = 8, '02-August',
    Month({mytable.date}) = 9, '03-September',
    Month({mytable.date}) = 10, '04-October',
    Month({mytable.date}) = 11, '05-November',
    Month({mytable.date}) = 12, '06-December',
    Month({mytable.date}) = 1, '07-January',
    Month({mytable.date}) = 2, '08-Febuary',
    Month({mytable.date}) = 3, '09-March',
    Month({mytable.date}) = 4, '10-April',
    Month({mytable.date}) = 5, '11-May',
    Month({mytable.date}) = 6, '12-June',
    true; 'Unknown'
  )
else
  Switch(  
    Month({mytable.date}) = 1, '01-January',
    Month({mytable.date}) = 2, '02-Febuary',
    Month({mytable.date}) = 3, '03-March',
    Month({mytable.date}) = 4, '04-April',
    Month({mytable.date}) = 5, '05-May',
    Month({mytable.date}) = 6, '06-June',
    Month({mytable.date}) = 7, '07-July',
    Month({mytable.date}) = 8, '08-August',
    Month({mytable.date}) = 9, '09-September',
    Month({mytable.date}) = 10, '10-October',
    Month({mytable.date}) = 11, '11-November',
    Month({mytable.date}) = 12, '12-December',
    true; 'Unknown'
  )

Note that "Switch" is just a simpler way of writing out if...then...else multiple times. the first part of each line is a comparison, the second is the result if the comparison is true. Also, to get numbers to sort correctly as text, they need to have leading zeroes for 01 through 09.

You'll also need to work out which fiscal year you're dealing with as well.

-Dell

[former_member548403]

Thanks for the tip, Dell! I just tried it, but I'm getting ") is missing" message, though. I'm not sure where it's missing ")".

[DellSC]

There should be a comma instead of a semi-colon after "true" at the end of both switch statements. Sorry about that!

-Dell

[former_member548403]

Ahhhh! This is driving me nuts! The formula seems fine and should work on the report, but this stubborn report refuses to take it and I don't understand why?! The list of months is in one of the group header section. Could this be the reason? The report insists on displaying January first in either selection.

[DellSC]

Group by the formula instead of the date/month field.

-Dell

[former_member548403]

Do you mean, change Groups > Common > Use a Formula as Group Sort Order? If so, I tried to use the formula there, and I get an error message, saying "A constant expression is required here".

[DellSC]

No. If you're grouping on a date field, use this formula for the group instead of the date field. So you'll right-click to the left of the group header and select "Change Group". If you're not grouping on a date field, you need to add the formula to your groups.

-Dell