Skip to Content

Crystal Reports: change sorting order based on a user selection

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 04 at 04:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

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

  • May 07 at 09:16 PM

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

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

    • 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