Home » Developer & Programmer » Application Express & MOD_PLSQL » Conditionally Setting background color in apex (4.1.0.00.32)
Conditionally Setting background color in apex [message #611176] Fri, 28 March 2014 17:07 Go to next message
tejaabbaraju
Messages: 2
Registered: March 2014
Junior Member
I have a following table.
CREATE TABLE pavan_teja
(
    id               INTEGER NOT NULL
   ,record_type      VARCHAR2 (10 BYTE) NOT NULL
   ,mbr_first_name   VARCHAR2 (50 BYTE) NOT NULL
   ,mbr_last_name    VARCHAR2 (50 BYTE) NOT NULL
   ,mbr_id           VARCHAR2 (10 BYTE)
   ,csr_amt          NUMBER (18, 4)
   ,rating_area      VARCHAR2 (5 BYTE)
);

1. id is unique
2. record_type will have values CMS, FAC only
3. mbr_id will be always same for both CMS, FAC records


Example Data:
ID    RECORD_TYPE    MBR_FIRST_NAME    MBR_LAST_NAME    MBR_ID        CSR_AMT    RATING_AREA
1     CMS            SHARON            CABERLY          0000046669    32.64      014
2     FAC            SHARON            CAVERLY          0000046669    32.64      14
3     CMS            JACK              PARROTTE         0000158332    0          014
4     FAC            JACK              PARROTT          0000158332    2          14
5     CMS            DELORES           LORANZO          0000179553    0          012
6     FAC            DELORES           LOZANO           0000179553    1          12

Now, in the apex application I want to hightlight the columns that are not same between CMS and FAC records by comparing them using MBR_ID which will be always same for consecutive records.
I do not want to highlight the entire record. I want to highlight only the columns that are not same in the records where the MBR_ID is same.

Please help me!!!

[Updated on: Fri, 28 March 2014 17:07]

Report message to a moderator

Re: Conditionally Setting background color in apex [message #611189 is a reply to message #611176] Sat, 29 March 2014 03:56 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, built-in Apex formatting tool can't do that (at least, I don't know how to do it). Therefore, I had to write my own code. Here's how.

First, with some help of the LAG analytic function, I calculated value of the previous record's value for every column - it is the "WITH TEST AS" with factoring clause. If you aren't familiar with it, run the query in SQL*Plus (or any other tool you use to access an Oracle database), just to see what it does.

Its result is "input" for Interactive Report's final query which then - in CASE - compares value of current and previous values. If they are different, I'm painting them red.
with test as
(
select 
  id, 
  record_type,
  mbr_first_name, 
  lag(mbr_First_name) over (partition by mbr_id order by record_type) prev_mbr_first_name,
  mbr_last_name, 
  lag(mbr_last_name) over (partition by mbr_id order by record_type) prev_mbr_last_name,
  csr_amt,
  lag(csr_amt) over (partition by mbr_id order by record_type) prev_csr_amt,
  rating_area,
  lag(rating_area) over (partition by mbr_id order by record_type) prev_rating_area
from pavan_teja
)
select 
  id,
  record_type,
  case when mbr_first_name <> prev_mbr_first_name then
            '<font color="red">'|| mbr_first_name ||'</font>'
       else mbr_first_name
  end mbr_first_name,
  --
  case when mbr_last_name <> prev_mbr_last_name then
            '<font color="red">'|| mbr_last_name ||'</font>'
       else mbr_last_name
  end mbr_last_name,
  --
  case when csr_amt <> prev_csr_amt then
            '<font color="red">'|| csr_amt ||'</font>'
       else to_char(csr_amt)
  end csr_amt,
  --
  case when rating_area <> prev_rating_area then
            '<font color="red">'|| rating_area ||'</font>'
       else rating_area
  end rating_area
from test
order by id, record_type;  

Under "Report Attributes" tab, you have to set all those columns type to "Standard Report Column" otherwise you'll get rubbish instead of red coloured text (but you can try it, just to see how it looks like).

The final result is:

./fa/11797/0/

Re: Conditionally Setting background color in apex [message #611773 is a reply to message #611189] Tue, 08 April 2014 15:10 Go to previous messageGo to next message
tejaabbaraju
Messages: 2
Registered: March 2014
Junior Member
Hi littlefoot , thanks for your help it worked.

I have one more question, I have added a check box button to the existing query , so that user has an ability to select the records. There is a export to file button which is used to write the selected records into the .csv file.

My question is will i be able to write the records with colors into the .csv file?
-- process behind the export to file button
DECLARE
    v_file_path       VARCHAR2 (200) := lib_owner.ph_oracle_environment.get_output_directory;
    v_delimiter       VARCHAR2 (2) := ',';
    v_file_handle     UTL_FILE.file_type;
    v_file_name       VARCHAR2 (50) := 'discrepancies';
    v_header          VARCHAR2 (32767);
    v_report_record   VARCHAR (32767);
BEGIN
    IF lib_owner.ph_oracle_environment.is_prod_db_yn = 'N'
    THEN
        v_file_name   := v_file_name || '_' || 'test' || '.csv';
    ELSE
        v_file_name   := v_file_name;
    END IF;

    --open file

    v_file_handle   := UTL_FILE.fopen (v_file_path, v_file_name, 'w');
    v_header        :=
           'Discrepancy Batch Id'
        || v_delimiter
        || 'Record Type'
        || v_delimiter
        || 'Exchange Member Id'
        || v_delimiter
        || 'Member First Name'
        || v_delimiter
        || 'Member Last Name'
        || v_delimiter
        || 'Exchange Subsciber Id'
        || v_delimiter
        || 'External Member Id'
        || v_delimiter
        || 'QHP Id'
        || v_delimiter
        || 'APTC Amount'
        || v_delimiter
        || 'CSR Amount'
        || v_delimiter
        || 'Tobacco User Code'
        || v_delimiter
        || 'Rating Area'
        || v_delimiter
        || 'Date of Birth';

    UTL_FILE.put_line (v_file_handle, v_header);

    FOR i IN 1 .. APEX_APPLICATION.g_f01.COUNT
    LOOP
        SELECT DISTINCT
                  disc_batch_id
               || v_delimiter
               || record_type
               || v_delimiter
               || exchange_mbr_id
               || v_delimiter
               || mbr_first_name
               || v_delimiter
               || mbr_last_name
               || v_delimiter
               || exchange_subsc_id
               || v_delimiter
               || ext_mbr_id
               || v_delimiter
               || qhp_id
               || v_delimiter
               || aptc_amt
               || v_delimiter
               || csr_amt
               || v_delimiter
               || tobacco_use_cd
               || v_delimiter
               || rating_area
               || v_delimiter
               || mbr_birth_date
          INTO v_report_record
          FROM indv_recon_disc
         WHERE disc_id IN (SELECT APEX_APPLICATION.g_f01 (i) FROM DUAL);

        UTL_FILE.put_line (v_file_handle, v_report_record);
    END LOOP;

    --close file
    UTL_FILE.fclose (v_file_handle);
END; 


Re: Conditionally Setting background color in apex [message #611774 is a reply to message #611773] Tue, 08 April 2014 15:17 Go to previous message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CSV stands for comma separated values. I do not think that it supports any kind of formatting. You should pick a different file type if it is an important requirement.
Previous Topic: need some help with a code in application express
Next Topic: Apex installation problem
Goto Forum:
  


Current Time: Fri Aug 29 19:17:15 CDT 2014

Total time taken to generate the page: 0.12153 seconds