Help with month formula needed

[stormtrooper]

Hi there.

I have a report that groups by {@Month} descending which looks like this:
totext({MY_DATE}, "yyyy/MM/dd&quot

My question is, when I preview the report, how do I get the left side bar to show "January", "February", "March", etc. instead of "2003/03/08", "2003/02/08", "2003/01/08", etc.?

 

[dgillz]

Monthname({DateField}) should work. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

If you mean the drill down area, it will duplicate the grouping.

The only thing that comes to mind is to prefix the text equivalent of the Month number (2 digits as in 01, 02) to the front of a Monthname function as dgillz demonstrated.

Then your grouping will still work, and you'll have the name in there.

Include the year in the prefix if you span years.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[stormtrooper]

vampire, I am a little confused about the "prefix the text equivalent of the Month number (2 digits as in 01, 02) to the front of a Monthname function"

Could you please elaborate? I'm trying, but just can't quite grasp it.

Thanks

 

[stormtrooper]

I should have mentioned I'm using CR7.

 

[butkus]

Try this

totext({MY_DATE}, "MMMM&quot+ " " +totext(year({MY_DATE}), "####&quot


then insert a group on MY_DATE and select "In ascending order" and "This section will be printed for each month"

Then under the "Group Options" section check the "Customize Group Name Field": check box
then
click the "Use a formula as Group Name" radio button
when the formula editor opens, type{@monthname}

This will display the month name and year (usefull if your report spans years)

 

[stormtrooper]

Still trying to figure this one out!?!?

butkus, if I choose ascending, it will show December 2002 before January 2003. Same situation if I choose descending. November 2003 will come before January 2003.

What I need is for both to be consistent. That is for the date to be in descending order (year included), but display the month in the drill down area as well. My date field is coming in as dd/MM/yyyy (eg. 10/10/2002).

So, my problem is when I do this:
sort by {my_date} asc or desc, it works, but I don't get the month names displaying.

When I do this:
sort by totext({my_date}, "MMMM&quot asc or desc, it doesn't take in consideration the year. And when I sort on the year, it doesn't take in consideration the month!

Is there any way around it?

 

[chelseatech]

Customise Group Name appeared with CR8, and was one of the really useful new features in that version. In CR7 you can remove the group name and have it display a formula to get your grouping correct, but your group tree will alwyas show the original value of the group.

In Cr7, can you group by {MyDate} by Month. Note the extra question that appears if your field is a Date field rather than the {@month} string you are using. If your default date format is Mar-1999 then the dates will look okay.

If you are using Seagate Info 7, then this will work well in the report designer, but will revert to 03/1999 when scheudled and I've never found where in Info to change the default date settings.

I hope this helps. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[stormtrooper]

I got it and now I'm kicking myself for not knowing!

All I had to do was set the date format under report options to show the month name instead of the number. Then group on that by month.

Thanks for all the help though!