OBIEE1kenobi
If you ever need to default date prompts for an OBIEE
report, here are a few tidbits:
See below:
-1 defaults to current date
-1 defaults to first of month
-1 defaults to database value ...via MIN(datecol) function
You will notice adding currentTime is easily done via variable
expression. (in green
above)
Also, grabbing a value from an obiee table is pretty
straight forward
also via logical sql expression
(in orange above)
However, the syntax is a bit tricky if you need to
manipulate the date
(such as defaulting to the first day of the month) via sql
expression.
… even thought you merely want a simple
date formula, it still requires that
you specify the FROM “subject area” syntax
(in red above)
And
it does not seem to like calculations based on the system var ”currentTime”
Another
way to accomplish a static date function/result, would be via a repo var (set
in an init block),
the above works well, and does not require special
permissions on the repo, etc…
Here are the color-coded
statements/vars/formulas used above:
@{system.currentTime}[MM/dd/yyyy]
select
case when 1=0 then null else
TIMESTAMPADD(SQL_TSI_MONTH,
-1,
TIMESTAMPADD(SQL_TSI_DAY,
DAYOFMONTH(CURRENT_DATE)
* -(1) +1,
CURRENT_DATE))
END INDOT_cool_1st_mon_day
from "Professional Services"
select min(OBIEE_PSCS_LOIS_SUBMITTAL_DIM2."Submit Status
Date")
from "Professional Services"
Sample prompt for “how many years to show”
I created a prompt which set a presentation variable
(as
opposed to admin tool defined: repo
dynamic/static & session system/nonsystem) .
This prez var is then shown in the header and details and
used
in the filters (on/for 2 different logical fields/columns).
I use the number of years variable to filter a date field,
and also to filter the first 2 chars of the contract number.
Some interesting features this report uses:
-radio button prompt
-case statements
-CASTing (via the logical sql
syntax),
-some math/arithmetic/subtraction,
-some date manipulation (which is
funky in obiee)
-some string manipulation …using
logical functions such as LEFT()
-variable setting (and usage of
default/fallback values)
-variable displays in header
-variable displays in details
-variable used in filter (for
multiple fields)
-shows record filtering on the report view (so the user can see filtering in
dev env)
I wanted to share in case this comes in handy for someone in
the future….
Prompt/define
prez var:
Display var in header
Display var in detail recs as custom column formula
Note:
syntax allows you to even add a “default” value at the {end}
The filter:
Date Stuff
Date Column formulas (for report/analysis display purposes)
Display year:
YEAR("Dates"."Current NTP Date")
Concat text to a
year:
CONCAT(CAST(YEAR("Dates"."Current NTP
Date") as CHAR), 'aaa')
…because CONCAT cannot handle non-text parms
Concat year to
month (YYYY M)
CONCAT(CAST(YEAR("Dates"."Current NTP
Date") as CHAR), CAST(MONTH("Dates"."Current NTP
Date") as CHAR))
Concat year to
month to day
CONCAT(CAST(YEAR("Dates"."Current NTP
Date") as CHAR), CAST(MONTH("Dates"."Current NTP Date")
as CHAR)) || CAST(DAY("Dates"."Current NTP Date") as CHAR)
Date Filtering
Filter for Last 2 years:year("RFP"."Rfp Target Date") >= year(CURRENT_DATE) – 1
Filter within last 6 months:
"MyTable"."MyDateColumn" >= TimestampAdd(SQL_TSI_MONTH, -6, CURRENT_DATE)
Custom javascript on page (via Narrative View)
Some other oracle obiee bloggers:
http://oranalytics.blogspot.com/2012/06/hide-and-exclude-columns.htmlhttp://obiee10grevisited.blogspot.com/p/questions-and-answers.html
http://obiee911.blogspot.com/2013/04/sort-order-and-display-order.html
http://ruchi-obiee.blogspot.com/
http://obieeforall.blogspot.com/2013/07/obiee-10g-union-request-combine-2-or.html
http://obieeil.blogspot.com/2013/01/obiee-repeated-conditional-format-lazy.html
http://obieeforall.blogspot.com/2013/07/obiee-10g-union-request-combine-2-or.html
http://obieeil.blogspot.com/2013/01/obiee-repeated-conditional-format-lazy.html
Comments
Post a Comment