Print Page | Close Window

Formula to detemine month

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7605
Printed Date: 18 May 2024 at 4:22pm


Topic: Formula to detemine month
Posted By: AndreasI
Subject: Formula to detemine month
Date Posted: 06 Sep 2009 at 11:06pm
I have been asked to develop some reports grouped by month. The field I am using (View_Incidents.Open_Date) to base the month on is in the datetime format of DD MON YYYY HH:MM:SS, with an example string being 01 Apr 2009 01:00:59.
 
I tried using an if-then-else formula to attribute the month string based on between values, but that failed to return all months. Since I have yet to select a range I know the strings exist, despite not being handled by my (probably wrong) formula.
 
So now I need a little help with writing one of those nifty little string search formulae I've seen people on here use...one that looks at the MON part only, then depending on that string, attributes the month for grouping.   Can't wait for RD210 doing it in October :)
 
Thanks in advance.


Replies:
Posted By: Jyothi Yepuri
Date Posted: 06 Sep 2009 at 11:45pm
Create a formula as below, group by it

MonthName(MONTH(CDATETIME(View_Incidents.Open_Date)))

All data is from current year?


HTH,
Jyothi

Posted By: AndreasI
Date Posted: 07 Sep 2009 at 4:32pm
Thankyou! I presume its a version issue but I needed to amend the syntax slightly:
 
MonthName(MONTH(CDATETIME({View_Incidents.Open_Date})))
 
But the formula worked a charm and I was able to group the first report I tried it on by month.
 
Some reports work on just this years data (which is the case for the report I needed this formula for) and others being developed will query the entire data set.
 
Why do you ask?
 

Posted By: Jyothi Yepuri
Date Posted: 07 Sep 2009 at 4:41pm
I don't think its version issue, you should add braces for database fields in crystal which i haven't added in my example :)

about the data in same year, If you group by month and have incidents opened in 2008, 2009 then you will not get accurate data.

in that case you should group by year also. something like this

2009
Jan
Feb
...
2008
Jan
Feb
...

Jyothi

Posted By: AndreasI
Date Posted: 07 Sep 2009 at 6:22pm
I assumed that would be the case. How would I code for year?
 
 
 

Posted By: Jyothi Yepuri
Date Posted: 07 Sep 2009 at 6:29pm
YEAR(CDATETIME({View_Incidents.Open_Date}))
try grouping by this formula

"why MONTH works on a text string date"?

Month, year both funtions require Date field
where as MonthName requires number in range 1 to 12

Jyothi

Posted By: AndreasI
Date Posted: 07 Sep 2009 at 7:05pm
Wow, that was quick!
 
I got around the problem using 2 formula - one to convert the date:
Date({View_Incidents.Open Date})
and then one to extract the year:
Year ( mailto:%7b@ConvertDate - {@ConvertDate })
 
but i like your approach better.
 
Thanks again :)

Posted By: AndreasI
Date Posted: 07 Sep 2009 at 7:21pm
Grouping by Year then Month loses my Year headings between the months, but thats hardly a technical question and the grouping works well nonetheless :)
 
Whats my best approach for summarising this information to count the number of jobs by month/year (e.g number of jobs in Feb 2008 etc)? I tried some basic summaries but have hit the books again :)
Thanks

Posted By: DBlank
Date Posted: 07 Sep 2009 at 7:22pm
As another solution
convert he field as you were to a datetime
CDATETIME({View_Incidents.Open_Date})
group on that formula field and alter the grouping type to "for each month" which also keeps it per month/year.
It will also keep it in the correct sequential order unlike converting it to the monthname whcih makes it alpha order.
If you want to visually make:
g1: Year
g2: Month
Group on the formula field twice and make it per year first and per month secondly

Posted By: AndreasI
Date Posted: 07 Sep 2009 at 7:27pm

Brilliant! These solutions are exactly what I needed, thanks gentlemen!

Posted By: DBlank
Date Posted: 07 Sep 2009 at 7:30pm

If you group once per month (right click on the field grou and you can chnge the look to month-year).

FOr the SUmmary it depends on your data. If you havea field per row that you need to sum:
Insert a SUmmary using that number field as a SUM reset on group footer 1.
it will be on the GF1 and show you a sum per month.
If each row is one job then insert a Summary as a Count of the Primary key of your job table reset on group 1.

Posted By: DBlank
Date Posted: 07 Sep 2009 at 7:47pm
Note that you can use the insert SUmmary as a Count or Sum using any of the group options that were given earlier. You just have to place them ont he correct footer.
If you used a double group
Year
Month
Having the SUmmary reset at group GF1 gives you the total per year, on GF2 gives you per month (report footer for all records).
Didn't want to give the impression that you cannot get the numbers by using options Jyothi gave you earlier.


Print Page | Close Window