One of the most requested features in Web Intelligence reports is to have a default value in a time range prompt that will enable to chose time frames such as current day, last day and ctr.
In the following example I will demonstrate how to implement such logic in a very simple way
I am using:
- BI4 SP6
- Oracle 11
- A Custom Universe containing a time view table
- Information design tool
- First we will create s special super prompt condition object that will enable us to choose date range ,current month or last month :
This script uses a case when phrase that allows choosing
several time ranges.
For example ‘
this month’ will return
the function: TRUNC(SYSDATE,'MM') which will result us the current month data
Here is the full syntax:
"GenericDateTable".GENERICCURRENTDATE between
Case
WHEN @Prompt('From
Date(dd/mm/yyyy)','A',,Mono,Free,Not_Persistent)='last month' then
trunc(add_months(last_day(sysdate),-2)) + 1
WHEN @Prompt('From
Date(dd/mm/yyyy)','A',,Mono,Free,Not_Persistent)='this month' then TRUNC(SYSDATE,'MM')
Else To_Date(@Prompt('From
Date(dd/mm/yyyy)','A',,Mono,Free,Not_Persistent),'DD/MM/YYYY')
End
AND
Case when @Prompt('To Date
(dd/mm/yyyy)','A',,Mono,Free,Not_Persistent) = 'last month' THEN trunc(add_months(last_day(sysdate),-1))
WHEN @Prompt('From Date(dd/mm/yyyy)','A',,Mono,Free,Not_Persistent)='this
month' then ADD_MONTHS(TRUNC(SYSDATE,'MM'),+1)
Else
To_DATE(@Prompt('To Date
(dd/mm/yyyy)','A',,Mono,Free,Not_Persistent),'DD/MM/YYYY')
End
2. Now we will first test our prompt to varify that it is working currectly :
This month data :
Last month data :
3.Now we will add a sinple parameter to the prompt syntac which will allow us to use a default value
that corresponed to our prompt ,lets use the value ‘last month’,the last parameter in the prompt is the one that holds the default value and as you can see when we open the report the default value is already there :
"GenericDateTable".GENERICCURRENTDATE between
Case WHEN @Prompt('From
Date(dd/mm/yyyy)','A',,mono,free,Not_Persistent,{'last month'})='last
month' then trunc(add_months(last_day(sysdate),-2)) + 1
WHEN @Prompt('From
Date(dd/mm/yyyy)','A',,mono,free,Not_Persistent,{'last month'})='this month'
then TRUNC(SYSDATE,'MM')
Else To_Date(@Prompt('From
Date(dd/mm/yyyy)','A',,mono,free,Not_Persistent,{'last month'}),'DD/MM/YYYY')
End
AND
Case when @Prompt('To Date
(dd/mm/yyyy)','A',,mono,free,Not_Persistent,{'last month'}) = 'last month'
THEN
trunc(add_months(last_day(sysdate),-1))
WHEN @Prompt('From
Date(dd/mm/yyyy)','A',,mono,free,Not_Persistent,{'last month'})='this month'
then ADD_MONTHS(TRUNC(SYSDATE,'MM'),+1)
Else To_DATE(@Prompt('To Date
(dd/mm/yyyy)','A',,mono,free,Not_Persistent,{'last month'}),'DD/MM/YYYY')
End
Now when we will run the report we will see the default value:
And that’s it….
Conclusion:
Implementing default dynamic time range is simple but can be
also used to reports that are required to run daily using date range but also
required to be scheduled on last month, by implementing such prompt we will
reduce the number of versions of the same report: one version for daily running
and the other version for scheduling…
month_data.png)
:
