Hello all,
If you have a requirement for a Crystal Report to allow the end user to change the grouping interval on a date or datetime field (to intervals like Monthly, Weekly, Quarterly...) then the following 3 steps should help you get started.
The output of the actual grouping is string type and not a date due to the nature of the groupings.
1) Create a new Parameter / Prompt (string type) that has the following values:
Daily
Weekly
Monthly
Quarterly
Semi-Annually
Yearly
2) Create a new Formula and copy and paste the syntax from below into the new formula. Change the datetime field and the parameter / prompt in the formula as per the instructions in the syntax.
__________________________
datetimevar mdf:= {Orders.Order Date};
// replace the field above with the datetime field from your database
stringvar mp:= {?dwmy};
// replace the parameter name with your parameter / prompt
datevar yearstart := date( Year(mdf), 01, 01);
numbervar weekofyear:= truncate((mdf - yearstart + dayofweek(yearstart) - 1) / 7) + 1;
if mp = "Daily"
then totext(mdf,"yyyy/MM/dd")
else
if mp = "Monthly"
then totext(mdf, "yyyy/MM")
else
if mp = "Yearly"
then totext(mdf, "yyyy")
else
if mp = "Weekly"
then
if weekofyear<10
then totext(year(mdf),0,"") + ", week " + "0" + totext(weekofyear,0,"")
else totext(year(mdf),0,"") + ", week " + totext(weekofyear,0,"")
else
if mp = "Quarterly"
and month(mdf) in [1,2,3] then totext(mdf, "yyyy") + ": 1st Quarter" else
if mp = "Quarterly"
and month(mdf) in [4,5,6] then totext(mdf, "yyyy") + ": 2nd Quarter" else
if mp = "Quarterly"
and month(mdf) in [7,8,9] then totext(mdf, "yyyy") + ": 3rd Quarter" else
if mp = "Quarterly"
and month(mdf) in [10,11,12] then totext(mdf, "yyyy") + ": 4th Quarter"
else
if mp = "Semi-Annually" then
if month(mdf) in 1 to 6 then totext(mdf, "yyyy") + ": 1st Half" else
if month(mdf) in 7 to 12 then totext(mdf, "yyyy") + ": 2nd Half"
__________________________
3) Create a Group on the new formula.
Please note that this is not a GROUP BY which is passed to the database server. This will still bring all details records back. In order to do a GROUP BY on a date which is passed to the server you'll have to write a Command object instead of using tables and use database relevant functions that will parse out the various date pieces.
If there's enough interest in creating a Command object using parameterized date groupings then perhaps I can create another blog on this specific topic. In fact, if you have any ideas for a How To in Crystal Reports design, please let me know by replying to this blog.
