Solved

Need Crystal Date Formula Help

Posted on 2014-12-22
5
224 Views
Last Modified: 2014-12-22
Hello All...

So, I know this is something likely very simple and silly, but we're stuck.  What we're trying to do is to have a Formula Field, with something like the following:

DateAdd("m", 1, DateTime( Year({CurrentDate}), Month({CurrentDate), 1, 0,0,0 )) - 1

...such that we can have Crystal display the END of the CURRENT month (meaning "2014-12-31" if run *today*).

But, the 'CurrentDate' function, doesn't seem to work in the Formula above.  BTW...we *ALSO* need, the BEGINNING of the PRIOR 3rd MONTH.  In other words, we need a SECOND Formula, which shows "2014-09-01"...assuming *today* is "2014-12-22".

Please help!  We need to finalize these reports before the XMas break.  THANK YOU!...Mark
0
Comment
Question by:datatechcorp
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
vasto earned 500 total points
ID: 40513759
Try
DateAdd("m", 1, DateTime( Year(CurrentDate), Month(CurrentDate), 1, 0,0,0 )) - 1

This should work too:
DateAdd("m", 1, DateTime( Year(CurrentDate), Month(CurrentDate), 1 )) - 1

for the prior 3rd month try

DateAdd("m", -3, DateTime( Year(CurrentDate), Month(CurrentDate), 1 ))
0
 

Author Comment

by:datatechcorp
ID: 40513770
OMG...thank you VASTO sooooo much!  My bad on that...I'd copied in an old formula, and didn't realize...that I still had the "braces" intact...from the old field I was copying this from.  I've looked at this sideways for over an hour :-)

Again, thank you!...Mark
0
 
LVL 18

Expert Comment

by:vasto
ID: 40513829
You are welcome Mark!

BTW having the date hardcoded will work if you want always to use the report for a fixed period: last quarter, last month etc. However if you would like to use the same report for different periods you can create parameters for the beginning and the end of the period and control the default values using the software, which runs the reports. In this way you can predefine periods like last month , last quarter, last year , last week etc. and you can run the report with the predefined values or manually select any other set of values. This will make the report more customizable.

You can check this video : http://www.r-tag.com/Pages/Preview_Demo.aspx
From the menu in left Jump directly to "Parameter templates", or go to time 14:45.

This tool has a free Community edition in case it might be useful. There are plenty of other viewers too. I guess there should be some more which support parameter templates.

This approach will allow you to reduce the number of reports. You can also use predefined set of values inside the report. For example if set =1 use last month , if set =2 use last year etc. but you should handle this inside each report, while the software will handle it for all at once. Also ,internal handling will not allow you to use the report with free dates.
0
 

Author Comment

by:datatechcorp
ID: 40513885
Great tip!  Thanks Vasto!  It's very much appreciated!
0
 
LVL 18

Expert Comment

by:vasto
ID: 40513953
You are welcome!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
sort by a cross tab in crystal report 2 53
Left and Right Trim a field in Crystal Report 2 165
Calculation - Mini/Max Removed then average 8 29
Crystal Report sorting 6 16
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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