Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dates

Posted on 2011-09-08
5
Medium Priority
?
247 Views
Last Modified: 2012-05-12
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
Comment
Question by:halifaxman
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36501317
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 36502700
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
 

Expert Comment

by:RaoVP
ID: 36508320
Follow the Below steps for your requirement.
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
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 36509498
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
 
LVL 35

Expert Comment

by:James0628
ID: 36509513
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses
Course of the Month11 days, 20 hours left to enroll

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question