ELFFAR ANALYTICS
  • Home
  • Services
  • Contact Us
  • About Us
  • Blog

Elffar Analytics Blog

Direct Database Requests & Conditional Formatting

10/8/2018

5 Comments

 
I'd start this blog by issuing a warning that Direct Database requests should be used sparingly. A Direct Database Request (DDR) bypasses the OBIEE metadata defined in the repository and this can have security implications if not managed properly where users could potentially have access to data that they should not have access to.

There are some use cases for DDRs like building an OBIEE analysis to display the status of a transactional process for instance.

According to the OBIEE user guide documentation, you can not specify conditional formatting for columns of a Direct Database Request.

This post describes a "workaround" which enables you to achieve this. In the example below, all you need is a little HTML and bingo!

All you need is a CASE statement to implement the conditions of your logic and on the column that you'd be applying the formatting, go to Column Properties > Data Format and change the Treat Text As field to HTML.
Picture
5 Comments
Chip Bash
19/4/2019 04:15:15 pm

It looks like all that's happening is that your coloring the text based on the text value.

What if the field is numeric as an example.

I want all values in field AGE > 10 to be red. if AGE is 80, i want to see 80 red. If it's 79, I want to see 79 red.

in this case will the workaround actually work?

Reply
Elffar Analytics
19/4/2019 06:29:42 pm

If I understand correctly, what you’ll need to do is to modify the CASE statement of your SQL to something along the line of:

SELECT
CASE WHEN age > 10 THEN ‘<B><FONT> COLOR=“red”>’||age||’</FONT></B>’ ELSE
...”the rest of your case statement here” END
FROM “your table”

Reply
Analytics BI
29/5/2019 09:32:41 am

Hi i am trying to implement the conditional formatting based of the work round above. What i am hoping to achieve is that when 'round (((max(start_ts- sysdate))*-24*60),0)' is greater than 25 then font should be 'red' else 'green').

My select statement is below

select
node_id,
to_char(max(start_ts), 'YYYY-MM-DD HH24:mi:ss') as QUERY_START_Time,
to_char(max(sysdate), 'YYYY-MM-DD HH24:mi:ss') as SYSTEM_TIME,
round (((max(start_ts- sysdate))*-24*60),0) Start_to_Sysdate_Diff_Mins,
count(*) No_Of_Queries,
count(distinct session_id) No_Of_Sessions,
count (distinct USER_NAME) as USERS,
count(query_key) as TOTAL_QUERIES,
count(distinct query_key) as DISTINCT_QUERIES,
avg(total_time_sec) as AVG_TIME_SECONDS,
SUM(NUM_CACHE_HITS) as CACHE_HITS
from MY Table
where start_ts > sysdate-60/1440
group by node_id
order by 1

Reply
Elffar Analytics
29/5/2019 09:58:05 am

Add something like this to the SELECT of your SQL statement:

CASE WHEN ROUND(((MAX(start_ts- sysdate))*-24*60),0) > 25 THEN ‘<B><FONT> COLOR=“red”>’||ROUND(((MAX(start_ts- sysdate))*-24*60),0)||’</FONT></B>’ ELSE
‘<B><FONT> COLOR=“green”>’||ROUND(((MAX(start_ts- sysdate))*-24*60),0)||’</FONT></B>’ END,

and then use this column for the conditonal formatting.

Reply
Gino
11/11/2019 02:06:42 am

This is very neat work around. Excellent!
Can you advise how to change the table data background colour based on a value?

Reply

Your comment will be posted after it is approved.


Leave a Reply.

    Author

    I'm an Oracle Ace Associate, Oracle Cloud Platform Enterprise Analytics 2019 Certified Associate and a certified OBIEE 11g implementation specialist.

    Archives

    March 2020
    February 2020
    December 2019
    August 2019
    June 2019
    February 2019
    January 2019
    December 2018
    August 2018
    May 2018
    December 2017
    November 2016
    December 2015
    November 2015
    October 2015

    Categories

    All
    OAC
    OAS
    OBIEE
    OBIEE 12c

    RSS Feed

Powered by Create your own unique website with customizable templates.
  • Home
  • Services
  • Contact Us
  • About Us
  • Blog