OBIEE direct database request conditional formatting

Direct Database Requests & Conditional Formatting

10/8/2018

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.

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?

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 ‘ COLOR=“red”>’||age||’’ ELSE
...”the rest of your case statement here” END
FROM “your table”

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

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 ‘ COLOR=“red”>’||ROUND(((MAX(start_ts- sysdate))*-24*60),0)||’’ ELSE
‘ COLOR=“green”>’||ROUND(((MAX(start_ts- sysdate))*-24*60),0)||’’ END,

and then use this column for the conditonal formatting.

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?

Your comment will be posted after it is approved.

Elffar Analytics Blog

Direct Database Requests & Conditional Formatting

Leave a Reply.

Author

Archives

Categories