Universe 'month name' Object That Orders Chronologically

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:

Universe 'month name' Object That Orders Chroologically

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:

Waterfall Charts in SAP Crystal Reports for Enterprise 4.2

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:

Waterfall Charts in SAP Crystal Reports for Enterprise 4.2

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 Practice Manager on January 16th, 2017