Months in Alphabetical Order in Simple Report Filter

building reports webintelligence
1

Hello,

I’m having the issue of my months being displayed Alphabetically instead of Chronologically. I’ve solved this issue within reports by using an alerter with the MonthNumberOfYear() function. However, I can’t seem to apply the alerter to my Simple Report Filter, so I’m not sure how to get the months in Chronological order. Any Input is greatly appreciated. Thanks!

jmfuller (BOB member since 2010-02-16)

2

The alerter simply displays the underlying value in a different way. You need an actual converted value to sort. I would either create a report variable with the month number and sort on that, or pull it in from the universe if available.

Chris

cfachris :us: (BOB member since 2003-09-03)

3

Thanks for the response Chris,

I thought about creating a variable within the report with MonthNumber, but it seems simple report filters do not pick up on variables created on the report level (I made a post about this awhile back). I have to put in requests to IT to modify the universe, does the MonthNumberOfYear function work on the universe level?

Thanks!

jmfuller (BOB member since 2010-02-16)

4

Try specifying a custom sort within the report. It will also allow you to hand type in values to be sorted on. I.E. If your initial query returns January, February, March you can hand type April, May, June, July…so when your query returns the remaining months they will get sorted automatically.

Another option is to create a variable similar to month number of year and then add that column to your block of data. Sort on the month number and then hide the column. In the event of a report you can shrink the column size, change the font color and remove the cell borders.

mcliffordgoo :us: (BOB member since 2003-02-13)

5

The custom sort works assuming you have a limited number of values (12 months, 7 days, etc.). If you have a year in the value then it becomes more of a maintenance issue.

If you have the ability to modify the universe, that option is reusable by all report writers. IF you have an Oracle db, you can use

to_char(date, 'MM')

to get the month number. You need two Ms to ensure 11 and 12 don’t come between 1 and 2 as it formats as 01, 02, etc…

Hope this helps!
Chris

cfachris :us: (BOB member since 2003-09-03)

6

Thanks for the input mcliffordgoo,

I will try the custom sort, that is a good idea.

As far as the second option, yes this would work in the report, but I am discussing the LOV in a simple report filter. If I was talking about the actual body of the report, how about instead of creating and hiding a column, create an alerter and have the display be =month([Date]) while the formula is =MonthNumberOfYear([Date]). I’ve used this approach many times.

Chris,

Yes, that did help. Thanks!

jmfuller (BOB member since 2010-02-16)

7

A quick thought,
How about using a concat of monthnuber and month name for you simple filter.

Simple Filter object=MonthNumberOfYear([Date])+"-"+Month([Date])

KhoushikTTT :us: (BOB member since 2005-02-24)

8

Good idea Bashaa. This gives me what I need except for one problem:

The order is:
10-October
11-November
12-December
1-January
2-February
…
…
.

Is there a way I can define MM within the variable editor get it to list in this order:
1-January
2-February
…
…
.

Thanks!

jmfuller (BOB member since 2010-02-16)

9

Custom sort helped me here.

  1. Remove the existing Simple filter.
  2. In the report block do a custom sort (Jan till December) on the object which would be used as simple filter.
  3. Now add the simple filter object and you should see the drop down sorted month ascending

KhoushikTTT :us: (BOB member since 2005-02-24)

10

This is best solved at the universe level, I believe the report level filter is “decided” by sorting the objects LOV, but I must admit I’m unsure, I can’t test it here.
The best way, if your database supports it, is the following method, on Daves blog:-
http://www.dagira.com/2010/03/04/sort_by_nono-very-confusing/

There are other, less graceful ways of sorting a, character, LOV, using another field as the order by, if you go the universe path - many posts, on here :).

Edit, well Neo has suggested a working Webi solution :).

Mak 1 :uk: (BOB member since 2005-01-06)

11

Thanks guys, the issue has been solved with your help.

jmfuller (BOB member since 2010-02-16)

12

Mr Fuller,

Egos spared :).
Can you elaborate what you actually did, it may help others.

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)

13

Surely.

I created a variable ‘Month - Variable’ defined as:

=MonthNumberOfYear([Date])+"-"+Month([Date])

I then created a table within the report populated by the ‘Month - Variable’ variable.

I then created a custom sort on the table 1-January …12-December.

I then drag and dropped the variable into the simple report filter toolbar, and presto magico! It sorted from 1-January to 12-December.

I then deleted the table from the report.

jmfuller (BOB member since 2010-02-16)

14

Hi

Just had a question… if you set up an object that returns the month in a number as shown below. How then at the Universe level do you apply it to your object that has the Months as JAN, FEB, Mar, etc… I am a little new to Universes
to_char(date, ‘MM’)

bobboard (BOB member since 2009-03-04)