Sort By Month 3

[donodm]

I wrote two formulas to organize the columns of a cross tab report buy quarter and month (Crystal 8.5).

Month:
if {CONTHIST.ONDATE} in Date (2003, 01, 01) to Date (2003, 01, 31) then 'Jan' else
if {CONTHIST.ONDATE} in Date (2003, 02, 01) to Date (2003, 02, 28) then 'Feb' else
if {CONTHIST.ONDATE} in Date (2003, 03, 01) to Date (2003, 03, 31) then 'March' etc.

Quarter:
if {@On Date} like 'jan'then 'Q1'else
if {@On Date} like 'feb'then 'Q1'else
if {@On Date} like 'march'then 'Q1' else
if {@On Date} like 'april'then 'Q2'else
if {@On Date} like 'may'then 'Q2'else
if {@On Date} like 'june'then 'Q2' etc.

When I run the report, the months are out of order. How do I get the report to run Jan, Feb, March, etc.

Thanks

 

[lbass]

They are organized alphabetically because they are strings, so go to format crosstab, click on the column field, select "group options" and choose "specified order" and then enter the fields in the order you want.

-LB

 

[donodm]

Works great. Thanks Ibass.

donodm

 

[kenhamady]

Or, you could have skipped the formulas altogether and used the Cross-tab's "group options" to let the Cross-tab group Quarterly and Monthly. That way you don't have to change the formulas every year.

Add the date field twice to the columns box, and then set one to be Quarterly and the other to be Monthly.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[donodm]

This is great too. Thanks.

donodm