| Author |
Message |
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
  
Topic: group on monthname logically
Posted: 16 Feb 2009 at 8:06pm |
Hi all,
I have three groups in one report:
1. Institution
2. year
3. month.
both groups 2 and 3 are based on Ordered date
with group three, I want the Month name to be sorted logically not alphebaticlly.
I first created a formula called 'group by month' where:
Monthname(Month({ORD.CDATE}),true).
So the report looks like the following:
Institution A
2008
Dec XXXXXX
Sep XXXXXX
Obviously, the sorting is alphebatical, you cannot use either "in ascending order" or "in descending orders" in Change Group Options which don't make sense. The month should be sorted as Jan, Feb, Mar, April...Dec.
In the above sort, the April is the first one appeared under 2008.
by the way, I'm using CR XI
Any advices would be much appreciated, thanks in advance.
|
 IP Logged |
|
|
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 17 Feb 2009 at 1:26am |
Hi
Go to Report Menu then Select Group Expert then Select Group 3 and Click Options.
Then in Change Group Options box from the DropDown for
in Ascending order
in Descending
Select
in Specified Order then you can specify how you want the order to be
Type Jan in Named Group Click New and select the New value equal to the field value
i.e
Jan = Jan
Feb = Feb
and so on ....
Cheers
Rahul Edited by rahulwalawalkar - 17 Feb 2009 at 1:26am
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 17 Feb 2009 at 3:34am |
thanks for your help, Rahul. That works!
John
|
| IP Logged |
|
despec99
Newbie
Joined: 10 Feb 2009
Online Status: Offline
Posts: 22
|
Posted: 17 Feb 2009 at 6:34am |
Originally posted by johnwsunHi all,
I have three groups in one report:
1. Institution
2. year
3. month.
both groups 2 and 3 are based on Ordered date
with group three, I want the Month name to be sorted logically not alphebaticlly.
I first created a formula called 'group by month' where:
Monthname(Month({ORD.CDATE}),true).
So the report looks like the following:
Institution A
2008
Dec XXXXXX
Sep XXXXXX
Obviously, the sorting is alphebatical, you cannot use either "in ascending order" or "in descending orders" in Change Group Options which don't make sense. The month should be sorted as Jan, Feb, Mar, April...Dec.
In the above sort, the April is the first one appeared under 2008.
by the way, I'm using CR XI
Any advices would be much appreciated, thanks in advance.
Or you can group by the "monthnumber" and change what appears on the Group Header name, i.e., instead of a number, test the number and assign a Month name. David
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 15 Feb 2010 at 2:23pm |
Hi,
firstly I created a formula similar as yours:
if month({DTM.DATE}) = 1 then
totext("Jan")
else if month({DTM.DATE}) = 2 then
totext("Feb")
else if month({DTM.DATE}) = 3 then
totext("Mar")
,,,,
then in the changing group, select the above formula as group,
click specified order tab, click edit where you then type
Jan = Jan
Feb = Feb
Mar = Mar
Apr = Apr
etc
so the report will sort the month name logically, not alphebatically.
John
|
| IP Logged |
|
nix1016
Groupie
Joined: 07 Jun 2010
Online Status: Offline
Posts: 40
|
Posted: 05 Aug 2010 at 2:09pm |
|
I have the same problem, however this does not work for me as my Year changes, so basically if I have Dec 2009 and Jan 2010 I need Dec 2009 to come before Jan 2010. How do I go about doing this?
|
| IP Logged |
|
Mrs Robinson
Newbie
Joined: 15 Feb 2010
Location: United Kingdom
Online Status: Offline
Posts: 5
|
Posted: 11 Aug 2010 at 4:02am |
|
You could add an extra grouping (above the Monthly group) and in Group Options, group by year - therefore 2009 would list before 2010
|
| IP Logged |
|
Emir_W
Senior Member
Joined: 25 Apr 2010
Online Status: Offline
Posts: 228
|
Posted: 14 Aug 2010 at 9:27pm |
additional information:
create 2 formulas:
1. xMonth:
if month({tbl.datefield})>10 then
"0"+totext(month({tbl.datefield}),0)
else
totext(month({tbl.datefield}),0)
2. xDate
totext(year({tbl.datefield},0,"")+" - "+{@xMonth}
xDate will give you something like:
- 2009 - 03 (for March 2009)
- 2009 - 11 (for November 2009)
- 2010 - 01 (for January 2010)
- etc.
hope it help.
|
|
Emir W
|
| IP Logged |
|
Smittles
Newbie
Joined: 15 Sep 2010
Online Status: Offline
Posts: 5
|
Posted: 05 Oct 2010 at 7:11am |
|
I have a similar problem, but in a Cross-tab. Can I also use the methods described above in that situation?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Oct 2010 at 7:45am |
it depends on what your data looks like but yes you should be able to do this.
That being said IMO, the more logical process for this is to just group on the date field. When grouping on a date field in crystal you have the option to group on day, week, month, year, etc. When you do this it keeps the logica sort order asc/desc on the actual date. You can change the group header (or CT row name) by righ clicking and selecting Format field and then picking it to show just the month name.
|
| IP Logged |
|
|
|
