• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6978
  • Last Modified:

Oracle APEX (Advanced) - Highlight row based on condition

I have this query on a classic report.

SELECT   w.vendor, w.qty, r.rec
    FROM (SELECT   vendor, SUM(ord) AS qty
              FROM wag where ship_date = :P0_DATE
          GROUP BY vendor) w,
         (SELECT   vendor, SUM(ord) AS rec
              FROM recinc where "sDATE" = :P0_DATE
          GROUP BY vendor) r
   WHERE w.vendor = r.vendor
ORDER BY vendor

Open in new window


That query gives me the results of the attached file,

I want to highlight rows where the "Ordered" and "Received" column do not match

12 and 12 would not be highlighted
12 and 10 would be highlighted.

I am looking for someone to walk me through the process, i have been researching for a couple of days now, refraining from coming on here and asking with no solution in sight.
compare.png
0
futureDBA
Asked:
futureDBA
  • 5
  • 2
1 Solution
 
sdstuberCommented:
please don't pictures unless you have to

post text
0
 
futureDBAAuthor Commented:
understood. apologies
0
 
sdstuberCommented:
with text, the data is reusable, with images it's not.  So I just used my own data

I assume you have a query that looks something like this...


SELECT id,
       vendor_name,
       ordered,
       received
  FROM vendor_data
 WHERE (INSTR(UPPER(vendor_name), UPPER(NVL(:p1_report_search, vendor_name))) > 0)

add one column to that query, this new column defines your highlighting colors

SELECT id,
       vendor_name,
       ordered,
       received,
       CASE WHEN LNNVL(ordered = received) THEN 'red' ELSE 'black' END vendor_colors
  FROM vendor_data
 WHERE (INSTR(UPPER(vendor_name), UPPER(NVL(:p1_report_search, vendor_name))) > 0)


make this change in the Region Source field of the Region Definition Tab
click Apply Changes

on the Report Attributes tab clear the "Show" check box for the new column
click Apply Changes

then edit each column under Column Formating, HTML Expression  enter this...

<span style="color:#VENDOR_COLORS#;font-weight:bold;">#ORDERED#</span>

change ORDERED to RECEIVED and VENDOR_NAME for those columns

apply changes after each column

run your report  matching values will be in black, unmatched will be in red
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
futureDBAAuthor Commented:
I followed you completely until "Column Formatting" HTML EXPRESSION... i cannot find this under column attributes.


I found it furthered down.

When i ENTERED #ORDERED# and ran the query,


i literally get #ORDERED# as a result, but what I can say is that for the columns that are suppose to be RED, they are.. just not with the value, literally with #ORDERED#
0
 
futureDBAAuthor Commented:
I also didn't understand "
change ORDERED to RECEIVED and VENDOR_NAME for those columns"
0
 
futureDBAAuthor Commented:
got it... thank you sir..
0
 
futureDBAAuthor Commented:
perfect
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now