Thursday, 6 February 2014

OBIEE 11g – Advanced Prompts for Financial and Other Data such as ‘Last Year’, Last Month’ or ‘Last 6 Month’



OBIEE 11g supports number of different prompts such as column prompt, currency prompt , image prompt and variable prompt. It supports number of  user input such as text, choice list, slider, check boxes, radio buttons and list boxes. It has been improved a lot from previous release and going stronger with every new release.

 Today I came across a customer request; customer would like to use prompts to choose as user friendly time parameters such as Last Year, Last Month or Last 6 Month to drive underline report. I do understand why customer would like these type of parameter specific dashboard filters to drive reports. I am struggling to find out why Oracle does not offer a straight forward option to provide global filter which caters time series specific parameters based filter so user can choose Last Year, Last Month or Last 6 Month are filters to drive underline report.

Is there any work around? I have started looking into the option to achieve this within the boundaries of what OBIEE 11g offer. Good news is that yes I have achieved something to show and tell. This blog is an attempt to explain how we can model to get prompt specified.


Dashboard Prompt and Presentation Variable

The first hurdle is to define a prompt with options such as Last Year, Last Month or Last 6 Month within dashboard prompt so user can choose the one of the option and apply to run report.


Type : - Column Prompt

Choose Any Column – should be of type Var Char if not make sure it is casted

Label  - Choose Filter

User Input – Choice List, you can choose any of the user input supported

SQL – Which generate parameters e,g Last Year, Last Month etc

SELECT  CASE WHEN "Time"."Month" IS NULL THEN  'Last Year' END FROM "Human Resources - Workforce Profile"
UNION ALL
SELECT  CASE WHEN "Time"."Month" IS NULL THEN  'Last Month' END FROM "Human Resources - Workforce Profile" UNION ALL

SELECT  CASE WHEN "Time"."Month" IS NULL THEN  'Last 6 Month' END FROM "Human Resources - Workforce Profile"

Set Variable – Presentation Variable
Name – q1



Report Definition

A simple report with three columns Year, Month and Headcount , from a standard OBIA Application.


Subject Area – Human Resources – Workforce Profile

Selected Columns
"Time"."Year"
"Time"."Month"
"Workforce Profile Facts"."Headcount"



Filter

This is most critical part of the report; the filter should be driven by presentation variable value selected from dashboard prompt. If user choose ‘Last Year’ it should show all data for last 12 months since today , if user choose ‘Last 6 Month’ the report should show all data for last 6 months since today.



"Time"."Date" >

CASE WHEN '@{q1}{Last Year}'='Last Year'   --------------------- Presentation variable is ‘Last Year’
THEN
TIMESTAMPADD(SQL_TSI_YEAR, - 1, CURRENT_DATE )  ------------ Date >  Current Date – 1 Year

WHEN  '@{q1}{Last Year}'='Last Month' THEN   ---------------Presentation variable is ‘Last Year’
TIMESTAMPADD(SQL_TSI_MONTH, - 1, CURRENT_DATE )   ---------Date >  Current Date – 1 Month

WHEN '@{q1}{Last Year}'='Last 6 Month' THEN   ---------------Presentation variable is ‘Last Year’
TIMESTAMPADD(SQL_TSI_MONTH, - 6, CURRENT_DATE )   ------ Date >  Current Date – 6 Months

END


 
Dashboard Page


Choose Filter – Last Year




Report
Headcount for Last 12 Months
Choose Filter – Last Month




Report
Headcount for Last 30 Days
Choose Filter – Last 6 Month




Report
Headcount for Last 6 Month


Time Stamp Add Usage

Some other expressions which might be useful for wider audience are as follows.

First Date of Week:
TIMESTAMPADD(SQL_TSI_DAY, (DAYOFWEEK(Current_date)*-1)+1, Current_date)

Lat Date of Week:
TIMESTAMPADD(SQL_TSI_DAY,DAYOFWEEK(Current_Date)*-1,TIMESTAMPADD(SQL_TSI_WEEK, 1,
Current_date))

First Date of Year:
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE)

Last Date of Year:
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1,
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE)))

Inference

Oh yes, it worked as expected. It is a shame that why OBIEE as product could not support such a genuine requirement. I have seen it is very common requirement to have an option to filer data for ‘Last Year’, ‘Last Month’, Last 6 Months’, … ‘Last 3 Years’ etc,

I hope you find this information useful. Please do comment if you like it or not.

5 comments:

  1. Had a similar requirement and used the same method what you have shown here. Few months later encountered performance issues.During investigation it was found that prompt SQL could be optimized in such a way that it does not include 'case' statements and uses Union clause.
    Ex-
    SELECT 'Last Year' FROM "Human Resources - Workforce Profile"
    UNION
    SELECT 'Last Month' FROM "Human Resources - Workforce Profile"
    UNION
    SELECT 'Last 6 Month' FROM "Human Resources - Workforce Profile"

    -BDW all your blogposts are informative and qualitative. Keep blogging. All the best.

    Best wishes. Nachiket
    obiee1000.blogspot.in

    ReplyDelete
  2. Hello

    Really good one.. I have a question. Where did you add this filter? I am not able to figure that out.

    ReplyDelete
  3. Using this we can achieve only Last 360 days data ,Last 30 days data etc.But how can i get Last Years Data,Last Months data.Thank You.

    ReplyDelete
  4. good ..... put i can't figure the filter

    ReplyDelete