• Status: Solved
• Priority: Medium
• Security: Public
• Views: 254

# Dates

I use Date Ranges in a lot of parameters in my reports i.e.

Last Full Month
Last Full Week
Yesterday

I always display them in the report header i.e. Report Period : 01/08/2011 to 31/08/2011

The formula is always

if {Date Range?} = 'Last Full Month' then 'Report Period: ' minimum(lastfullmonth) & ' to ' & maximum(lastfullmonth) else
if {Date Range?} = Yesterday' then 'Report Period: ' currentdate - 1 else
etc

Is there a better way of wwriting the formula above at is seems quite long winded, especially if you have many dates?

Thanks
0
Mark Wilson
1 Solution

Commented:
For true ranges ('Yesterday' is not a 'range' in my book....) you can just do..

"Report Period: " &  minimum({?Daterange}) & " to "  & maximum({?Daterange})

But if you want to show 'Yesterday' as a single date then you will have to include a test for it.
0

Commented:
Agree.  If Yesterday is the only single entry you could do this

if {Date Range?} = Yesterday' then
'Report Period: ' currentdate - 1
else
"Report Period: " &  minimum({?Daterange}) & " to "  & maximum({?Daterange})

mlmcc
0

Commented:
1.Create a Date_Range_Type Parameter of String Type and provide these 3 values
"Last Full Month"
"Last Full Week"
"Yesterday"

2.
Create 2 formulas for Start and End Date of the Date Range

// START_DATE

If {?Date_Range_Type} = "Last Full Month" then
DATESERIAL((DATEPART("YYYY",CurrentDate)), DATEPART("m",CurrentDate)-1,1)
else if {?Date_Range_Type} = "Last Full Week" then
currentdate - DayOfWeek (currentdate, crMonday)- 7
else if {?Date_Range_Type} ="Yesterday" then
currentdate-1

// END_DATE

If {?Date_Range_Type} = "Last Full Month" then
DATESERIAL((DATEPART("YYYY",CurrentDate)), DATEPART("m",CurrentDate),1)-1
else if {?Date_Range_Type} = "Last Full Week" then
currentdate - DayOfWeek (currentdate, crMonday)- 1
else if {?Date_Range_Type} ="Yesterday" then
currentdate

NOTE:
If the 1st day of the week is Sunday then replace "crMonday" with "crSunday"

3. Place the Text Box in the Report Header
Report Period: START_DATE to END_DATE
0

Commented:
I don't know how much "better" this is, but when testing for several possible values, I find Select-Case a bit easier to read.

Select {?Date Range}
Case 'Last Full Month' :
'Report Period: ' & minimum(lastfullmonth) & ' to ' & maximum(lastfullmonth)
Case 'Yesterday' :
'Report Period: ' & currentdate - 1
etc.

You could theoretically also shorten that a bit by doing something like this:

'Report Period: ' &
(
Select {?Date Range}
Case 'Last Full Month' :
minimum(lastfullmonth) & ' to ' & maximum(lastfullmonth)
Case 'Yesterday' :
currentdate - 1
etc.
)

James
0

Commented:
Oh yeah.  If you use the same basic formula in a lot of reports, you can copy it from one report and paste it into another.  By that, I mean copy the formula from the page header or whatever in one report and paste it into the page header or whatever in another report (as opposed to copying the contents of the formula in the formula editor, although you can do that too if you like).  And then make any changes that might be required for the new report.

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.