Date format in the chart axis 2

[russula]

Hello All,

could you please help to find solution for this problem. I'm doing graph, based on the date field (let's it be Start_Date). And graph is "For every month" . I have to show in the graph axis this date as "Jan 2007", "Feb 2007" etc.... But I can't chose what date format I want to see for graph... there is no options in the Properties. This format is taken from default settings and depends on computer. When report is run on our corporate Reporting Server, the graph is shown like "01/2007, 02/2007" etc.... How I can set up format for date in the graph?

Thanks a lot.

 

[russula]

Forgot to mention. We are using Crystal Report XI

 

[Tatertot45]

This is how i do this. Go in the Preview, and click on the graph. Then, click on a date on the axis you want to format, and keep clicking the date until that date is highlighted. Once the box is around only the date, right click and choose, "Format Axis label". Select the "Number" tab and change the "Catagory" to date. From there choose a date format. I don't think the format you want is a choice, but there are a couple that are really close, with a month abbreviation and year.

Hope this helps.

 

[russula]

Thank you, Tatertot45, for answer, but unfortunately that doesn't help. This Tabs "number" appears only if original data format is Number, but I have Date from the beginning. For this format there is no such tabs at all :-(. Sounds stupid, but how it is - special date format can be chosen only from number format.
Any other idea?

 

[Tatertot45]

OK... Try this. In the chart, click on the axis label like before, until just the date is highlighted.Right click again, and choose "Edit Axis lable", and free type in what you want. Unfortunately, you have to edit each individual lable. I guess the other way to try and do this would be to write a formula that changes the way the date is displayed. I know that for some crosstabs, i have written little formulas to seperate the month name and year, so i can use them in the cross tab a little diffeently. This is the formula i use.

MonthName(Month ({inv_item.tax-date}),true) & " " & totext(Year ({inv_item.tax-date}),0)

It gives me a month abbreviated and the year. I can then use this in the cross tab and charts if needed. Give that a whirl, and let me know if it works.

 

[russula]

Manually changes we have to exclude because report is for scheduling It will run automatically every month. To the formula - it works, but there is one problem - how to order this month? Because if you convert date format to the text it would be ordered alphabetically. And even more - in the January 2008 the data from 2007 should be before then data for 2008..... I was thinking about putting formula as rules for order but didn't manage to do this.

 

[dgillz]

This answer assumes Crystal XI. You should always put your version of crystal in your posts.

Right click the group footer and select change group. On the options tab, select the "use formula for group name" radio button and enter a formula like this:

Monthname(Month({YourDateField}))&" "&totext(year({YourDateField}),0,"")

The sorting is not affected by this.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[russula]

Hello dgillz! Thanks a lot - it works! Honestly I don't understand relation between group and chart in this case - when I created chart, I put as Data -> on change of just this field and in The section will be printed I have chosen for each month . What would be if I don't have any group at all? Or group, based on this field, is For each week (day) but chart should be for each month ? Could you please explain logic behind or give a link to explanation this relation between group format and chart? (In addition I can say that I'm not allowed to choose group when creating chart - only original data field).
Thanks in advance.

 

[dgillz]

Russula,

When you graph, below the "on change of" there should be a dialog box that says "Sum of {Database Field}".

Thats the grouping. If you hit the down arrow, you will see other grouped values (if you have them).

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[srmalloy]

I had the same issue with CRXI that russula had, and the suggestion dgillz had didn't affect the chart -- I was still getting "1/20/2007" and the like on my chart axis. However, the solution did derive from his answer. I have a report that summarizes data in several columns by month, and am graphing each column with month as the X axis. What I did was to create a formula field using a slight change in the formula that dgillz specified (since I wanted the month abbreviated):

Monthname(Month({YourDateField}),True)&" "&totext(year({YourDateField}),0,"")

and then use that formula field in the chart expert for the "On Change Of" field. Since the data was already ordered by the date field, the change in the formula field matched the change in the date field, so the summary values I'm graphing don't get mangled, and the chart displays the date as "Jan 2007" instead of "1/20/2007".

 

[Cooldaddee]

I am using CR which ships with .NET 2005. I had a similar problem with dates on the Group Axis within a line graph. I created a formula field called @DatePart and converted my date to this "formula = cstr({SP_STANDARD_DATA_COLLECTION.Date},"MM/dd")" ... Then I used the formula field within the chart expert as my data 'On Change Of'. Now my date appears im my axis labels in the format I want. Hope this helps.

 

[russula]

dgillz,

under 'Change of' I have fields, one of them is "date" type. In the option for these fields I can choose only 'Order....' or 'Top N'. In the 'Order...' I'm able to select "for each month" (actually selected) or "Use formula as Group Sort Order'. So, when I choose this 'for each month' I can set up any format for this field.
In the Sum{database field} I have absolutely other options (I show Count or Distinct Count) - has nothing to do with date.

srmalloy,

honestly I don't understand this: 'and then use that formula field in the chart expert for the "On Change Of" field. Since the data was already ordered by the date field,....' Data are not already ordered. I'm using the same formula (monthname + year) for name of group - it works, but I'm not able to choose group for graph, in the list of available fields I can see only original date field, not grouped. And when I select this one, I have to group it again (can select 'for each month', see answer to dgillz).

Guys, thanks for answers, but I know how to create formula for month name, the problem is where to put this formula in the graph to keep date order (not alphabetically). Grouping separated from graph doesn’t affect graph itself, at least I don’t know how to link groups and graph.

Thanks for your idea.