4 Replies Latest reply: Feb 2, 2013 9:41 AM by William Wallace RSS

    Changing Row color of standard SQL report

    William Wallace
      Hi,

      I am trying to follow this post to change the color of a row in APEX SQL report. Change Colour of Row - Oracle APEX SQL Report

      It's a bit outdated, and trying to figure out how to get to this page: http://img7.imageshack.us/img7/4782/columntemplate.jpg in APEX 4.2 where I can conditionally set background color.


      I am using theme 13 (legacy) and made a copy of the report region. Edited it but cannot find an equivalent section of that apex 3.1 screenshot.

      Any help appreciated.
        • 1. Re: Changing Row color of standard SQL report
          fac586
          William Wallace wrote:

          I am trying to follow this post to change the color of a row in APEX SQL report. Change Colour of Row - Oracle APEX SQL Report

          It's a bit outdated, and trying to figure out how to get to this page: http://img7.imageshack.us/img7/4782/columntemplate.jpg in APEX 4.2 where I can conditionally set background color.

          I am using theme 13 (legacy)
          Ugh.
          and made a copy of the report region. Edited it but cannot find an equivalent section of that apex 3.1 screenshot.
          This suggests you're looking at the region definition page, or just possibly the region template definition. What you need here is a copy of the report template.

          1. Go to Home > Application Builder > Application > Shared Components > Templates.

          2. Select theme 13 from the Theme filter, Reports from the Type filter, and click Go.

          3. Create a copy of the Standard report template using its copy icon in the list.

          4. Select the new report template and edit it as discussed in the linked thread above, paying particular attention to the contents of the second post. (This is the generic column template definition page that contains the section in the screenshot.)
          • 2. Re: Changing Row color of standard SQL report
            William Wallace
            I sure was :) Cheers for pointing it out.


            Just another question, there are 4 options for the #COLUMN_VALUE# which I have background color conditionally (just like in that screenshot) based on one of the column values. The column can be of 1..5 values, however I only have 4 conditions to work with in the template editor.


            So what APEX is doing is since it can't find a 5th matching condition, it picks the very first one and applies it on it (even though the condition doesn't match).

            Is there anyway I can incorporate a 5th option/condition for column templates? Cheers.
            • 3. Re: Changing Row color of standard SQL report
              fac586
              William Wallace wrote:
              I sure was :) Cheers for pointing it out.


              Just another question, there are 4 options for the #COLUMN_VALUE# which I have background color conditionally (just like in that screenshot) based on one of the column values. The column can be of 1..5 values, however I only have 4 conditions to work with in the template editor.


              So what APEX is doing is since it can't find a 5th matching condition, it picks the very first one and applies it on it (even though the condition doesn't match).

              Is there anyway I can incorporate a 5th option/condition for column templates? Cheers.
              Not using that technique. There are a couple of options: another template-based approach using a custom named column report template, or using jQuery. I generally prefer the template method as everything runs on the server.

              1. Start by including a column containing the condition logic to generate a class value in the report query:
              select
                        ...
                      , case
                          when sal < 1000 then 'low'
                          when sal between 1000 and 2000 then 'medium'
                          when sal > 2000 then 'high'
                        end sal_class
                        ...
              from
                        emp
              Then create a custom named column report template:

              2. Go to: Shared Components > Templates > Create

              3. In the wizard select: Report > From Scratch

              4. Enter/select:

              Name: [Name for this template]
              Theme: [Your current theme]
              Template Class: Custom 1
              Template Type: Named Column (row template)

              5. Click Create.

              6. Click the Status Report link in the Templates report.

              7. Enter the following properties:

              Row Template 1
              <tr class="#SAL_CLASS#"><td>#1#</td><td>#2#</td>...include a cell with a column substitution for every column in your report...</tr>
              i.e. the column with the conditional logic in the query is used to apply the required class to the row.
              Before Rows
              <!-- Copy the Before Rows definition from the Standard report template for your current theme and paste here -->
              <tr><th>#1#</th><th>#2#</th>...include a header with a column substitution for every column in your report...</tr>
              After Rows
              <!-- Copy the After Rows definition from the Standard report template for your current theme and paste here -->
              Add CSS to style the rows:

              8. Edit the Page CSS Attributes for the report page:

              Inline
              tr.low td { background-color: yellow; }
              tr.medium td { background-color: white; }
              tr.high td { background-color: red; }
              9. Change the report to use the new template.


              For the alternative jQuery approach, Tom created an example for this thread: +{thread:id=2487955}+
              • 4. Re: Changing Row color of standard SQL report
                William Wallace
                Thanks. I implemented the jquery and works beautifully but will look to implement the way you showed as a long term solution.