Array not properly reset 1

[JCruz063]

Hi All,
This is driving me insane... check it out:

The Scenario:
I have a crystal report that prints from a MySql Server database. The report deals with dates and times and it has 5 groups.

Group 1 groups records by month and year, and so I have a formula like this:

[tt][green]// Group1Formula[/green]
monthIndex := year({theDate}) + month({theDate});[/tt]

Group 2 groups the records by some field in the database, and it goes like this:
[tt][green]// Group2Formula[/green]
if {theField.value} = 1 then
1
else
2;[/tt]

Group 3 groups the records by start and end time (the records have a start and end time) and it looks like this:
[tt][green]// Group3Formula[/green]
{startTime} & " - " & {endTime};[/tt]

And so on with the other two groups.



The Problem:
The problem is the following: I need to know (and print) how many days worth of data I have in every week of every month. To get that, I have an array that keeps a count of the total number of distinct days in each week. Since the number of days in each week is different for every month, I have a formula in the header of Group1 that redims the array so that the number of days in each week are set to 0 for every month. Well, my array is not being reset for some reason, and the total number of days that I get is the total number of days for all the months that I have.

Am I missing something?

Here's the formula that resets the array... it's found in the header of group 1.

[tt][green]// Resetting Formula[/green]
global numbervar array arrActualDaysInWeek;
redim arrActualDaysInWeek[5];
[green]//this isn't really needed but i did it anyways[/green]
arrActualDaysInWeek[1] := 0;
arrActualDaysInWeek[2] := 0;
arrActualDaysInWeek[3] := 0;
arrActualDaysInWeek[4] := 0;
arrActualDaysInWeek[5] := 0;[/tt]

And here's the formula that keeps trak of the number of days in each week. This is found the Details section:

global numbervar totalDays;
global datevar array datesArr;
global numbervar firstDayOfWeek;
global numbervar array arrActualDaysInWeek;

global datevar array arrDatesInWeek1;
global datevar array arrDatesInWeek2;
global datevar array arrDatesInWeek3;
global datevar array arrDatesInWeek4;
global datevar array arrDatesInWeek5;

local numbervar weekIdx;
local numbervar returnVal := 0;

if {template} = 0 then
(    
    if not (({theDate} in datesArr)) then
    (
        // check if this date must be counted
        if dayofweek({theDate}, firstDayOfWeek) <= {?DaysInWeek} then
        (
            // total days in the whole month
            totalDays := totalDays + 1;
            redim preserve datesArr[totalDays];
            datesArr[totalDays] := {rundown.rundownDate};
        
            // total days in the week            
            weekIdx := WeekOfMonth({theDate}, firstDayOfWeek, {?DaysInWeek});
            arrActualDaysInWeek[weekIdx] := arrActualDaysInWeek[weekIdx] + 1;
            select weekIdx            
                case 1:
                (                    
                    redim preserve arrDatesInWeek1[arrActualDaysInWeek[weekIdx]];
                    arrDatesInWeek1[arrActualDaysInWeek[weekIdx]] := {theDate};
                )
                case 2:
                (
                    redim preserve arrDatesInWeek2[arrActualDaysInWeek[weekIdx]];
                    arrDatesInWeek2[arrActualDaysInWeek[weekIdx]] := {theDate};                    
                )
                case 3:
                (
                    redim preserve arrDatesInWeek3[arrActualDaysInWeek[weekIdx]];
                    arrDatesInWeek3[arrActualDaysInWeek[weekIdx]] := {theDate};                    
                )
                case 4:
                (
                    redim preserve arrDatesInWeek4[arrActualDaysInWeek[weekIdx]];
                    arrDatesInWeek4[arrActualDaysInWeek[weekIdx]] := {theDate};                    
                )
                case 5:
                (
                    redim preserve arrDatesInWeek5[arrActualDaysInWeek[weekIdx]];
                    arrDatesInWeek5[arrActualDaysInWeek[weekIdx]] := {theDate};
                );
        ); //if dayofweek({rundown.rundownDate}, firstDayOfWeek) <= {?DaysInWeek})
    ); //if not (({theDate} in datesArr)) then  
); //if {template} = 0 then

In the code above, WeekOfMonth is a custom function that returns the week number (from 1 to 5) that a given day falls in a month. It's bulletproof so that's not the problem. But, when I print the report, arrDatesInWeek and arrActualDaysInWeek reflect the total unique days for all months in the data. How can this be? I'm resetting these arrays in the resetting formula that's in the footer of group 1 so these values must be reset for every group.

Am I missing something?

Thanks!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[smcnulty2000]

Three things occur to me right away.

1. If you are trying to get the actual days that data occurred and not get any days that didn't have data, you could put a group between your first and second groups that groups on date, by day. There's ways to count these.

2. If you just want to count the weekdays between two dates without the weekends, you can just use this formula:

DateDiff ("d", d1, d2) -
(DateDiff ("ww", d1, d2, crSaturday) +
DateDiff ("ww", d1, d2, crSunday))

...which pulls the days, then subtracts out the weekends.

3. If neither of those is helpful because they aren't what you are looking for and you want to proceed with your formula, you are most likely dealing with an evaluation time problem. In other words, your array is not resetting because the reset is occurring in the 'before reading records' phase. Crystal is doing that part first and merrily going on, ignoring the formula for the remainder of the report run. And yes, that's going to drive you nuts if that's what's going on.

You could try plugging in WhileReadingRecords; including the semicolon, of course, at the beginning of your redim array formula and see if that makes any difference.

If that doesn't work, have a look at 'evaluation time' in the help file and/or the following link.

http://msdn.microsoft.com/library/d.../crystlmn/html/crconreportprocessingmodel.asp

Which may be somewhat enlightening. The four statements that control evaluation time are BeforeReadingRecords; WhileReadingRecords; WhilePrintingRecords; and EvaluateAfter({@formula}); and you can look at the help files on each of these.

The natural assumption is that Crystal chugs through the report in the manner in which it displays, which turns out to not be the case.

Let us know if all this advice doesn't work and maybe we'll dig some more.


Scott M.

 

[JCruz063]

Thanks Scott!

Evaluation time is indeed the problem, but I already had the 'whileprintingrecords' statement in my formulas. The problem, however, seems to be that I cannot have a variable store different values for different parts of the report. This makes sense, as a variable can only have one value at a time, and when the report is printed, that variable can only print one value all throughout, which is either 0, or the final (total) value of the variable.

Thus, instead of using a variable (i.e. arrActualDaysInWeek[]), I'm now using a formula (for each week) that returns 1 or zero, depending on the date. To get the total number of days in a given week on a given group, I simply use the Sum of the formula pertaining to that week, and send the group name as the second parameter. That gives me the total number of 1's returned by the formula on that given group.

Thanks again for your detailed reply!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.