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

x
?
Solved

Need Crystal Date Formula Help

Posted on 2014-12-22
5
Medium Priority
?
240 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 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2

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