Multi Value Presentation Variables
You may often find that if you have a prompt that allows users to select multiple string values then sets a presentation variable. If later you need to use this as a filter as an IN list for another analysis, you need to ensure the BI server places single quotes around pathetic comma separated list. To achieve this you can use the following notation
@{variable name}[‘@’]{default value}
Date/Time Maths
You can format a date in OBIEE via evaluate
EVALUATE(‘to_char(%1,%2)’ AS CHAR, <expression> ,’DD-MM-YYYY’)
This gives you the number of days between 2 dates
TIMESTAMPDIFF(SQL_TSI_MONTH,<from date>,<to_date>)
Add 2 months to a date
TIMESTAMPADD(SQL_TSI_MONTH,2,<expression>)
This gives you the first day of the current month
TIMESTAMPDIFF(sql_tsi_day, -1*(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)
LEAD/LAG – Analytic Functions in OBIEE
In order to do this, you must have the EVALUATE function enabled otherwise you will get an error
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
# EVALUATE_SUPPORT_LEVEL:
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.
EVALUATE_SUPPORT_LEVEL = 2;
This can be used on the Sample Sales subject area, and will show you Revenue by Product, showing previous Day/Time Period Revenue
EVALUATE(‘LAG(%1,1,0) OVER (ORDER BY %1,%2’, <expression to LAG/LEAD> ,< order by 1 expression > , <order by 2 expression > )
cast(evaluate(‘LAG(%1,1) OVER ( ORDER BY %2)’,”Base Facts”.”1- Revenue”,”Products”.”P1 Product” ) as integer )
EVALUATE and Partition By Example
A customer had a requirement to replicate some Disco expressions which involved some simple analytic functions that used MAX() and PARTITION ORDER BY Clauses… Seems not possible in native OBIEE, so would have to use EVALUATE to do this…
See the “Analytic Functions” My Dashboard page on my Sample App 309 VM…, This page provides some test cases where you may need to use the Oracle RDBMS Analytical functions i.e. LEAD/LAG, or just a MAX() but with PARTITION ORDER BY clauses.
There are some things to be aware of here, as found on site at CSC, For example you might need to perhaps write a query such as –
MAX(HIRE_DATE) OVER ( PARTITION BY HIRE_DATE)
You may receive an error “Data Type 25 Not supported” IF the data type within OBIEE RPD is a DATE type. However DATETIME generally work !!! if the data type of the column is DATE , this works, by first converting the date to a string, then using hidden obiee function TO_DATETIME the analytic function works…
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 46033] Datatype: 25 is not supported. (HY000)
WORKADOUND is to use the OBIEE hidden function TO_DATETIME on any data types that are DATE. You first though need to convert the date to a string, then use TO_DATETIME, see sample expression that will be used
evaluate(‘MAX(%1) OVER ( PARTITION BY %2)’,to_datetime(EVALUATE(‘to_char(%1,%2)’ AS CHAR, “Sales Person”.”Sales Rep – E7 Hire Date” ,’DD-MM-YYYY’),’dd-mm-yyyy’),”Sales Person”.”E1 Sales Rep Name”)
Note here the use to TO_DATETIME
Its wrapped around a call to “TO_CHAR” to convert the DATE to a string before then converting to a DATETIME type
The other way round this is to change the Data Type in the physical table defintion in the RPD to be DATETIME, then a simple EVALUATE(MAX() PARTITION BY ) should work without throwing the “Data Type 25” error we see currently in OBIEE
Have tested this with patched OBIEE to 140527 and the same “Date Type 25” error exists if the type in the RPD is DATE
to_datetime example
to_datetime(EVALUATE(‘to_char(%1,%2)’ AS CHAR, “Sales Person”.”Sales Rep – E7 Hire Date” ,’DD-MM-YYYY’),’dd-mm-yyyy’)
to_datetime(EVALUATE(‘to_char(%1,%2)’ AS CHAR, “Dim – Date Pay Elements”.”CALENDAR_DATE” ,’DD-MM-YYYY’),’dd-mm-yyyy’)
Collapse Sections by Default
Set an OBIEE section on a dashboard to display collapsed by default You can do this refer to
http://obieepedia.wordpress.com/2013/06/19/how-to-collapse-or-minimize-a-dashboard-section-by-default-in-obiee/
You just need to find the “section id ” of the section you want to be minized add this java script in a text box within that section o the page
<script type=”text/javascript”>
var sectionId = “d:dashboard~p:b04pk3680em3ueb9~s:8gh2o0hqns9hdo4v”;
var sectionDiv = document.getElementById(“Embed”+sectionId);
var plusImg = document.getElementById(sectionId+”Max”);
var minusImg = document.getElementById(sectionId+”Min”);
var contentsTable = document.getElementById(sectionId+”Contents”);
minusImg.style.display=”none”;
contentsTable.style.display=”none”;
plusImg.style.display=””;
sectionDiv.setAttribute(“minimized”,”true”);
</script>
The + or – signs you see on the sections to expand/minimize arein the dashboard “style” .. So choosing “blafp” is where this is modified..
The Style is located here in e ORACE_HOME
/home/oracle/obiee/Oracle_BI1/bifoundation/web/appv2/res/s_blafp
So the style can be changed here..
Think the images might be here…
/home/oracle/obiee/Oracle_BI1/bifoundation/web/appv2/res/s_FusionFX/dashboard
i.e.
disclosure_expanded.png either > or + / –
disclosure_collapsed.png so this can be customized here !!