Crystal Reports conditional grouping

[Former Member]

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.

[Former Member]

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

Ian

[abhilash_kumar]

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

[Former Member]

Thank you Abhlash, that does get rid of the error message.

[Former Member]

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.

[abhilash_kumar]

Hi Karim,

Get rid of the datatype conversion for the date fields in the code and that should fix the sort:

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

-Abhilash

[Former Member]

I assume your month format is 20180201 , 20180101.

As text this will sort correctly, whereas weeks are 1, 2, 3...11, 12,13 these will not sort correctly as text, change your week totext formula to

totext(dateadd("ww",datepart("ww",{Date}, crMonday)-1,date(year({Date}),1,1)-dayofweek(date(year({Date}),1,1),crMonday)+1), "00", 0, "")

This will format weeks a 01, 02, 03...11, 12, 13 which will sort correctly.

Ian

[Former Member]

For some reason none of the 2 formulas works.

when I used

(dateadd("ww",datepart("ww",{Date}, crMonday)-1,date(year({Date}),1,1)-dayofweek(date(year({Date}),1,1),crMonday)+1))

it says a date-time is required here. >>> after the else

and when I used

totext(dateadd("ww",datepart("ww",{Date}, crMonday)-1,date(year({Date}),1,1)-dayofweek(date(year({Date}),1,1),crMonday)+1), "00", 0, "")

it highlighted "totext" and says "A string is required here."

and my date format is M/D/YYYY.

Not sure what am I missing!!!

[abhilash_kumar]

Hi Karim,

This should hopefully work:

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

[Former Member]

Beautiful 😊, that worked. Thank you all and Thanks to everyone for the help.

[Former Member]

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.