0800 652 4050

  sales@dscallards.com

Universe 'month name' Object That Orders Chronologically

Disclaimer

This approach only works for Web Intelligence and when you don't commonly need to filter on month.

The Problem

If you use a month name field (or convert a month number to name), it is a string object, so orders alphabetically in a report. The report users can change this with a custom sort but this is cumbersome.

Using month number avoids this problem, but users often want to see the name, especially the short name (eg ‘Oct’).

So by default you can either have your cake, or you can eat it but not both.

The Solution

You can, however, get both by using a date field (so it orders chronologically) along with the IDT's display format function to display the name of the month.

'Aha' you are hopefully thinking, 'but if I use a date field simply formatted as month name, the month will repeat time in the report, one for each date'.

True, so the essential element to this solution is a 'first of month' field in your calendar table. This ensures there is only ever one value for e.g. October, so all October rows roll up into one:


Of course if your database is outside of your control you do this with a derived table.

You then display format the object as so:


You can display any element of the date with the month. Here I'm showing year as well.

Here are the results in Web Intelligence:


The only issue with this approach when the display form isn't used - for instance client tools other than Web Intelligence, or in Web Intelligence when looking up a list of values - for example in a filter. In these cases you are back using either month number or custom sorting. However, I sometimes combine a month number based object for filtering with a chronological month name (as show above) for displaying.

Posted By Angus Menter, BI Services Manager, on 16th January 2018