Repeating pattern

[Phillipg]

I have a report that displays the "Date", a count of "Refused" and a count of all "case numbers" for that particular day. I have a group (group:1) that is of the date the cases were entered. The Detail section has other data from that case in it, but is hidden. The group header is also hidden, but with the "Group #1:Name" in the group:1 footer along with that, the count of "Refused" from a field named "Status" and another count of all case numbers for that date. I have a formula that will produce a repeating pattern of the letters "A", "B", "C" in the group footer. this is the formula I have to do that;

if remainder(groupnumber,3) in 1 to 1 
then "B Shift" else
if remainder(groupnumber,3) in 1 to 2 
then "C Shift" else "A Shift"

What I would like to do is to set a starting date for "A" and place the corresponding "A, B or C" in the group footer depending on the the date displayed in the footer. I do select a range of dates to print in the report by use of a selection paremeter. At present, I have to go back through a calendar to find which shift ("A", "B" or "C&quot worked on the starting date of the report and change the formula as needed. Is there some way I can tell Crystal to, say, start the yaer off with "B" and calculate which letter of the pattern to place on the first date on the report? My report looks as bellow;

02/01/04    5    20    B
02/02/04    8    36    C
02/03/04   11    29    A
02/04/04    4    29    B
02/05/04    6    23    C
02/06/04    8    27    A
02/07/04    7    19    B
02/08/04    9    20    C
and so on

btw; Crystal Reports 9.2
and Crystal 8.5,
database; LAN, ODBC (RDO)

any help will be appreciated.


Phillipg

 

[lbass]

If you are always working with consecutive dates then the following would work:

numbervar dayofyear := datediff("d",Date(year({table.date}), 01, 01), {table.date});

if remainder(dayofyear,3) = 1
then "B Shift" else
if remainder(dayofyear,3) in 1 to 2
then "C Shift" else "A Shift"

If dates are not always consecutive, then you could use a record select based on a range starting with January 1, and then use a date range parameter {?date} to display the records you wish by going to format->section->group header/details/group footer->suppress->x+2 and entering:

not({table.date} in {?date})

Then use your original formula:

if remainder(groupnumber,3) in 1 to 1
then "B Shift" else
if remainder(groupnumber,3) in 1 to 2
then "C Shift" else "A Shift"

The groupnumber will count the suppressed sections, with January 1 being groupnumber 1.

-LB

 

[IdoMillet]

Another option is to create a table with a record for each day of the year(s) and a column specifying the "shift" of that day. Then, simply join that table to your data based on {date}.

This is known as a "Time Dimension" in data warehouses and is useful for a variety of reasons. For example, would you like to see dates even if they had no activity? Or perhaps you'd like to flag certain dates as holidays or special events...

I believe the easiest approach is to use Excel to increment a fill date range operation by 1 day. Then set the first shift and fill the rest with a simple formula.
Then, copy and paste into your database.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com