Sort values by month & year in a chart.

[willg]

Hi Everyone,

I'm currently trying to produce a chart which sorts the x-axis by the month&year of a column. I've tried using the data format to only display the information I want, however then the chart is not grouped, and just displays multiple values of each (eg. Jan 2011, Jan 2011, Jan 2011, Feb 2011, March 2011, March 2011 etc etc).

The only way I've managed to actually get the data to group is by using the extract() function. Specifically;

Code Select Expand


case extract(month,[date])
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May''
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
else 'error'
end


Though the problem with the above code is that cognos then just groups the data to each month, regardless of the year. For example, grouping data from January 2011 & January 2012 in the same column. Has anybody had any experience with this, or knows how I could sort the x-axis (and display it) by month & year instead of just by month.

Thanks in advance,
Will.

[pricter]

Create a data item with the combination of year month
for example 201201
and sort the x-axis based on this data item

[willg]

Thanks for the reply, how exactly would I make a data item with the combination of year/month such as 'January 2011'?

[pricter]

One option will be:
extract(year, [Date]) *100 + extract(month, [Date])

[willg]

Brilliant, that works well thanks a lot. Any idea on how I could edit the format (201203, 201204 etc) to look like an actuall date format? (March 2012, April 2012 etc)?

[pricter]

Maybe I was so clear.
You create two data items
One for showing what you want for example January 2013 (each database has some functions to achieve this format)
and one in order to sort the x - labels for example 201301

[willg]

Sorry to keep asking questions, but then how do I sort the x-axis by one data item, but then make it display a different one?

[Lynn]

Sorry to keep asking questions, but then how do I sort the x-axis by one data item, but then make it display a different one?

Questions are a big part of what makes this forum useful  ;D

Go to the page layout and click on the data item that you dragged into the x-axis of your chart. That one should be the query item you want to use for display. Once you've selected that in the layout, look for the sorting property in the properties pane and click the ellipses. Specify the data item that pricter described to give you YYYYMM format. That data item should already be in the query in order for you to select it.

Hope this helps!

[willg]

Still doesn't work as intended  :-\ but thanks all for the help anyway

[willg]

Hi once again, I thought I'd revive this post as I have come back to work on my project and still cannot figure this out. Currently I have two ways of displaying the date, one is as follows;


Though the issue with this is that every date field is displayed separately, where I want them to group the values by month.

The other method I have to display the date is via 'extract(year, [date])*100 + extract(month, [date])' which returns the following;


As you can see this does group the values by month, although the issue is that my date field has now been converted into an integer field. And I would much prefer a layout of '07-2012/July-2012' rather than '201207'.

If anyone can please help me with this it would be greatly appreciated..

[BigChris]

Have you tried something along the lines of datename({mm},[date]) + '/' + extract(year,[date])? You might need to play around a bit with it, but that should give you something close to what you're looking for...

C

[CognosPaul]

Try the following:

cast (_first_of_month([date]),date)

This will always return the first day of the month as a date without time.  In the chart, set the source type of the x-axis to Data Item Value and use the Data Format to format it the way you want.

[Michael75]

I just had a similar requirement - to display months in a chart as Apr-13, May-13, Jun-13 etc. My input field is [BALANCE MONTH], in the same YYYYMM format as yours, and I came up with this:

Code Select Expand

concat (concat (to_char (to_date (BALANCE_MONTH, 'YYYYMM'), 'Mon'), '-'), substr(BALANCE_MONTH, 3, 2))

You could adapt this to your particular requirements.

[willg]

Thanks Chris, that looks promising, the only problem is I get an error when I try to use both 'datename' and 'extract' in the same expression, I can do them in different data items and it works fine however;


And the errors recieved when trying to place in the same expression;




And Paul thanks for the reply, that expression successfully returns the first day without time (eg. 01-05-2011) but if I then go into data format and change the display, no matter what I change the date format does not get altered. Why do you think this is?

I feel like this is something that should be easily accomplished but it has given me far too much hassle.

[BigChris]

Ok - it looks like the extract element is bringing back an integer for the year...have you tried casting it as char(4)? There's a Year function in Cognos, but I think it brings back the same data as the extract function.

[willg]

Success! Thanks a lot Chris, using the cast worked. Also Michael thanks for the reply, though I got an error and never got round to trying it properly.  :)

[CognosPaul]

The data format won't affect a member caption. In the Chart Node Member set the Source Type to Data Item Value. Do that and you'll be able to set the data format to however you want. Since it will still be in date format, you can still use that to do your sorting, and any future formatting changes will be significantly easier.

[willg]

I'd had that problem in the past, and as such I had already set the source type to 'data item value'. So I'm not sure why it doesn't want to work that way

[CognosPaul]

That's really weird.

I just tried it on one of the samples, and it works perfectly. See the attached report.

[willg]

Do you know of any possible reasons why that could be?

-The database/table/query I'm using?
-The install version I'm using?
-etc

Also, when I tried to open your report into cognos using 'Open Report From Clipboard' I got the following error;


Though I am using Cognos V10.1.1 so I'm surprised I got that error.

[CognosPaul]

Based on the error you posted before, I could tell that it's an SQL server. SQL Server has some oddities when it comes to date datatypes. Try casting back to datetime or timestamp.

I'm pretty sure I've done this in 10.1, so I don't think it's an issue with the version.

I created my example on 10.2.1. Downgrading the report is a simple matter of switching the 9 to an 8. Downgrading to earlier versions will be much harder.