3 Replies Latest reply: Mar 18, 2010 4:53 PM by 555071 RSS

    Report formatting/sorting issue

    555071
      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
          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
            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
              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