sorting issue in yearmon column

[kvchowdary]

Hi,

I have a Yearmon column and the data is like 2016-1,2016-2,2016-10 and 2016-12.i applied the ascending sorting order and i am getting the data like below

2016-1
2016-10
2016-12
2016-2

Cognos 10.2.1 and oracle db
Can anyone help me?


Thanks
vck

[MFGF]

Hi,

I have a Yearmon column and the data is like 2016-1,2016-2,2016-10 and 2016-12.i applied the ascending sorting order and i am getting the data like below

2016-1
2016-10
2016-12
2016-2

Cognos 10.2.1 and oracle db
Can anyone help me?


Thanks
vck

I'm guessing the column has a character datatype? If so, the sort order your report is delivering is correct. To sort the way you wish, you're going to have to split the string into two values - the year and the month, make these numeric, and sort on these.

Cheers!

MF.

[Lynn]

Or better yet, go back to the people responsible for the data warehouse and have them format this as YYYY-MM with leading zeros so that it is fixed at the source and won't require special handling on each and every report.

[kvchowdary]

Thanks for MF and Lynn,

it is the so much of process to change in Source..i mean in the dataware house level..I tried the MFGF method but still not getting the correct format


substring(yearmon,6,7)..still i am getting like
1
10
11
12
2
Thanks,
vkc

[MFGF]

I tried the MFGF method but still not getting the correct format


substring(yearmon,6,7)..still i am getting like
1
10
11
12
2
Thanks,
vkc

You missed a very important step - making the values numeric. Your substring() function is returning the values as character strings...

MF.

[Lynn]

it is the so much of process to change in Source..i mean in the dataware house level..I tried the MFGF method but still not getting the correct format

Yes, that is a frustration, but the sole purpose of a data warehouse is to support reporting and analytics. It is the foundation that the entire BI initiative relies upon. The approach you are taking is going to benefit the one report you are working on right now. You, and every other developer, will need to replicate this logic on every report created where this field is used. The cumulative time and effort can easily exceed the overall cost to the organization of simply doing it properly in the source.

Failing that, at least try to have the field transformed in the Cognos Framework. It will be a performance hit compared to simply populating the field properly, but at least it will alleviate report author angst and lead to consistency.