Skip to Content
0

Crystal Reports conditional grouping

Feb 13 at 06:56 PM

81

avatar image
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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Abhilash Kumar
Feb 13 at 06:57 PM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

0
Ian Waterman Feb 19 at 09:28 AM
1

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

Ian

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 16 at 06:26 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 20 at 07:18 PM
0

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.

Show 5 Share
10 |10000 characters needed characters left characters exceeded

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

1

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

1
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!!!

0

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))
1
Former Member

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

0