Formatting Date in LineChart X-axis?

[RobSil]

For some reason I can't do this on my line chart.

I have a Date that I've drug from my Query into the "Category (x-axis)" box.  When i run my report the date is in format "2009-12-04 00:00:00" e.g.  My goal is "December 04".

I select the date item and go to the properties on the left.  The title is "Chart Node Member".  Any combination of setting Data Format to be Date, Date/Time etc. and either setting "Display Hours" etc. or using a pattern does not take.  The date displayed is always the same. 

Thoughts?

Rob
I should also mention this is 8.2.

[MFGF]

Hi,

I think the problem is that you are using complete date-time values on your x-axis, which the report is trying to group on (as this is what charts and crosstabs do).  Even if you managed to reformat the dates to display in the desired format (month name and day), you would still end up with a node for each date-time value, which I'm guessing you don't want (ie you could have many nodes of 'December 04' if there were multiple time values for that day).

One solution in this situation is to extract the month and day from the date in a calculation and use the calculation on your x-axis.

1. Extract the month numbers in a calculation - Mth:
extract(month, [your date-time item])

2. Convert this to a descriptive month name in another calculation - Monthname:
if ([Mth]=1) then ('January') else if ([Mth]=2) then ('February') else if... ...else ('December')

3. Extract the day and convert it to a character string in a third calculation - Day:
cast(extract(day, [your date-time item]),CHAR(2))

4. Concatenate the Month name and day with a space between - Monthday:
[Monthname] + ' ' + [Day]

Use the MonthDay item on your X-axis of your chart.

Regards,

MF.

[RobSil]

I'm dodging a bullet I think in that I have maybe 5-6 entries for a given day, but they all have the same date/time value (e.g. Dec 13th, midnight). 

If I understand things correctly... when I put the date/time along the x-axis it applies a sort rule to my query to group by unique dates... correct? 

Assuming the above is true then I am happy with the dates given so would still try to format the display... are you saying that's not possible and I can only achieve MMMMMM dd by manually extracting the date in the query and producing the appropriate fields that way?  Not trying to be difficult but this seems like such a routine function that I am a bit surprised that Cognos doesn't offer a simple format-the-date-and-your-done solution.

Thx!
Rob

[CognosPaul]

Are you using a cube for this? Can you tell me the datatypes for the members?

[RobSil]

Imagine a student DB.... fields in the data set would be:

SchoolName | HomeroomName | HomeroomTeacher | StudentName | TestDate | Test1Score | Test2Score | Test3Score

I would have my line chart be average test score over a month for each of the 3 tests (e.g.).  I would query with filter HomeroomName = "John Smith" (e.g.).  Then I just want to trace the test results over the month, independant of student so I would assume the aggregation on the line chart would be an average by TestDate.  In this case, all dates are 00:00:00.000 time (e.g. no time variance).

[MFGF]

I'm dodging a bullet I think in that I have maybe 5-6 entries for a given day, but they all have the same date/time value (e.g. Dec 13th, midnight). 

If I understand things correctly... when I put the date/time along the x-axis it applies a sort rule to my query to group by unique dates... correct? 

Assuming the above is true then I am happy with the dates given so would still try to format the display... are you saying that's not possible and I can only achieve MMMMMM dd by manually extracting the date in the query and producing the appropriate fields that way?  Not trying to be difficult but this seems like such a routine function that I am a bit surprised that Cognos doesn't offer a simple format-the-date-and-your-done solution.

Thx!
Rob

Hi Rob,

No problem - if you can guarantee that each date has the time part set to midnight, you should be OK without a calculation solution.

try the following:

1. Unlock the report structure (click the padlock button on the toolbar).
2. Select the date item within the Categories (X-axis) box - make sure it says "Chart text item" at the top of the properties window.
3. Change the Source Type property from "Member Caption" to "Data Item Value"
4. Set the Data Item Value property to be your date item
5. Define the Data Format property as Date - with Ordering set to "Year, Month, Day", Display Years set to "No", Display Months set to "Long Name" and Display Days set to "Yes"
6. Re-lock the report structure.

MF.

[RobSil]

Thanks MF!  As always you are the man!


R.