22/06/2010 Leave a comment
I saw a post on the Oracle BI EE Forums today; Sorting month name. There were various solutions to the question. I think Kishore Guggilla came up with the best solution, so he deserves all the credits.
Although, it’s a very easy solution to a very simple ‘problem’, I thought I write a little post about the subject, because the solution can be used very often. Not only in a month sort.
Picture the following. I have created a dimension table with month names and month numbers.
Based on this dimension table I have created the following Business Model.
Now we can create a simple report with the month names.
As you can see the month names ar sorted alphabetically. We can verify that by the query fired.
If we want to sort the month names based on theire month number, we have various options. As said before I like the solution via the repository. In the repository you can sort a column, using another column. Just change the properties of the logical column. Now the BI Server knows that it hast to sort the month name via the month number.
Now we can create the same simple report with the month names.
As you can see now the month names ar sorted by month number. We can verify that by the query fired.
Easy but powerful in some cases.