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.