Skip to Content
0

Crystal Reports: change sorting order based on a user selection

May 04 at 02:10 PM

44

avatar image

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:

months.png (3.1 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Dell Stinnett-Christy May 04 at 04:34 PM
1

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

-Dell

1

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.

0
Dell Stinnett-Christy May 07 at 09:16 PM
0

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

-Dell

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

1