Sorting behavior incorrect with FormatDate
<p>I am editing a Webi report using SAP Business Objects BI4.</p>

<p>I have a report with a table in which I display a date formatted like this :</p>

<pre><code>=FormatDate([Sales Date];"dd-MMM-yyyy")
</code></pre>

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

<p>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 the =FormatDate([Sales Date];"dd-MMM-yyyy") in ascending order.</p>

<p>My problem is that, the sorting behavior is incorrect as the result is as follows:</p>

<pre><code>Sales Date
----------
01-AUG-2006
----------
01-JUL-2010
----------
02-FEB-2006
----------
03-AUG-2005
</code></pre>

<p>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".</p>

<p>My object is correctly defined as "Date" in the universe.</p>

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

<p>Any suggestions please?
Thanks! </p>