Skip to Content

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.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member
    Would using a case select statement help clean up the length of the formula? If not, what situations do you suggest using/not using case select over the classic if/then statement?
    (0) 
    1. Jamie Wiseman Post author
      hey James, thanks the good question. i chose an If Then Else method for this sample based on there being nested If statements and also multiple conditions on some of the If statements (Weekly, Quarterly, Semi-Annually). it was a personal choice where i find i can write an If Then Else faster where these conditions exist.

      someone out there could definitely write something that was much cleaner and shorter than what i posted if they used a Case statement.

      (0) 
      1. Former Member
        Thanks for the input!

        I’ve used case statements for basic formulas that reference string parameters, but haven’t had the opportunity to use it with more complex situations.

        Its good to know that just because an option may be cleaner and shorter, it may not always be the preferred option…

        Thanks again!

        (0) 

Leave a Reply