Day selection parameter in record select - Crystal Reports XI

[rguittar]

Have a request to select either weekends, weekdays, all days, or one or more specific days of the week. I can figure everything out except how to set up and have the date extract the chosen days of the week using either Crystal or Oracle PL/SQL logic.

 

[Turkbear]

If the Date is an actual Date ( or DateTime) field in the Oracle database then Crystal's WeekDay or DayOf Week functions should be usable in your record selection formula, like:
(assume Sunday is 1st day of week(The Default in Crystal), and you want Tuesday)

DayOfWeek({TableDateField}) = 3

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rguittar]

That's true, but the problem comes when I want to use more than one day of the week, for example, Monday and Thursday. Thus, the DayOfWeek #'s would be 2 and 5. It will read the 2 but not the 5 and that is the issue with pulling both dates.

 

[lbass]

The simplest solution would probably be to set up a number parameter that allows multiple values, and use dayofweek as the value, and add the day descriptions. Then let the users select the specific days.

Or you could set up two parameters, one a string parameter {?DayType} with options: all, weekend, weekdays, specific days. Then add a multiple value number parameter {?WhichDays} with prompt text like this:

If you selected DayType = "specific days", select the day or days you would like to report on; otherwise, select "N/A"

Then add day of week numbers as the values, and day descriptions to that. Add 0 as the "N/A" value, and only display the description. Then your record selection formula would be:

select {?DayType}
case "Weekend" : dayofweek({table.date}) in [1,7]
case "Weekdays" : dayofweek({table.date}) in 2 to 6
case "Specific Days" : dayofweek({table.date}) in {?WhichDays}
case "All" : dayofweek({table.date}) in 1 to 7

Whether or not they selected 0 for {?WhichDays}, it would only affect record selection if "Specific Days" was selected, but it is more logical to the user to see that option, I think.

-LB

 

[rguittar]

That worked perfectly in the record select. Is there a comparable way to make that work in the report heading to select the dates chosen? There is a date parameter where multiple dates/date ranges can be selected. If a range of 6/1/2011 - 7/10/2011 and 8/5/2011 and 8/7/2011 are chosen, doing a min/max formula would just display 6/1/2011 - 8/7/2011. I was hoping to comprise something to give me the individual dates and ranges.

Thanks.

 

[lbass]

Please see faq767-5684.

-LB

 

[rguittar]

Thanks a million lbass. Again, saved the day.