This discussion is archived
3 Replies Latest reply: Mar 18, 2010 2:53 PM by 555071 RSS

Report formatting/sorting issue

555071 Newbie
Currently Being Moderated
Hi,

Here is something I spent a whole day on, and still can't figure out.

I have a simple SQL report, which is supposed to show estimated and actual expenses and the variance between the two. The trick is that whenever the variance is positive, the numbers should be green and whenever it is negative - the numbers should be red and NO MINUS SIGN should be shown. For example, if the variance is "-15", then a red "15" should be shown.

The report query is as follows:
-----
SELECT
region,
estimate,
actual,

(estimate - actual) AS variance,

(CASE
WHEN (estimate - actual)>=0 THEN 'green'
WHEN (estimate - actual)<0 THEN 'red'
ELSE NULL
END) AS variance_color
FROM
expenses
-----

And then the following HTML expression is used for the Column Formatting on "variance" field (sorry for double "<<" and extra spaces - I could not figure out how to format text for this posting):
-----
<< span style = " color : #VARIANCE_COLOR# ; font-weight : bold ; ">#VARIANCE#<< / span>
-----

All works quite well and I get the colors right, but I still have the minus sign on the negative numbers. If I use "ABS(estimate - actual) AS variance" in my query, I certainly can get rid of it, but then sorting does not work properly, since negative numbers are treated as positive.

Any ideas would be appreciated.

Constantine
  • 1. Re: Report formatting/sorting issue
    Arie Geller Guru
    Currently Being Moderated
    Hello Constantine,

    >> If I use "ABS(estimate - actual) AS variance" in my query, I certainly can get rid of it, but then sorting does not work properly, since negative numbers are treated as positive

    Please try the following:
    SELECT
    region,
    estimate,
    actual,
    '<input type="hidden" value="'||(estimate-actual)||'">'||
    abs(estimate - actual) AS variance,
    (CASE
    WHEN (estimate - actual)>=0 THEN 'green'
    WHEN (estimate - actual)<0 THEN 'red'
    ELSE NULL
    END) AS variance_color
    FROM expenses
    The trick is to add to the column a hidden prefix with the real value of the column, and then concatenate it with the visible value. As the sort order is determine by the beginning of the column – the hidden prefix in our case – you can enjoy both worlds – maintain a correct sorting order while displaying only positive colored numbers.

    >> sorry for double "<<" and extra spaces - I could not figure out how to format text for this posting)

    You should use the forum tags *&#91;code]* and *&#91;/code]* .

    Regards,
    Arie.

    -------------------------------------------------------
    &diams; Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.

    &diams; Forthcoming book about APEX: Oracle Application Express 3.2 – The Essentials and More
  • 2. Re: Report formatting/sorting issue
    fac586 Guru
    Currently Being Moderated
    Include both variance and abs(variance) in the query:
    SELECT
            region,
            estimate,
            actual,
            (estimate - actual) AS variance,
            ABS(estimate - actual) AS abs_variance,
            (CASE 
              WHEN (estimate - actual)>=0 THEN 'green'
              WHEN (estimate - actual)<0 THEN 'red'
              ELSE NULL
            END) AS variance_color
    from
            expenses
    And the HTML Expression for the "variance" column is:
    <span style="color: #VARIANCE_COLOR#; font-weight: bold;">#ABS_VARIANCE#</span>
    Hide the #VARIANCE_COLOR# and #ABS_VARIANCE# columns.

    #ABS_VARIANCE# is the value shown in the column, but the sort is performed in the underlying SQL using the original variance value.
  • 3. Re: Report formatting/sorting issue
    555071 Newbie
    Currently Being Moderated
    This is perfect! I was looking for a way to display one number, while having it sort by something else, but never could figure out how to do it...

    Thanks a lot,

    Constantine

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points