Chart Grouping by Months 1

[crogers111]

CR XI
SQL

I'm trying to create a simple Bar Chart where each bar is a Month & Year. e.g. Dec-06, Jan-07, etc.

The report uses parameters forbeginning and end date based on the same date field that the bar chart uses. Using the parameters means the report span more than 1 year.

My chart is in the report Header

I'm having difficulty creating the formula that would be used for the 'on change of' so that each bar is a month & year AND in chronological order.

I tried:
year({table.CheckDate})& ToText(Month({table.CheckDate}),"00")

which puts it order but the label doesn't display as needed (Dec-06) on the chart. (This formual does work for grouping as I can Format the formula's Display String to show Dec-06)

How can I get this data in chrononlogical order regardless of the date range and have a display of: Jan-07, Feb-07, Mar-07, etc ? I also considered setting up a specified order but since the report uses parameters and can span more than 1 year, I was unsure if this was feasible.

Thanks.

 

[Madawc]

Try

ToText({table.CheckDate}, "yy-MM")

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[crogers111]

Thanks for the reply...

This does put the bars in chronological order but the labels are not quite what I need. I used a date range of 1-1-2006 thru 12-31-06. I get 12 bars in the chart, one for each month. But using the above formula the labels are:


06-01, 06-02, 06-03, etc.

This is the correct order but I need the bar chart labels to be:

Jan-06, Feb-06, Mar-06

 

[Madawc]

You want the chart bars to be labelled by month name but ordered by month number? Not something I know about. Can anyone else help?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[fallsr]

I have used solution located here and it works.

http://technicalsupport.businessobj...9&sliceId=&dialogID=360384&stateId=1 0 356275

Thanks,
Bob

 

[crogers111]

This will work !

I used a slight variation in the formula that is provided:

ToText(Year({Table.CheckDate}),0,"") & "-" & ToText(Month({Table.CheckDate}),"00") &
" " & Left(MonthName(Month({Table.CheckDate})),3);

This shows in the X-axis label as: 2007-01 Jan.

Ideally I'd prefer to not see the month number: -01, but not a big issue.


and although the KB notes state that this won't work if your date range spans more than 1 year, but it works for me and CR XI.

Thanks for the help !!