How-to: Drill through & Guided Navigation 101 w/ OBIEE 10g Answers

OBIEE offers 2 types of drilling for slicing & dicing:

1) Drill down- Using a hierarchy to navigate to a lower level of granularity for a specific report or data set

2) Drill through/drill across  - Using Answers to navigate from report 1 to report 2 in order to view additional fields while saving the filters used in report 1.

Consider the requirement of navigating from a summary report which displays Revenue by Quarter to a detailed report which breaks down revenue by month and includes additional metrics such as Booked Amount and Unit Price.

If the requirement only needed revenue by Month, then we could configure a hierarchy to drill from Quarter to Month. But since the user needs to see additional metrics in the detailed report, we must utilize the drill through feature to accommodate this requirement.

Step 1:  Create Summary Report with specified prompts and metrics.

In the above example I used SampleSales OOTB RPD to create a report that has slices Revenue by Quarter and created a prompt to filter the report by Area.

Make sure you specify M02 Area has prompted in your summary report:

 Step 2: Create Detailed report 1 with dimensions from report 1 set to filters as 'Prompted'

If you want both 'T03 Per Name Qtr' and 'M04 Region' values to be applied to your detail report, then you must set 'T03 Per Name Qtr' and 'M04 Region' to 'is Prompted' in your detail report.

Step 3: In Summary Report 1, enable Guided Navigation to to Detailed Report 1

For your KPI (in this example 0-01 Revenue (Sum All)) , click Column Properties - > Column Format Tab ->  Value Interaction Drop Down Menu - > Select Navigation then find your detailed report 1

Step 4: Test by Navigating from Summary Report 1 to Detail Report 1

I recommend adding the 'Filter' has a section to your detailed report to confirm that the values are getting passed. 

Report 1:   Filters ; M02 Area = 'Area 0', T03 Per Name Qtr = '2007 Q1', M04 Region = 'South' for first row where 1-01 Revenue (Sum All) = 27,391

Report 2:

keywords: obiee answers, drill down, drill through, drill down report, obiee guided naviation

How to: Default Date Prompt to NULL in OBIEE Answers

Consider the following scenario:

• You have an accumulating periodic fact table that tracks an item as it moves through its distribution life cycle from warehouse to the point of sale at a store.

If applying Kimball Design Principles (which you should always follow in a denormalized dimensional model), your accumulating periodic fact table would be sparsely populated and contain NULL values for stages the item has not yet encountered. Take the fact table below as an example.

This fact table is what Kimball classifies as a 'periodic accumulating snapshot' because:

1) each record represents the life cycle of 1 item
2) each record is updated as it reaches a key date, as opposed to a new record being added
3) each record has a definite beginning and end

Assume you had a requirement to create a report that identified all of the products that arrived at the store but have yet to be sold.

To translate this technically, you're looking for Sales Dates that are NULL.

You cannot simply create a filter that says 'Sales Date <> NULL' because NULL is not a valid DATE type. NULL in DATE is represented as a '01-01-1900'. You could then be tempted to create a filter as 'Sales Date <> '01-01-1900' but that is not a valid DATE either, since by default Dates are stored as DATETIME stamps.

An easy way to exclude Dates that are stored as NULL, is to do it in answers as follows:


Step 1: Create a Dashboard Prompt for your Date that Defaults to SQL Values:

and populate it with:

SELECT

case when 1=0 then Time."Fiscal Date" else Cast(NULL as Date)  END

FROM "ENTER LOGICAL FACT TABLE HERE"

The 'Enter logical fact table here' should be replaced with the name of the logical fact table of that subject area in the BMM.

Step 2: Edit Column Formula to Cast as Date

Date dimensions default to DATETIME in the repository so you will need to cast your column as a Date.

This will generate the following result:

Note that although the default value looks 'blank', a filter of NULL is being applied in the physical query:

How to: First Day of Last Month & the various combinations

A common requirement is to have default date values in a prompt - see below:

1) It helps limit the data set, and 2) it is a way to tell the end user the format of the input values.

But most of the time, the user will want the values to change as time progresses, i.e. 'default to last month's date range'. In this case, a static variable will not due.

How do you create default dates in the prompt that change as time progresses?

Step 1:

Identify what the default values should be. In the above example, the start date is 'First day of last month' and the end date is 'last day of last month', assuming the current month is May 2012.

First day of last month is created as:

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last day of last month is created as:

 TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

Step 2:

The issue is that you cannot add the above 2 functions to a prompt because it is not in a recognized format, it must be in one of the following formats:

 Step 3:

We can enclose the above 2 functions in a logical SQL format that Answers will understand ,as follows  :

SELECT

case when 1=0 then Time."Fiscal Date" else  INSERT FUNCTION HERE

from

'Your-logical-subject area'

END
 

 We must do a case 1=0 operation to force the sql code to always default to the timestampadd function

 Step 4:

Using the above logic:

first day of last month:

SELECT

case when 1=0 then Time."Fiscal Date" else TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) end

FROM "Subject area - Subject area name"

last day of last month:

SELECT

case when 1=0 then Time."Fiscal Date" else TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

 end 

FROM "Subject area - Subject area name"

Step 5:

Paste the above into your prompt with default values as 'SQL Results':

 

 Step 6:

And make sure to cast your prompt value as Date - it defaults to datetime.

 

EXTRA Credit:


What if you have 20 prompts that have this requirement?

Answer: Create 2 dynamic variables in the rpd with the following logic:

First day of last month:

select Last_Day(ADD_MONTHS(SYSDATE,-2))+1 FROM DUAL

Last day of last month:

select Last_Day(ADD_MONTHS(SYSDATE,-1))  FROM DUAL

Note how Oracle does NOT have a first_day function :(


Need other day combinations? First day of last quarter? Last day of next month? First day of last year? 

Use the logic below:

First Day of the Previous Year

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.

First Day of the Current Year 
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 
This calculation returns the first day of the year by deducting one less than the total number of days in the year.
 
First Day of the Next Year 
TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

 From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.
 
First Day of the Previous Month 
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.

First Day of the Current Month

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

 This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.
 
First Day of the Next Month 
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.
 
First Day of Current Quarter 
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.
 
Last Day of the Previous Month 
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.
 
Last Day of Current Month 

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.
 
Last Day of the Next Month 
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

 From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
 
Last Day of Previous Year 

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.
 
Last Day of Current 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))) From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.
 
Last Day of the Next Year 
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

 From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.

Last Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.
 
Number of days between First Day of Year and Last Day of Current Month

 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))) 

For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month. 

keywords: obiee 10g, answers, prompt, presentation variables, first day last month combinations, repository variables,