Bar chart order by Year & Month problem

[tjw2006]

Hi, I've got some data coming into a Crystal Report (v11.5) and basically the Year field is an integer and the Month field is formatted via the stored procedure as "Jan", "Feb", "Mar"...etc. I've created a bar chart and have set the OnChange values to the Year field in ascending order and then the Month field in a specified order. What seems to be happening is for some years the month ordering is going wrong - like starting with "Oct" or "Nov" instead of "Jan". I've tried everything to fix this and nothing is working. Incidentally, there is no actual full date field in the data.

Thanks.

 

[tjw2006]

Anyone!?

 

[lbass]

You need to be a little more patient re: how quickly people respond.

I just did a mock up of your report and I see the issue. The specified order only works if all subcategories are present in the first set of results. Since you are working with a stored procedure, I would just ask them to convert the string for month to a number (NOT a string). This will sort correctly regardless of the presence of all members of the month group.

-LB

 

[tjw2006]

Hi lbass, thanks for your response. I've actually tried changing the stored procedure so it brings back a month number as well and assigned the second OnChange element to this, but the same thing happens. Would it be the case that the procedure would need to indicate zero values for months that have no figures, rather than those months just not being included?

 

[lbass]

No. If they are really of number datatype the chart should work properly. Using numeric text ("10" for "Oct") will NOT work. I tested this too, although with XI.

-LB

 

[tjw2006]

I was actually bringing back a numeric value for the month e.g. 1 for "Jan", 2 for "Feb" and based the Month OnChange to that numeric value and it still didn't work. I just want the chart to show the data that's there - so in this case figures for November and December for 2008, all month for 2009 and January and February for 2010 - there must be a way to do this?

 

[lbass]

I tested this with numbers for months and it worked. So you have confirmed that the datatype of the month field is number, not numeric text? I can't recreate your issue--I modeled my report with missing months in the first and last years, and all sorted correctly. Did you remember to set the order for the new field to ascending?

-LB

 

[tjw2006]

Yes, that's working now with numeric months, it's just when you hover over each riser you get something like 2009: 2: 78 as the value for Feb 2009, which doesn't look particularly descriptive. Would be nice to get it showing something like "Feb 2009: 78" - but just can't find a way of doing this.

Thanks for your help.

 

[lbass]

You could consider using one 'on change of' field:

totext({table.year},"0000")+"/"+totext({table.month},"00")

This should return: 2009/02: 78 when hovering over it, and should still sort correctly.

-LB

 

[tjw2006]

Ok, I'll give that a go - thanks

 

[tjw2006]

Yes, that works quite well - thanks again for all your help.