Flexible date in a prompt

Having a flexible date value in a prompt is a feature which is very often sought after by users who have to schedule reports on regular basis as well as those who want to have easier life while running manually a report. Very often these reports have to be run for a particular date like today, yesterday, first of a month or last of a prior month. Fortunately we can accomplish this with little coding.

We can build in a universe a predefined filter with a prompt on a date and customize it to have a flexible date. Below we have 2 examples. Which solution to choose can be determined by answering the question: Do you need to provide users with a calendar feature? If the answer is yes then choose the first solution, if no then the later one.

Solution 1 – Users choose a date datatype value and a calendar is available to them. Using some exotic dates like 9/9/9999 or 1/1/1111 etc we can assign a system based dates values to them. Make sure your users are aware of what these custom dates mean. In my example today’s date is hiding behind 9/9/9999 value.

pic4.1

Here is a sql code:

(
	@prompt(
	'Select Date or keep default (9/9/9999) set to today:','D',,
	Mono,Free,Persistent,{'9/9/9999'},User:0) 
	<> to_date('9/9/9999','MM/dd/yyyy') 

	AND 

	Table_Name.Date_Column = 
	@variable(
	'Select Date or keep default (9/9/9999) set to today:'
	)
) 
OR 
( 
	@variable(
	'Select Date or keep default (9/9/9999) set to today:'
	)  =  
	to_date('9/9/9999','MM/dd/yyyy') 

	AND 

	to_char(Table_Name.Date_Column,'MM/dd/yyyy') = 
	to_char(sysdate,'MM/dd/yyyy')
)

Solution 2 –  If users don’t require the calendar feature we are open to a bigger variety of custom dates because we can choose a character to be a datatype in a prompt and create multiple labels. In my example I have ‘Today’, ‘Yesterday’, ‘Last Friday’, ‘Last Monday’, ‘Prior Month Last Day’, ‘Current Month First Day’, ‘Prior Quarter Last Day’. You can keep adding them or removing, it’s up to you. Behind each label you just need to construct a desired formula using a sysdate.

pic4.2

Here is a sql code:

(
	@prompt('Type Date (MM/dd/yyyy) or choose a value:','A',
	{'Today','Yesterday','Last Friday','Last Monday',
	'Prior Month Last day','Current Month First Day',
	'Prior Quarter Last Day'},Mono,Free,Persistent,
	{'Today'),User:0) 
	NOT IN
	(
	'Today','Yesterday','Last Friday','Last Monday',
	'Prior Month Last day','Current Month First Day',
	'Prior Quarter Last Day'
	) 

	AND 

	Table_Name.Date_Column = 
	to_date( 
	@variable('Type Date (MM/dd/yyyy) or choose a value:') ,
	'MM/dd/yyyy') 
) 
OR 
( 
	@variable('Type Date (MM/dd/yyyy) or choose a value:') 
	= 'Today'
	AND 
	Table_Name.Date_Column = trunc(sysdate) 
) 
OR 
( 
	@variable('Type Date (MM/dd/yyyy) or choose a value:') 
	= 'Yesterday'
	AND 
	Table_Name.Date_Column = trunc(sysdate-1) 
) 
OR 
(  
	@variable('Type Date (MM/dd/yyyy) or choose a value:') 
	= 'Last Friday'
	AND 
	Table_Name.Date_Column = trunc(next_day(sysdate-7,'FRIDAY'))
) 
OR
( 
	@variable('Type Date (MM/dd/yyyy) or choose a value:') 
	= 'Last Monday' 
	AND 
	Table_Name.Date_Column = trunc(next_day(sysdate-7,'MONDAY')) 
) 
OR 
( 
	@variable('Type Date (MM/dd/yyyy) or choose a value:') 
	= 'Prior Month Last Day' 
	AND 
	Table_Name.Date_Column 
	= trunc(LAST_DAY(ADD_MONTHS(sysdate,-1))) 
) 
OR 
( 
	@variable('Type Date (MM/dd/yyyy) or choose a value:') 
	= 'Current Month First Day'
	AND 
	Table_Name.Date_Column = trunc(sysdate,'MONTH') 
) 
OR 
( 
	@variable('Type Date (MM/dd/yyyy) or choose a value:') 
	= 'Prior Quarter Last Day' 
	AND 
	Table_Name.Date_Column = trunc(sysdate,'Q')-1 
)

 

The both examples are constructed for Oracle but with a little change you can adjust them to any database. You might ask why I don’t use a CASE WHEN statement. First of all it is performing better when you use AND/OR, second the script is cleaner and shorter. Probably you noticed that I use @Variable() instead of Prompt() in many places. It is enough to define a full prompt definition only once in a code and then just reference it with @Variable(). This way the code is shorter and easier to maintain. Let me know if you see any drawbacks of replacing @Prompt() with @Variable().

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s