Array Help Needed, Please!

[sandiclaus]

Hi,
We are running CR Ver 10.0.0.053 on Windows XP.
I have been struggling with this code for a week now and can't seem to get it right.

My records have a detention start and a detenion end date. Given a parameter report start and end date, I am trying to use arrays to generate an average daily population by Month.

For example: Jan Feb Mar Apr May
117 111 100 94 97
instead I am getting:
519 519 519 519 520

Could someone PLEASE tell me what's wrong with my code?!
I did create a loop to make it error out so I could check my values and the dates and values for my dates, m, d, and x appear to be correct.


WhilePrintingRecords;
Global NumberVar Array MoAvg;
Local NumberVar m;
m := DateDiff('m',{?startdate},{?enddate})+1;
Redim Preserve MoAvg [m];
Local DateVar CheckMonth := {?startdate};
Local DateVar EndMonth := {?enddate};
Local NumberVar i;
Local StringVar Display;

Global NumberVar Array DailyKids;
Local DateVar CheckDate := CheckMonth;
Local DateVar EndDate := DateSerial(Year(CheckDate),Month(CheckDate)+1,1)-1;
Local NumberVar d;
d := EndDate - CheckDate +1;
Redim Preserve DailyKids[d];
Local NumberVar x;

While CheckMonth <= EndMonth
Do
(
i := m - DateDiff('m',CheckMonth, EndMonth);
While (DateSerial(Year(CheckMonth),Month(CheckMonth)+1,1)-1) - CheckDate >= 0
Do
(
x:= d - (EndDate - Checkdate);
if {@DetStart} <= CheckDate
and ({@DetEnd}>= CheckDate or {@DetEnd}=Date(0000,0,0)) then
DailyKids[x] := DailyKids[x]+1;
CheckDate := CheckDate + 1;
EndDate := DateSerial(Year(CheckDate),Month(CheckDate)+1,1)-1
);
MoAvg := Sum(DailyKids)/d;
CheckMonth := CDate(DateAdd ('m',1,CheckMonth))
);


For i := 1 to ubound(MoAvg)
Do
Display := Display & Cstr(MoAvg,0,"") & Chr(9)& Chr(9);

Display

Any help would be greatly appreciated!
Sandi

 

[lbass]

What are the contents of {@detstart} and {@detend}?

What does your data look like at the detail level? Please show a sample.

You are showing months horizontally--is the the result of a manual crosstab using conditional formulas? If so, show an example of the formulas you are using.

Also explain in what report section you are using your formula.

-LB

 

[sandiclaus]

LB-
My database has dates in text format - 20080101 for example.
I use this function to convert to date:

Function (stringvar JoltsDate)
If not IsDate(Mid(JoltsDate,5,2)&"/"&Right(JoltsDate,2)&"/"&Left(JoltsDate,4)) then
Date(0,0,0)
else
Date(ToNumber(Left(JoltsDate,4)),ToNumber(Mid(JoltsDate,5,2)),ToNumber(Right(JoltsDate,2)))

So
P190.STRTDATE = 20080315 and {@detstart}= 3/15/08

1 row of P190 table looks like:
FNBR190 SCRID190 STRDATE190 STRTIME190
18040301 2 20080315 0315

ENDDATE190 ENDTIME190
20080411 0826

I am showing months horizontally using another array formula
JAN 08 FEB 08 etc (my report can cross years)

I put the MoAvg formula in both the detail and report footer sections (report is not grouped).

Sandi

 

[sandiclaus]

I finally (after almost a week of struggling) worked it out for myself! I did switch from Crystal to Basic syntax to make it easier. I am sharing in hopes that others might find this useful.

My report output looks like this (it does line up right in the report):
Jan-08 Feb-08 Mar-08 Apr-08 May-08
ADP 117 113 112 109 108

Here is the code for the Monthly headers (does work across years):

whileprintingrecords
REM Declare all the required variables
Dim m, i as number
Dim CheckDate, EndDate as date
Dim Display as string

m = DateDiff("m",{?startdate},{?enddate})+1
Global CountMos(m) as Date
CheckDate = {?startdate}
EndDate = {?enddate}


REM Loop through the date range and increment the array
Do while CheckDate <= EndDate
i = m - DateDiff("m",CheckDate, EndDate)
CountMos(i) = CheckDate
CheckDate = CDate(DateAdd ("m",1,CheckDate))
Loop

REM Build the display string
For i = 1 to ubound(CountMos)
Display = Display & Cstr(CountMos(i),"MMM") & "-" & Cstr(CountMos(i),"yy") & Chr(9)
Next i

Formula = Chr(9) & Chr(9) & Display & Chr(9)

Here is the code for the Monthly ADP (average daily population):

whileprintingrecords
dim m, d, i, x as number
dim CheckMonth, EndMonth, CheckDate, EndDate as date
dim Display as string

CheckMonth = {?startdate}
EndMonth = {?enddate}
CheckDate = CheckMonth
EndDate = DateSerial(Year(CheckDate),Month(CheckDate)+1,1)-1
m = DateDiff("m",{?startdate},{?enddate})+1
d = EndDate - CheckDate +1

Global MoAvg(m) as number
Dim DailyKids as number

Do while CheckMonth <= EndMonth
i = m - DateDiff("m",CheckMonth, EndMonth)
Do while (DateSerial(Year(CheckMonth),Month(CheckMonth)+1,1)-1) - CheckDate >= 0
d = DateSerial(Year(CheckMonth),Month(CheckMonth)+1,1)-1 - DateSerial(Year(CheckMonth),Month(CheckMonth),1) +1
x= Day(Checkdate)
if ({@DetStart} <= CheckDate and ({@DetEnd}>= CheckDate or {@DetEnd}=#12/30/1899#)) then
DailyKids = DailyKids +1
end if
CheckDate = CheckDate + 1
Loop
MoAvg(i) = MoAvg(i) + DailyKids/d
CheckMonth = CDate(DateAdd ("m",1,CheckMonth))
DailyKids = 0
Loop

For i = 1 to ubound(MoAvg)
Display = Display & Cstr(MoAvg(i),0,"")& Chr(9)& Chr(9)
next i

Formula = "ADP" & Chr(9) & Display & Chr(9)

The only thing I haven't resolved yet is issue of if someone puts in dates other then 1st/last of start/end date but that part should be easy.

Sandi