Solved

Need Crystal Date Formula Help

Posted on 2014-12-22
5
217 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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. …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now