sortingbusiness-intelligencebusiness-objectswebi

Sorting behavior incorrect with FormatDate

I am editing a Webi report using SAP BusinessObjects BI4.

I have a report with a table in which I display a date formatted like this :

=FormatDate([Sales Date];"dd-MMM-yyyy")

Originally, Sales Date dimension has the format "mm/dd/yyyy".

I want to sort my table by this formatted date dimension in ascending order, so I just select my table > right click > select Sort and then I select =FormatDate([Sales Date];"dd-MMM-yyyy") in ascending order.

My problem is that, the sorting behavior is incorrect as the result is as follows:

Sales Date
----------
01-AUG-2006
----------
01-JUL-2010
----------
02-FEB-2006
----------
03-AUG-2005

As you can see above, it seems that the sorting is only done by the "day" value and it completely ignores the "month" and "year".

My object is correctly defined as "Date" in the universe.

and the sorting is correctly done when I don't use the formatting "dd-MMM-yyyy" and keep the original format of Sales Date.

Any suggestions please? Thanks!

Solution

  • The return data type of the FormatDate is a string. This is why the sorting will be out of order as it will sort the string value instead of the date value.

    You have two options:

    1. Format the date using a (custom) date format (In the Formatting section when you have the document in Edit mode). You'll need to use the RIA (Java applet) in order to define your own custom date format. Alternatively you can use one of the formats available. This will not change the data type but only the presentation, thus the sorting should be fine.

    Formatting

    1. If you still want to use the FormatDate option, you could add the [Sales Date] in a second column, use it to sort your data and then hide the column.