Sorting the Months in a Chart

Sorting Months in Webi

Sorting dates, and particularly months, in Webi can often seem quite difficult. Us humans believe that months are sorted January, February, March and so on. Machines will tell you that months are sorted April, August, December…
If you’re here reading this, you may have one of these problems:

1. My drop-down list of months doesn’t sort properly.
2. My report block doesn’t sort properly.
3. My chart doesn’t sort properly.

The following steps and screenshots were carried out in XI3.1 SP3, Webi via InfoView, with the editing mode set to advanced (Preferences > Web Intelligence).
If you have access to modify your universe or even data source, skip right to the bottom…


Sorting Months in a Filter Drop-downIf you want your users to be able to select a month in a simple filter, you will find that the months sort like this:

To sort the months correctly, follow these steps.

1. Drag the Month Name dimension onto the page (hold down alt before before dragging if you have other blocks on the page and don’t want to create a section). If you don’t have a month name dimension, use this formula to create a variable (I’ll use the phrases ‘variable’ and ‘dimension’ interchangeably):
   =Month([Order Date])
2. With the block selected, click on Custom Sort… in the sorting drop-down on the toolbar.
   
3. Sort the months like so, or however they are ordered where you come from:
   
4. Click OK.
5. Save your report, close it, open it again. You will now have a nicely-sorting drop-down.
   
6. You can delete the block if you don’t need to use it in your report layout, Webi will remember the custom sort.

But what if you want to show the month and year in the drop-down? Well, you have one pretty bad option, one bad option, one average option, and the one I use.

• The worst one looks like this:
  
  It’s just a list of months, in date format. Not great.
• The second option is to set up a custom sort for a year/month dimension. It will look fine to your users, but means that you need to be re-defining the custom sort order as the months roll by.
  
  If you like this option, you may want to increase the number of allowed items in a custom sort: CMC > Servers > WebIntelligenceProcessingServer > Properties > Maximum Custom Sort Size (entries).
• Option three is to format your dimension in such a way that the machine sorts the records in the same way us humans want to see them, like so:
  
  It does the job, but it doesn’t look pretty.
• Last, my preferred approach is to simply have one drop-down for year and another for month.
  

Next…


Sorting Months in a Report BlockObviously you already know how to sort a list of months using a custom sort, but what if you want to show the year and month in the one column?
First you’ll need two dimensions, one for the humans and one for the machine.

• The Month Year dimension. Use this formula…
  =Month([Order Date]) + " " + FormatNumber(Year([Order Date]); "0000")…for a dimensions that looks like this:
  
• The Year Month Code dimension. Use this formula…
  =FormatNumber(Year([Order Date]); "0000") + " " + FormatNumber(MonthNumberOfYear([Order Date]); "00")…for a dimension that looks like this:
  

The idea is quite simple, you’d like your report to sort by the Year Month Code dimension, but display the Month Year dimension to the user.

1. Start by creating your report block, using the Year Month Code dimension. If it’s the first column, you won’t even need to define any sorting.
2. With the Year Month Code column selected, click Alerters in the toolbar.
   
   
   If you’re not familiar with alerters, the short version is: an alerter will check to see if some condition is true, and apply a format if it is. But it can apply more than just formats…
3. Click New.
4. Name the alerter something useful.
5. Create a condition that is always true, like Year Month Code = Year Month Code. To do this, click the ellipsis next to boxes on either side of the operator, and click Select and object or variable.
   
   The result should look like this:
   
6. Click Format…
7. Set the font to black, or whatever is appropriate.
8. Click the formula icon in the top left.
   
9. The formula should simply point to your Month Year dimension.
   
10. Click OK.
11. Click OK.
12. Click OK.
13. And you’re done!
    

And now for a little tweak so that you can still use this even when drilling down through a hierarchy. I have four dimensions in my date hierarchy: Year, Quarter, Month and Date. Year and date look fine just the way they are, but for Quarter and Month, I’d also like to display the year. The logic is mostly the same as the above, so I’ll go quickly…

1. Create a report block with a year column.
   
2. With the year column selected, click Alerters in the toolbar.
3. Add a new alerter, give it a name.
4. Set the first sub-alerter to When Cell Contents is equal to Quarter Name where ‘Quarter Name’ is the quarter dimension in your hierarchy (don’t just type the words ‘Quarter Name’).
5. Click format…
6. Set the display formula to something like this (or create a variable that does the same):
   
7. Add another sub-alerter and set it to When Cell Contents is equal to Month Number where ‘Month Number’ is the month dimension in your hierarchy.
8. Set the display formula to show the friendly name for your month and year.
9. The screen should look something like this:
   

So now, when you drill down from year, the first column will change from showing the year dimension to show the quarter dimension. This will trigger the first sub-alerter, and it will display the quarter name and year, rather than just the quarter.

Drill again. The column will change to display the month dimension, which will trigger the second sub-alerter, which will show the month name and year, rather than just the month.

Drill again and the date dimension will show. This doesn’t need an alerter because, like the year dimension, it looks and sorts correctly as it is.

Sorting the Months in a Chart

It’s more of the same right here. The steps are the same as they are for a block, with one twist. In the steps above, you selected the month column in order to apply the alerter that would display the nicely formatted date. For a chart you must first select View Structure.

Select the Year Month Code dimension.

Then apply the alerter. Switch back to View Results and you’ll see that the chart now displays the month name and year. I’ve changed the definition of my Month Year variable to show shortened month and year values.



One Level DownAll of the above are things that you can do in Webi. This means it makes no difference if you data source is MS SQL, Oracle, BEx Query or anything else. If you do have access to modify your data source/universe, then it helps to add in a dimension that will truncate a date to the first day of the month.
In T-SQL: Sales.SalesOrderHeader.OrderDate - DAY(Sales.SalesOrderHeader.OrderDate) + 1
In PL SQL: TRUNC(Sales.SalesOrderHeader.OrderDate, MONTH)
In Universe designer, format this as MMMM YYYY or however you like. Then, with the exception of a drop-down filter, this will look and sort correctly.

- See more at: http://blog.davidg.com.au/2011/03/sorting-months-in-webi.html#sthash.yWHF2yu7.dpuf