Array/Formula Help

[Former Member]

I have crystal reports XI. I found a formula online and modified it to fit my companys holidays each year. This is the formula:

numberVar yr := {?Year}; // The year for which you need Holidays

dateVar array holidays;

redim holidays [10];

holidays [1] := CDate (yr, 1, 1); // New Years day

holidays [3] := CDate (DateAdd ("d", 1 - DayOfWeek (CDate (yr, 5, 31), crMonday), CDate (yr, 5, 31) ) );

// Memorial Day (last Mon in May)

holidays [4] := CDate (yr, 7, 4); // July 4 (Independence Day)

holidays [5] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 9, 1) , crTuesday), CDate (yr, 9, 1) ) );

// Labor Day (first Mon in Sept)

holidays [6] := Date ( yr, 11 , 3) - DayOfWeek ( Date ( yr , 11 , 3) ) + 26;

// Thanksgiving (4th Thurs. in Nov)

holidays [7] := CDate (yr, 12, 25); // Merry Christmas

holidays [8] := Date ( yr, 01 , 3) - DayOfWeek ( Date ( yr, 01, 3) ) + 23;//MLK Day

holidays [9] := Date ( yr, 11 , 3) - DayOfWeek ( Date ( yr, 11, 3) ) + 27; //day after thanksgiving

holidays [10] := cdate (yr, 12, 26); //day after christmas

I am trying to count the number of tickets we had open when the ticket was opened on the dates indicated in the array above. I have another formula that converts a field to a string: cstr (cdate({TASKS.OPENDATE}), "MM/dd/yyyy"). What I want to do is create a formula that says "If @dtconv = @holidays then "Holidays" and group by that forumla so all holidays are grouped together so I can count them based on the grouping. What is happening is that it is partial working. but it is only looking at the last row of the holidas formula bove and comparing the dates based on that, the rest are left ungrouped and added to the report. I just want Holidays group using the formula above so I can count them. Is there a different want of comparing them? Thanks

Ralph

[Former Member · Accepted solution]

1. Why is element 2 not populated?

2. Every element in the array is of type date, then why are you converting {TASKS.OPENDATE} to cstr?

3. Do not convert {TASKS.OPENDATE} to cstr, rather leave it as a date field.

cdate({TASKS.OPENDATE})

4. Change your second formula to this:

dateVar array holidays;

If @dtconv IN holidays then 'Holidays' else ' Working Days';

5. Group on this second formula.

6. Do a count on ticket number by the above group and you should get the number of tickets opened during holidays.

[Former Member]

It was probably mis misinterpretation. I added the formula into the holiday array formula and it worked like a champ.

Ralph

[Former Member]

I did what you suggested, but it is now putting everydate under Working Days so it does not appear it is comparing to the holiday arrary anything I can do to troubleshoot what it is doing? Thanks

Ralph

[Former Member]

The only issue I can think of would be TASKS.OPENDATE. What is the data type and display

cdate(TASKS.OPENDATE) to ensure tha it is indeed being converted properly.