12 Replies Latest reply: Nov 19, 2007 1:58 PM by Colby J RSS

    Conditional Background Color for Report Cell

    403501
      Hi,

      I am trying to put a background color to the Cell of Report. I would like to have it conditionally.

      I am trying to use Template. But when I give condition like : #SAL" > 1000 it gives error. Also when I use

      <td bgcolor=green #SAL# > it actually displays #SAL# in all the report values instead of the actually value with bgcolor=green.

      I have also tried giving select ename, <td bgcolor=green>||sal,comm from emp, but this shifts the table coloum by one.

      Not sure what is going wrong.

      Thanks
      Sandeep
        • 1. Re: Conditional Background Color for Report Cell
          Marc Sewtz-Oracle
          Sandeep,
          Do you want to conditionally change the background for the entire column or just a cell? HTML DB reports allow you to conditionally alter the look of rows, not columns. What you might want to try though is using an HTML expression (see report column attributes) for the column where you want to alter the background color. You could define a complete table within that cell, including table, tr and td tags. This way you won't get the extra column. Then for the background color attribute, you use another column in your result set that has the color name as the value. You would hide that actual column in your report, but reference its value using the #COL_NAME# syntax in your HTML expression.
          Hope this helps,
          Marc
          • 2. Re: Conditional Background Color for Report Cell
            Tyler-Oracle
            Option number 2 is to use a case statement in your query like this:

            select serial,
            '<div style="width:100%;height:100%;background:'||
            case when purchase_price > 5000 then 'red' else 'green' end
            || '">'||purchase_price||'</div>' purchase_price,
            brand
            from hardware

            For the actual implementation, I would use a class instead of a style definition, but you can change that if this solution works for you.

            Thanks,
            Tyler
            • 3. Re: Conditional Background Color for Report Cell
              249989
              Hi,

              What is the way to conditionally format the background color of the row based on query values.

              Regards,
              Manpreet
              • 4. Re: Conditional Background Color for Report Cell
                Marc Sewtz-Oracle
                Manpreet,
                Conditionally formatting rows based on query values is what's actually build into the engine. All you need to do is to edit your report template, define two alternative column templates and define a PL/SQL condition for them. To do that pick "Used based on PL/SQL expression" from the select list named Column Template 1 Condition. And then put your condition into the Column Template 1 Expression field. A condition could be "#SAL# > 2000". Click on the label to get more online help on how to do this.
                Regards,
                Marc

                • 5. Re: Conditional Background Color for Report Cell
                  403501
                  Hey Tyler,

                  Thanks a lot it works great !!

                  -Sandeep
                  • 6. Re: Conditional Background Color for Report Cell
                    427751
                    Hi,
                    I have tried to change the cell background color using the exmple you gave :
                    select serial,
                    '<div style="width:100%;height:100%;background:'||
                    case when purchase_price > 5000 then 'red' else 'green' end
                    || '">'||purchase_price||'</div>' purchase_price,
                    brand
                    from hardware
                    the problem is that if we need sort capability to this field, it wont work, because the sort will not be ablt to sort it correctly.
                    can you advise on the way to solve it ?
                    Thanx
                    • 7. Re: Conditional Background Color for Report Cell
                      427751
                      Hi,
                      just to give some more details :
                      we have a report with some columns that are colored accordnig to the cell value ( from a select statement ).
                      we cant use the templates option ( because we need mote the 4 templates ... ). when we sort by one of those columns - it makes alphanumeric sort and not numeric sort - how can we make the sort right.
                      thank you very much.
                      • 8. Re: Conditional Background Color for Report Cell
                        427751
                        Any Help Please ???? it very important to us. Thanx
                        • 9. Re: Conditional Background Color for Report Cell
                          Marc Sewtz-Oracle
                          Ronen,
                          If you add HTML tags to your column value, then the value is no longer numeric and thus you'll get alphanumeric sorting. So in order to maintain numeric sorting, you should only select the column value without any tags. To get you background color working, you should be using the report template and the conditional sub templates available there. If that's not sufficient in your case, then I'd recommend working with HTML expressions. First you need to add another column to your report that you use to derive the color, so that "case" expression that you use for your purchase price should be in a separate column and the return value should be either red or green, without the div tag. Then go to the report attributes page and un-check the show-flag for that new column, this way it's not shown in your report. And finally, go to the report column attributes for your purchase price column and in the html expression field, type in something like this:

                          <div style="width:100%;height:100%;background:#COLOR#">#PURCHASE_PRICE#</div>

                          The PURCHASE_PRICE substitution string refers to your price column, the COLOR substitution string to your new color column. A HTML expression is applied to your column after processing your SQL statement, so the numeric sorting is maintained using this method.

                          Hope this helps,
                          Marc

                          • 10. Re: Conditional Background Color for Report Cell
                            427751
                            Thank you very much Marc.
                            this has done the job !!!
                            as always till now, the htmldb and the support team have the solution.
                            • 11. Re: Conditional Background Color for Report Cell
                              ssryu
                              can anybody post missing part in the query?
                              select serial, ''||purchase_price||'' purchase_price, brand from hardware

                              to post code, use the following tags in your message:
                              -pre-code here -/pre-

                              thanks,
                              • 12. Re: Conditional Background Color for Report Cell
                                Colby J
                                I ran into a problem when doing a condition similar to this(only I did not use numbers, I used a varchar =) --- A condition could be "#SAL# = 2000". -- What fixed my error of 'Error ERR-1025 Error processing PLSQL expression' was putting '#SAL#' = '2000' - Hope this saves someone some time in the future...