Help with Array

[69AceCool]

Hi All,

CRXI
ODBC connection to Remedy ARS 5.1

Currently have a simulated crosstab the Date is in the group header and the HD's are detail section similiar to below:

22/09/06 0-5days 6-10days 11-30days
HD1 47 35 10
HD2 31 41 7
HD3 etc...
23/09/06
HD1 45 31 9
HD2 34 39 9
HD3 etc....

It is grouped by day, each day will have either have an increase or decrease in each of the columns. What I am trying to do is compare 22/09/06 HD1 with 23/09/06 HD1 (HD2, HD3, HD4, HD5, etc) without changing the layout or grouping. The compare would first check if there was an increase/decrease/no change and then calculate the % change. Do I need to create an array to hold the values and then compare? (The date range can be any number of days e.g 02/09/06 - 25/09/06)Any pointers greatly appreciated.

 

[satinsilhouette]

Or you could similate this using subreports.

Thanks so much!
satinsilhouette

 

[synapsevampire]

Yeah, given your requirements you're probably best served using arrays, keep in mind that crystal does not have 2 dimensional arrays, so you'll have to handle this manually that will prove purdy fugly.

Generally comparisons are side by side, hence the difficulties.

Formulas:

Report Header:
booleanvar FirstDate:=true

Then create a details formula to reset FirstDate if you're not on the first date:

Report Header:
booleanvar FirstDate;
If not(onfirstrecord)
and
{table.date} <> previous({tabledate}) then
FirstDate := false

Details
whileprintingrecords;
booleanvar FirstDate;
stringvar array MyTypes;
numbervar array Qty0-5;
numbervar array Qty6-10;
numbervar array Qty11-30;
If FirstDate then
(
redim preserve MyTypes[ubound(MyTypes)+1];
redim preserve Qty0-5[ubound(Qty0-5)+1];
redim preserve Qty6-10[ubound(Qty6-10)+1];
redim preserve Qty11-30[ubound(Qty11-30)+1];
MyTypes[ubound(MyTypes) := {table.HDtype};
Qty0-5[ubound(Qty0-5)]:= {table.qty0-5};
Qty6-10[ubound(Qty6-10)]:= {table.Qty6-10};
Qty11-30[ubound(Qty11-30)]:= {table.Qty11-30}
);

Then you can create another formula for use in the details during the second group (second date, as in):

whileprintingrecords;
booleanvar FirstDate;
stringvar array MyTypes;
numbervar array Qty0-5;
numbervar counter;
numbervar Output;
If not(FirstDate) then
(
for counter := 1 to ubound(MyTypes) do(
if {table.HDType}= MyTypes[counter] then
Output:= {table.Qty0-5} - Qty0-5[counter]
);
"Difference between this day and the last for 0-5 = " & Output

Or store both values in even more arrays and do it all in the Report footer.

Note that you show what your data looks like, but decide it unimportant to show what you want as output, and where, rather just describing some properties of the math that will occur...

Anyway, the above theory should show you what needs to be done, it's basic array handling.

-k

 

[69AceCool]

thanks synapse, sorry I forgot to show expected output (I've read so many of your posts as well, d'oh!)

Ok I have it working except that it is only comparing 1 day correctly i.e comparing Date2 to Date1 is fine, however Date3 is comparing to Date1 instead of Date2. Howe do I change the array so that Date3 is compared to Date2, Date4 is compared to Date3, Date5 to Date4, etc?

For reference expected output is:

22/09/06 0-5d %+/- 6-10d 11-30d
HD1 47 35 10
HD2 31 41 7
HD3 etc...
23/09/06
HD1 45 -4.3% 31 -11.4% 9 -10%
HD2 34 9.7% 39 -4.9% 9 28.6%
HD3 etc...
24/09/09
HD1 41 -8.9% 29 -6.5% 9 0
HD2 36 5.9% 39 0 8 -11.1
HD3 etc...

 

[synapsevampire]

Ahhh, right, I just saw that you can have any number of dates.

So replace the current days array values with the previous days value at display time, as in:

whileprintingrecords;
booleanvar FirstDate;
stringvar array MyTypes;
numbervar array Qty0-5;
numbervar counter;
numbervar Output;
If not(FirstDate) then
(
for counter := 1 to ubound(MyTypes) do(
if {table.HDType}= MyTypes[counter] then
Output:= {table.Qty0-5} - Qty0-5[counter];
Qty0-5[counter]:={table.Qty0-5}
);
"Difference between this day and the last for 0-5 = " & Output

So now you have the current value squirreled away in the variable.

The gotcha here is if you have differing HDs between the days, meaning some do not exist for all days.

If that's the case, then you might use another array to maintain whether it was updated by the current date...

If they're all within each date then it should be fine, if not, it's going to get a bit fuglier

-k

 

[69AceCool]

Thanks Synapse,

when I add the line:

Qty0-5[counter]:={table.Qty0-5}

to the details formula for Qty0-5 it appears to move the values down 1 i.e date2 compared to date1 no longer works, date3 to date2 are values are being compared to the wrong HDtype. I saw your gotcha there, however each day always has 5 HDtypes, so can't figure out what's going wrong. Hope that makes sense...?

 

[synapsevampire]

Please show the entire formula you used, and state which section you are using it in.

You also might need to put a

evaluateafter {@MyArrayCollectionFormula};

instead of

whileprintingrecords;

to make sure that they are populated in the right order.

-k

 

[69AceCool]

Here it is:

//details section
evaluateafter({@First Array});

booleanvar FirstDate;

stringvar array MyTypes;
numbervar array D05;
numbervar counter;
stringvar Output;

If not (FirstDate) then

(

for counter := 1 to ubound(MyTypes) do
(
if {STD_SR_ServiceRequest.Category}= MyTypes[counter] then
Output:= totext({STD_SR_ServiceRequest.0 - 5 Days}) + " " + totext(D05[counter]);
D05[counter]:={STD_SR_ServiceRequest.0 - 5 Days}

);
"Diff 0-5 = " & Output;
)

I've got the Output set that way so that I can see what is in the array.

Here is the output:
Category 0 - 5 Days
24/09/06
HD1 42
HD2 24
HD3 26
HD4 75
HD5 39
25/09/06
HD1 47 Diff 0-5 = 47 42
HD2 18 Diff 0-5 = 18 47
HD3 26 Diff 0-5 = 26 18
HD4 75 Diff 0-5 = 75 26
HD5 34 Diff 0-5 = 34 75
26/09/06
HD1 34 Diff 0-5 = 34 34
HD2 16 Diff 0-5 = 16 34
HD3 19 Diff 0-5 = 19 16
HD4 64 Diff 0-5 = 64 19
HD5 29 Diff 0-5 = 29 64