Conditional grouping and month groupings 2

[butkus]

I'm writing in CR 8.5 Dev ed. against a DB2 v.7 database.
I need some guidance on monthly groupings please.

I have reports that allow the user to select the groupings and the # of grouping levels deep. Here is my grouping formula:

If count({?Choose Groups}) >=1 then
select {?Choose Groups}[1]
case "Defendant Name" : {DA_DAPDEF.DU_NAM}
case "Disposition Code" : if isnull({DA_DAPCHG.DC_DISPO}) then "Pending" else {DA_DAPCHG.DC_DISPO}
case "Docket Type" : if {DA_DAPCRX.DO_TYPE}="RD" then "Rocket Docket"
else if {DA_DAPCRX.DO_TYPE}="RDC" then "Rocket Docket Companion"
else if {DA_DAPCRX.DO_TYPE}="MD" then "Missile Docket"
case "Event Type" : if {DA_DAPEVT.DV_EVT_TYPE}="OPEN" then "Opened"
else if {DA_DAPEVT.DV_EVT_TYPE}="INDT" then "Indicted"
else if {DA_DAPEVT.DV_EVT_TYPE}="INFO" then "Information"
case "Date" : monthname(month({DA_DAPEVT.DV_EVT_DATE})) + " " +totext(year({DA_DAPEVT.DV_EVT_DATE}),0,"")

As you can see, I have given the user the option to group by "Date" as noted in the last case statement.

My problem, is this;

It works fine but returns the monthly groups in alpha order rather than the prefered chronological order. Is there anything to code that would do what I want?

this not this
January August
February April
March December
April Febraury
May January
June July
July June
August March
September May
October November
November October
December September

Obviously, it's a bit different than just creating a new group and having Crystal recognize that your working with a date field and providing the options of grouping by day / week / month / year / etc...

Thanks in advance for any help provided.

James

 

[lbass]

You might try changing the date option to:

totext({DA_DAPEVT.DV_EVT_DATE},"yyyy MM")

Use your {@selectcase} formula for grouping.
Then you could create a separate formula for displaying as the groupname:

if {?Choose Groups}[1] = "Date" then totext({DA_DAPEVT.DV_EVT_DATE},"MMMM") else
{@selectcase}

-LB

 

[synapsevampire]

Group by a padded month number.

if month({table.date}) < 10 then
"0"+totext(month({table.date}))
else
totext(month({table.date}))

If you're crossing years, add the year as well:

if month({table.date}) < 10 then
totext(year,0,"")+"0"+totext(month({table.date}))
else
totext(year,0,"")+totext(month({table.date}))

You can still use the monthname as a display formula, just don't group on it.

-k

 

[butkus]

Both solutions provide the answer, thanks to all.

James