Solved

Crystal Reports 2011 (need help with date formula)

Posted on 2012-03-21
9
444 Views
Last Modified: 2012-11-30
I want to create a formula that will do the following:

If the data in my database has a date = to current month then name of month (i.e. March)

If the data in my database has a date = previous month then name of month (i.e. Feb)

And so on so that I can build a rolling 13 month view that the user will get without having to enter any parameter..
0
Comment
Question by:wshcraft70
  • 5
  • 3
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37749350
I assume you are trying to get the previous 13 months so for today you want Mar 1 2011 and later

{yourDateField} >= Date(Year(CurrentDate)-1, Month(CurrentDate),1)

mlmcc
0
 
LVL 18

Expert Comment

by:vasto
ID: 37749360
MonthName (Month ({Your Date Field}))
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37749361
Actually rolling..  Current plus previous 12 so I've got a rolling 13 month display from current date..

So how would I do that since my year will change to 2011 since we're currently only in March?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 37749381
If you mean you need Feb 1 2011 then

{yourDateField} >= DateSerial(Year(CurrentDate)-1, Month(CurrentDate)-1,1)


If you need for today 21 Feb, 2011

{yourDateField}  >= DateAdd('m',-13,CurrentDate)

mlmcc
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37749417
I'm thinking I'm going to need some sort of IF statement somewhere but....

So if user opens report and the MONTH/YEAR is AUGUST 2012 for instance then I would want:

AUGUST 2012
JULY 2012
JUNE 2012
MAY 2012
APRIL 2012
MARCH 2012
FEB 2012
JAN 2012
DEC 2011  ****
NOV 2011
OCT 2011
SEPT 2011
AUG 2011

I've created a section for each month in the Group Footer as I have several more formuals to calculate..

What I don't want to user to have to do is enter any dates..  I want the system to do it's thing..  But what I don't know how to do is how to create a formula that will take into account the previous year when applicable to that I have a clean 13 months consecutively..  Am I over complicating this again?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37749595
Have you tried any of my formulas in the report select expert?

Is the problem selecting the data or in having the labels correct?

mlmcc
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37749659
The problem is me..  haha!!

But I've managed to get the labels working using your suggestions above..  

I was hoping I was going to be able to work out the next step with that but turns out I'm a bit OFF..    Am happy to SCORE and put this out for another solution..

Now what I'm trying to fix is the FORMULA named Current Date so I can return the data from my database where FIELD {dates} is = to current month else 0

I've tried this just as a PURE GUESS and nothing more..  There is CLEARLY something wrong with it..  Thoughts?

if Month({NC_Call_Report_Stats.Dates}) = (Month(DateAdd ("m", 0, CurrentDate))) and  {NC_Call_Report_Stats.GroupID} = {?Group}
then ({NC_Call_Report_Stats.BaseID})
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37749735
I think I'm on to something..  

if Month({NC_Call_Report_Stats.Dates}) = Month(DateAdd ("m", -1, CurrentDate)) and  
year({NC_Call_Report_Stats.Dates}) = Year(DateAdd ("y", 0, Currentdate))
and {NC_Call_Report_Stats.GroupID} = {?Group}
then ({NC_Call_Report_Stats.BaseID})
0
 
LVL 1

Author Closing Comment

by:wshcraft70
ID: 37749740
Thank you for everything... You are AWESOME!!!  Thanks for your patience with me..
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
Formatting a Crystal Reports Report 9 94
Join issues with Crystal Report 10 74
Crystal  - Shared vairable capture wile printing 1 44
Sum Before Standard Deviation 2 20
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…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

840 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