cancel
Showing results for 
Search instead for 
Did you mean: 

Default Date values

nsandee
Participant
0 Kudos

Hello All,

There are two reports pointing to the same universe and Oracle is the backend.

Report 1 has 9 Prompts,  Out of which I need to show default values for the Start Date : Current Date whereas End Date:  Current Date + 3 months

Report 2 has 7 prompts, Out of which I need to show default values for the Start Date : Yesterday whereas  End Date:  Yesterday – 7 Days

Currently, Start Date & End date for both the reports are coming from a condition

Begin & End Date :

EVENTSTRT_TS  >=  to_date(@Prompt('2.Begin Date(mm/dd/yyyy)', 'D' , , mono, free),'dd/mm/yyyy hh24:mi:ss')  AND SCHDL_EVENTSTRT_TS < to_date(@Prompt('3.End Date(mm/dd/yyyy)', 'D' , , mono, free),'dd/mm/yyyy hh24:mi:ss')+01

How can we achieve for 2 reports ?

Sandy R

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

In BI4.1 SP06 you can use formulas for the default date in IDT:

nsandee
Participant
0 Kudos

Hello Victor,

we are on BI 4.0 Sp4 only. We didnt migrate yet to BI 4.1

Former Member
0 Kudos

In that case Sandy, this blog entry will help you


http://www.dagira.com/2008/07/21/using-a-magic-date-value-in-prompts/

nsandee
Participant
0 Kudos

Hello All,

If anyone would like to know the process, please find the steps which i implemented.

I have created two conditions in the universe. Use these conditons in the report query filter according to the requirement. Database used is oracle and we are at BO 4.0sp4

Condition 1: (Start Date and End Date)

============================

table.column_name  BETWEEN CASE @Prompt('2.Begin Date(mm/dd/yyyy)','A',,mono,free,,{'Today'})
WHEN 'TODAY' THEN sysdate
ELSE to_date(@Prompt('2.Begin Date(mm/dd/yyyy)','A',,mono,free),'mm/dd/yyyy') END
AND
CASE @Prompt('3.End Date(mm/dd/yyyy)','A',,mono,free,,{'Today+3 Months'})
WHEN 'TODAY+90' THEN add_months(sysdate,3)
ELSE to_date(@Prompt('3.End Date(mm/dd/yyyy)','A',,mono,free),'mm/dd/yyyy') END

Condition 2: (Begin Date and End Date):

=============================
table.column_name  BETWEEN CASE @Prompt('2.Begin Date(mm/dd/yyyy)','A',,mono,free,,{'Yesterday'})
WHEN 'YESTERDAY' THEN sysdate-1
ELSE to_date(@Prompt('2.Begin Date(mm/dd/yyyy)','A',,mono,free),'mm/dd/yyyy') END
AND
CASE @Prompt('3.End Date(mm/dd/yyyy)','A',,mono,free,,{'Yesterday-7 days'})
WHEN 'YESTERDAY-7' THEN sysdate-8
ELSE to_date(@Prompt('3.End Date(mm/dd/yyyy)','A',,mono,free),'mm/dd/yyyy') END

Answers (0)