I am using a relational model with the Date is in MM:DD:YY HH:MM:SS. I have a requirement to sort the date by a MM-YYYY and then Count all the orders in that Month.
So it should look like this
Date Total Order Count
06-2014 100
07-2014 110
I have used to_char to display the date in the correct format, but can't get it to sort properly.
You can either change the format to YYYY-MM or else create a second data item in that format and use it as a sort key.
I changed to YYYY-MM and the sorting works descending.. is there a way to make it Ascending?
I also tried a second data item which also worked, but it broke my total order count by Month. The Total order count displays all the orders by each DD:MM:YY HH:MM:SS instead of total by the MM-YYYY (now in correct order).. any hints? Thank you
Quote from: bud659 on 21 Jul 2014 09:49:05 AM
I changed to YYYY-MM and the sorting works descending.. is there a way to make it Ascending?
Yes, change the sort direction.
http://pic.dhe.ibm.com/infocenter/cfpm/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc%2Fug_cr_rptstd_id15543cr_rptstd_wrkdat_sort_data_rel.html
http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id8824cr_rptstd_wrkdat_perform_advanced_so.html
http://pic.dhe.ibm.com/infocenter/cfpm/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc%2Fug_cr_rptstd_id18742cr_rptstd_wrkdat_sort_data_dim.html
Quote from: bud659 on 21 Jul 2014 10:05:41 AM
I also tried a second data item which also worked, but it broke my total order count by Month. The Total order count displays all the orders by each DD:MM:YY HH:MM:SS instead of total by the MM-YYYY (now in correct order).. any hints? Thank you
My car is broken. Can you fix it?
I don't really think I could expect someone to answer that question unless I was able to provide specific details.
Is your source relational or dimensional? If your report is showing ordr count down to the second then it would be 86,400 lines long for just one day's worth of activity. Is this really what you are doing? What method are you using to get the totals?
hi lynn. thanks for the responses and sorry for the lack of details. I am going to use the sorting with descending order. this is working and thanks for your help