This morning I have been working with Cognos BI over a TM1 cube. I was frustrated that I couldn’t work out how to sort a dimension the way I wanted it. For example, in TM1 I have a PnL dimension and is viewed normally with Net Profit after Tax at the bottom, Tax Expense just up from it, Net Profit before Tax above that and then all the way up to List Price Sales at the top of the PnL. In TM1 this is easy, I just Expand Above and viola!
In Cognos BI by default the sort order is the default order of the dimension in TM1. So I was presented with Net Profit after Tax at the top, then it made its way down to List Price Sales at the bottom of the page. Not optimal!
I discussed this with a friend, the Chimney Sweep, and he suggested adding a “Sort Order” numeric attribute in TM1, and then in BI, selecting the Crosstab Node Member (in my case I just clicked on my rows of the report) and the properties for Crosstab Node Member were active from this. Then scroll to the bottom of the Properties box and modify the Set Sorting to use my new Sort Order attribute. Bingo!
