Skip to Content
avatar image
Former Member

Crystal Reports conditional grouping

Hi all, I'm working on a report that is grouping by month. I'm working on having the user to be able to select either to group by month or week. so here's what I have so far.

I created a groupby parameter type is "string" and values "static" and values are "week" and "month"

then i created a formula group:

______________________________

If {?GroupBy} = "Week" Then

//week:
dateadd("ww",datepart("ww",{table}, crMonday)-1,
date(year({table}),1,1)-dayofweek(date(year({table}),1,1),crMonday)+1)

Else

//month:
Right("00" & cstr(month({table}),0),2) & "/" & cstr(year({table}),0,"")

_______________________

so to me that looks good and make sense :-)

but when i try to save and close the formula, an error message comes up that highlights the whole month formula and says "A date-time is required here."

any idea what am I doing wrong here?

Thanks in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Feb 13 at 06:57 PM

    Hi Karim,

    Modify the formula to:

    if {?GroupBy} = "Week" then
    totext(dateadd("ww",datepart("ww",{Date}, crMonday)-1,
    date(year({Date}),1,1)-dayofweek(date(year({Date}),1,1),crMonday)+1))
    else 
    totext(date(year({Date}), month({Date}), 01))

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 19 at 09:28 AM

    Use sort in Report Designer, right click Group and select Change group set sort order there.

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 16 at 06:26 PM

    One more question, how can I sort ascending or descending for the group "week"? I tried crAscendingOrder but that did not work, it gives me this error: "The remaining test does not appear to be part of the formula."

    Thanks.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 20 at 07:18 PM

    Thank you Ian for the response but the group field that is in the "Record Sort Expert" is not sorting right. When I choose "Month" the sort is correct but when choosing "Week" the sort is all over the place. Also, in the Record Sort Expert, it does not allow me to remove the group field or choose to sort Asc or Desc. They are grayed out.

    I have also tried crDescendingOrder at the end of the Week formula but that gave an error message:

    The remaining text does not appear to be part of the formula.

    Add comment
    10|10000 characters needed characters exceeded