How can I sort on a date field but display in a different format 1

[nickirea]

Hello

I am building a stacked column chart which displays values for selected months.

I would like to display the column title in the format 'MMM YY' e.g. Jan 07, Feb 07 etc

If I sort on this field, then clearly it would sort the labels into alphabetical order - I want to sort the columns in month order. I have another column called MONTH_OF_YEAR which I can sort on, but don't know how to prevent this from being displayed on the chart.

Many thanks for any suggestions

 

[almeids]

If your data item is a date, just change it's Data Format property in the chart to control what it looks like and sort it however you like, sorting uses the actual value, not the visual representation.
If it's not a date, add month_of_year to your query and sort on it, it doesn't need to be in the chart.

 

[nickirea]

Many thanks for your reply. Your response makes sense, but I just can't find how to action it!

I'm afraid I am very new to Cognos (although have many years of programming experience most recently Excel and Access VBA).

I have found another data item which is a date. I have set a sort on this data item, and changed the data format property. However, despite me formatting the date as 'long' or 'medium' etc it always renders in the format YYYY-MM-DD. If I click the 'View Tabular Data' button in Report Studio then it displays in DD MMM YYYY format.

Any ideas?

Platform is Report Studio 8.2

Many thanks
Nicki

 

[almeids]

Keeping in mind that I'm on ReportNet so what I say may not apply...
You didn't mention changing the data item that the chart is using to the one which is a true date; if you are still using a text data item in the chart then the date formatting will have no effect. However, if what was in the chart was already in the format you want (that's how it sounded in your original post), it should be sufficient to simply apply the sorting to the true date. Just make sure you turn off the sorting on the text.
Or, if you have in fact switched to the true date in the chart, the problem may be where you are applying the date format. In RN it should be applied to the Category in the Chart, for example; setting it in the Layout Data Format has no effect. Also, you'll need to explicitly set "Display Days" to No even in the shorter formats if you just want to show month and year.
good luck