arrays in formulas in groups

[addy80]

I am having this problem with CR. Could you kindly take a sec to help me?
I have a report which is subdivided into groups. each grp is then divided into a dynamic number of groups. let's call the grp higher in hierarchy gp1. i have a formula which populates an array in the gp1 header. So, each grp1 will have their own array. take for eg the array in the first gp1 has elements 1,2,4 filled up and the rest of the elements are all zero.then the second grp1 is filled letsay element 1,2,3. when both groups are displayed the array seems to be overwritten in the 2nd grp. the array did not seem to be reset at the beginning of the group. when i add in an initialisation at the start of the array filling formula, then the results are weird too. tyhe first grp displays all zeros in the array, the 2nd array is correct. and if i have more than 2 groups of grp1, then all the odd grp1 will be all zero and the even grp1 s will have the correct results. can u pls give an insight into this anomaly? your help will be much appreciated.

 

[Ngolem]

I think you need to explain your problem with a simple example...

You cannot Group on an array as such...however you can certainly create arrays within groups....Perhaps it is in the reset of the arrays where you are running into problems Jim

JimBroadbent@Hotmail.com

 

[addy80]

thanks for replying jim. congrats on your best supporting actor oscar.
now back to the problem
if there are any CR experts out there, pls help me!

i have a few tables in a dataset that i report from. namely, rebates, exception and serial. the report is divided into groups based on rebate.id. And in the group header i put a formula which populates an array,TOSarray.I am using basic syntax. The formula is as follows:
------------------------------------------------------------
evaluateafter({@RDarr})
global TOSarray({Rebate.RebatePeriod} + 2) as number
global RDarr() as datetime
local i as number
local x as number
local ret as number

//here i reset the array at the start of the formula
for i=1 to {Rebate.RebatePeriod}+2
TOSarray(i) = 0
next i

for i=1 to {Rebate.RebatePeriod}
if {Exception.ExceptionType} >= "3" then

//this is my if condition; if that condition is true
//then insert into the TOSarray

if RDarr(i) >= {Exception.Value1} and RDarr(i)<=
{Exception.Value2} or RDarr(i) >={Exception.Value3}
and RDarr(i) <= {Exception.Value4} or RDarr(i) >=
{Exception.Value5} and RDarr(i) <=
{Exception.Value6} then

TOSarray(i) = -{Rebate.RebateValue}

else

end if
end if

next i

formula = 0
------------------------------------------------------------
so each group has their own array. i will display the arrays for each group. but the reults are not as expected. somehow, the odd groups(group 1,3,5) are all reset ot zero while the even groups have the correct results.

If i remove the code for the resetting of the array, the array is never initialised when it is used in the next group. and old values are overwritten, so the result is a jumbled array with values from the previous groups.

I tried to return an array element at the end of the formula by adding these lines:

ret = TOSarray(2)
formula = ret

so the second element shld be printed out. but, from what i can see, the result printed for each group turned out to be the 2nd element of the previous group's array values.

I'll give u an eg of the resetting problem

Example:
grp1 array shld be 0 15 15 0 0 15 0 0
grp2 array shld be 0 20 0 20 0 0 0 0
grp3 array shld be 0 5 5 0 0 0 0 0

but with the reset the results are: (in the same order)
0 0 0 0 0 0 0 0
0 20 0 20 0 0 0 0
0 0 0 0 0 0 0 0

withtout the reset the results are:
0 15 15 20 0 15 0 0
0 20 15 20 0 15 0 0
0 5 5 20 0 0 15 0 0

so it seems like the old arrays were used and not by default init to all zeros. but if i init, then the arrays are init and no values inserted.

your help is much appreciated.

 

[Ngolem]

Well...thank you for the notice of my Oscar...acting is a nice sideline ...some might say my advice here is Oscar material ... LOL.

Anyways, let's look at your problem

first of all there is nothing inherently wrong with your formula...it is just that it doesn't work for odd numbers...the even values of rebate.id seem to work fine.

Although I would question the placement of this test

**************************
for i=1 to {Rebate.RebatePeriod}
if {Exception.ExceptionType} >= &quot;3&quot; then
***************************

this formula doesn't work on more than one record so you are repeatedly testing the same value of {Exception.ExceptionType}

also

I would question this If condition

**********************************************
if RDarr(i) >= {Exception.Value1} and RDarr(i)<=
{Exception.Value2} or RDarr(i) >={Exception.Value3}
and RDarr(i) <= {Exception.Value4} or RDarr(i) >=
{Exception.Value5} and RDarr(i) <=
{Exception.Value6} then
**********************************************

should it perhaps be this?

**********************************************
if (RDarr(i) >= {Exception.Value1} and RDarr(i)<=
{Exception.Value2}) or
(RDarr(i) >={Exception.Value3}and RDarr(i) <=
{Exception.Value4}) or
(RDarr(i) >= {Exception.Value5} and RDarr(i) <=
{Exception.Value6}) then
**********************************************

Note the brackets around the &quot;and&quot; pairs...maybe this is right the way you had it...it is just an observation.

Failing the problem being there:

Is it possible that you have a null value in your data when the records of rebate.id are odd??

A null value in the exception table would kill the formula at that point.....but the reset of the array having already been performed would have been completed....giving you a complete set of zeros in your data...

well...Nicole K. calls {sigh} I must go I hope this helps you.


Jim

JimBroadbent@Hotmail.com

 

[meg25]

I have the same problem too. I have my formula in the group header. The formula initialises, then check the tables and then does the necessary calculation and store the results in arrays.

I have tested it, and I do believe it can access the second or more records. However, it just doesn't work.

Can any expert gives us an insight into how the sequence of the grouping actually happens? like what is executed when.

 

[addy80]

hope i am not interrupting your interlude with nicole, jim. at least tom is outta the way

but thanks for the advice. i tried correcting the appropriate sections of code but the zeros still maintained. i don't quite agree with the part about how u can only access one record in the formula. i have proved from my results that more than one record can be accessed under that condition:

if {Exception.ExceptionType} >= &quot;3&quot; then
//do stuff

I also added the missing &quot&quot; as you had recommended. still no help.
The fields that i am accessing are never null. They always have a value in them. so it can't be that.

I think the problem lies in the fact that the array in the formula seems to be shared amongst the groups. it can't initialise at the start and remain 'faithful' to that group. I just want the array to be init to zero at the start of that formula, fill it up, then display it for that group.And so on for subsequent groups.init,fill,throw away,init,fill,throw away. but CR doesn't throw it away when i don't init it. When i do init it, it sets the thing to zero alternatingly.
so i can't get the problem. The flow seems correct.

My situation:
------------------------------------------------------------

group1 header: TOSarray formula(fills TOSarray)
group2 header: (nothing in here)
details: (nothing in here)
group2 footer: display TOSarray
group1 footer: (nothing in here)
------------------------------------------------------------
group2 is grouped by serial.number which allows me to scroll though every array element to display given that the array element has index serial.number.

so by group1 footer, all the data structures and variables ought to have been removed by the time it goes into group2 am i right? why does the data persist?
please help

so sorry for taking up your precious acting breaks...

 

[Ngolem]

Well I'm back....those love scenes take a lot out ya'....the director said &quot;take five!&quot; ... Oh that was 5 minutes...ooopps!

Anyway...to get back to your problem

&quot; i don't quite agree with the part about how u can only access one record in the formula. i have proved from my results that more than one record can be accessed under that condition:&quot;

No, I disagree!...when you are working within a formula you are standing still, so-to-speak, on one record for the duration of that formula.

I think there might be a problem with {@RDarr}

Where is this formula placed?? It should be placed in the same header line for it to work the way you have it planned in your other formula.

evaluateafter({@RDarr})...this only evaluates @RDarr first if @RDarr is in the same section...in this case the Group Header. If this formula is placed anywhere else you are not getting the effect you are hoping for.

Also let us see what this formula does...show me @RDarr

Ahhh...the &quot;5&quot; is over...the Director calls....something about retakes {sigh}...work...work...work





Jim

JimBroadbent@Hotmail.com

 

[addy80]

hey jim, wondering if they actually pay you during these five minute &quot;breaks&quot;...

Well, back to what you do best...

I think i agree with you on the fact that the formula can only access one record at a time. So, i guess my formula only does access one record. But that still works fine for me, because there will be no situation that elements will be overwritten within a group. The data does not permit that.

Let me show you the layout of the report:

------------------------------------------------------------

group1 header: @TOSarray (fills TOSarray), @RDarr
group2 header: (nothing in here)
details: (nothing in here)
group2 footer: @displayTOSarray, @displayRDarr
group1 footer: (nothing in here)
------------------------------------------------------------

and this is the contents of @RDarr:
------------------------------------------------------------
global RDarr({Rebate.RebatePeriod}+2) as datetime
local i as number
local ret as datetime

for i=1 to {Rebate.RebatePeriod}+2
RDarr(i) = dateadd(&quot;m&quot;,i-1,{Rebate.StartDate})

next i
ret = RDarr(1)
formula = ret
------------------------------------------------------------

From my results, there is no problem with @RDarr.
because every element in RDarr is filled with a new value in each group. It is filled with a date and never a zero.

@RDarr works fine. @TOSarray doesn't. The resuts from the first group are carried forth into the second group. So, the subsequent groups use old arrays. But as i said, when i try to initialise the array by putting a @reset1 in the group1 footer as such:

------------------------------------------------------------

group1 header: @TOSarray (fills TOSarray), @RDarr
group2 header: (nothing in here)
details: (nothing in here)
group2 footer: @displayTOSarray, @displayRDarr
group1 footer: @reset1
------------------------------------------------------------

where @reset1 is the following:
------------------------------------------------------------
global TOSarray({Rebate.RebatePeriod} + 2) as number
local i as number

for i =1 to{Rebate.RebatePeriod}+2
TOSarray(i) = 0
next i
formula = 0
------------------------------------------------------------

then all the array outputs become zero. so by right, in the footer the array should be initialised.

So, i gather there is no problem with the filling of the array as without the reset, the values just overlap.

These are the results i expect:
TOSarray for grp1 0 15 15 0 0 15 0 0
TOSarray for grp2 0 20 0 20 0 0 0 0

But these are the results that i get:
TOSarray for grp1 0 15 15 20 0 15 0 0
TOSarray for grp2 0 20 15 20 0 15 0 0

So the array seems to be overwritten argh!!!!

So i know there is a prob with the reset. if i can get the reset to work then this job is over... So, any ideas on where to put the reset?

 

[Ngolem]

The &quot;five Minute breaks&quot; just allow me to catch my breath, thanks

quick question...What Are you grouping on in Groups 1 & 2

let us try this:

break up group1 into 2 sections

********************************************
in the first section put @reset1 and @RDarr
AND ADD TO THE FORMULA THE FUNCTION &quot;WhilePrintingRecords&quot;
to ALL of the formulas...make it the FIRST line of each formula
********************************************
in the second section section place @TOSarray but strip out the reset and the evaluateafter() as it is not necessary anymore.


The more I think about it the more I think the missing &quot;WhilePrintingRecords&quot; from all formulas is the problem. Without it Crystal decides when to do things

Jim

JimBroadbent@Hotmail.com

 

[addy80]

thanks once again for helping jim

i did what u said. i added the 'whileprintingrecords' to every formula and it didn't work either. in fact, when i tried to display the @RDarray, nothing came out. so i guess it's not 'whileprintingrecords'. i split up group1 into 2 sections too and took out the evaluateafter(@RDarr) in the @TOSarray formula. It didn't work. when i display the @TOSarray all the zeroes came out.

When i take out the 'whileprintingrecords' the zeroes appear again(i hate those zeroes, seriously)

i know u split up the group one in 2 sections to force the order of how the formulas are evaluated right?

Ok, u asked how it was grouped.
Basically i have 2 arrays: RDarr and TOSarray
i have 3 tables: exception, rebate and serial

i insert rows into the serial table based on a field in the rebate table: {rebate.period}
so i will have a serial table with {rebate.period} rows in it.
I first group the report by {rebate.ID} so that each group contains information for one rebate.
Then i group again on that first group by {serial.number}.

So let's take an example:
assume:

{rebate.period} = 3
thus, there will be 3 rows in the serial table.so, 3 values of {serial.number}
there are 2 rebate IDs
----------------------------------------------
grp rebate ID 1
grp 1
grp 2
grp 3

grp rebate ID 2
grp1
grp2
grp3

So i can have 3 groups within a group. The reason why i want these 3 grps is so that i can display the array element whose index corresponds to that {serial.number}
So, every subgrp allows me to display a value in the precalculated TOSarray.

ok, back to the reset. if i take out the reset formula in the grp1 header(section 1), then i get that overlapping array thing

Oh, and i also tried to use
redim preserve TOSarray({rebate.period}+2)
in my @TOSarray formula

and in my reset formula i did
redim TOSarray(1)

but much to my disappointment it didn't work as well.

Sigh, this is a tough cookie to crack, jim.

 

[Ngolem]

I don't seem to be getting the complete picture here

group1 header: @TOSarray (fills TOSarray), @RDarr
group2 header: (nothing in here)
details: (nothing in here)
group2 footer: @displayTOSarray, @displayRDarr
group1 footer: (nothing in here)


Group1 is based on {rebate.ID}

Group2 based on {rebate.period} or {serial.number}?

if it is based on {rebate.period} then you won't get the 3 serial numbers...only the last one...if it is {serial.number} then there is no reference to {rebate.period}

Perhaps you have positioned the display formulas wrong and they should go into the detail section if group 2 is based on Rebate.Period.

I see no reference to {serial.number}? so this must take place in the display formulas (@displayTOSarray, @displayRDarr)

BTW...the Whileprintingrecords...should make things better not worse.

Yes the splitting of sections is done so to force the order of operation....much more reliable

Jim

JimBroadbent@Hotmail.com

 

[addy80]

yes you are correct for the group1. but, group2 is grouped by {serial.number}. {serial.number} is inserted by me into a serial table. I used VB to insert it in in my vb code. like this:
------------------------------------------------------------
For i = 1 To 24
row = ds.Tables(&quot;serial&quot.NewRow()
row.BeginEdit()
row(&quot;number&quot = i
row.EndEdit()
ds.Tables(&quot;serial&quot.Rows.Add(row)

Next i
------------------------------------------------------------

this is where i insert {serial.number} rows into the serial table. The rows are just so that i can use {serial.number} to group by in group2. so that i can display my array.

my display array is such:
@displayTOS
------------------------------------------------------------
evaluateafter({@TOSarray})
global TOSarray() as number
local TOSvalue as number

if {serial.number} <= {Rebate.RebatePeriod} + 2 then
TOSvalue = TOSarray({serial.number})

else
end if

formula = TOSvalue
------------------------------------------------------------

this display array is put into group2 footer so that for each serial.number row (or group) it will display that element in the array.

also, the {serial.number} grouping is based on a selection formula where {serial.number} <= {rebate.period} + 2

so that means that there will be rebate.period + 2 rows within each group1 when i display it on the report.

The problem is the array now. The display is fine, because the @RDarr and the @displayRDarr both work fine and the report displays the correct results. Only the @TOSarray doesn't. The only diff between the two formulas is that for @RDarr, it writes a new value into the array for every element but for the @TOSarray, it only writes in the array when that if condition is met. if i include an else statement after the if like this:
@TOSarray
------------------------------------------------------------
evaluateafter({@RDarr})
'whileprintingrecords
global TOSarray({Rebate.RebatePeriod}+2) as number
global RDarr() as datetime
local i as number
local x as number
local ret as number

'redim preserve TOSarray({Rebate.RebatePeriod} + 2)

for i=1 to {Rebate.RebatePeriod}

if ({Exception.ExceptionType} = &quot;3&quot then

if (RDarr(i) >= {Exception.Value1} and RDarr(i) <= {Exception.Value2}) or (RDarr(i) >={Exception.Value3} and RDarr(i) <= {Exception.Value4}) or (RDarr(i) >={Exception.Value5} and RDarr(i) <= {Exception.Value6}) then
TOSarray(i) = -{Rebate.RebateValue}
else
TOSarray(i) = 0 ( add in this part)
end if

end if

next i

ret = TOSarray(2)
formula = ret
------------------------------------------------------------
then i will get all zeroes again...

the results are very weirs. i can't understand what the flow is and how the arrays are overwritten.

is everything clearer now?

 

[Ngolem]

Ok...you are revealing this problem one formula at a time

I hope @displayTOS is a mis-type and is the same as @displayTOSarray

************************
@displayTOS
------------------------------------------------------------
evaluateafter({@TOSarray})
global TOSarray() as number
local TOSvalue as number

if {serial.number} <= {Rebate.RebatePeriod} + 2 then
TOSvalue = TOSarray({serial.number})

else
end if

formula = TOSvalue
******************************
the evaluateAfter({@TOSarray}) is not needed since this function was done...long time before....in the header...evaluateafter() is used to force the order of functions in the same section...it should be removed...

And I stand by my statement that &quot;WhilePrintingRecords&quot; should go back into all formulas

AND WHAT IS THE WHOLE FORMULA...there is an Else condition missing

********************************************
also, the {serial.number} grouping is based on a selection formula where {serial.number} <= {rebate.period} + 2

so that means that there will be rebate.period + 2 rows within each group1 when i display it on the report.

******************************************************

I don't understand this piece...why does this garuantee this many rows??? if rebate.period = 10 then in the database there has to be &quot;garuanteed&quot; 12 serial numbers?? not less??

If so...then that if-then-else in @displayTOS is TOTALLY unnecessary since the selection statement would rule out anything greater.

ALSO

for your array to be filled the way I think you expect...{Exception.ExceptionType} = &quot;3&quot;... MUST ALWAYS BE THE CASE

You are processing only the first record in the Group header...when the Group for Rebate.ID changes....not all of them....

I think this is where your problem is....
If (Exception.ExceptionType} is <> 3 in the first record the whole row of the array is filled with zeros

***************************************************
for i=1 to {Rebate.RebatePeriod}

if ({Exception.ExceptionType} = &quot;3&quot then

if (RDarr(i) >= {Exception.Value1} and RDarr(i) <= {Exception.Value2}) or (RDarr(i) >={Exception.Value3} and RDarr(i) <= {Exception.Value4}) or (RDarr(i) >={Exception.Value5} and RDarr(i) <= {Exception.Value6}) then
TOSarray(i) = -{Rebate.RebateValue}
else
TOSarray(i) = 0 ( add in this part)
end if

end if

*****************************************************

This If-then-else condition for {Exception.ExceptionType} makes no sense to me...really unless you want a row of zeros if the first record has a value other that 3




Jim
JimBroadbent@Hotmail.com

 

[addy80]

now that you know how things are working in the report and have a clearer idea of what is happening , it'll be easier to refer to stuff. thanks for your time once again(is this freelance or do they pay u? heheh)

ok jim, let's get 2 things clear.

1. i am certain that i can process all the records in the table. this has been proven by the following results:

grp1 0 15 15 20 15 0 0 0
grp2 0 20 15 20 15 0 0 0

the exception table with the exception value field in the database looks like this:
exception value
4
2
3
3

if i could not access the 3s in the 3rd and 4th row, then how did i get the interspersing 15 and 20?
so, it is proven that i can access all records in the table.

2.The row of all zeros only comes about when i insert a reset formula into the report. If i don't then i will get the results above. so i have a feeling that the 'if' condition is not filtering out the appropriate data.
------------------------------------------------
if (RDarr(i) >= {Exception.Value1} and RDarr(i) <= {Exception.Value2}) or (RDarr(i) >={Exception.Value3} and RDarr(i) <= {Exception.Value4}) or (RDarr(i) >={Exception.Value5} and RDarr(i) <= {Exception.Value6})
------------------------------------------------
is there sth wrong with the 'if' condition?
can i compare dates the way i do?

the arrays just subbornly overwrite each other

thanks jim

 

[Ngolem]

sorry....but you are wrong...you are processing only the first record in the header...the rest are being processed in the detail section...(which you don't have anything there)

And...NO! I am not getting paid for offering advice...none of us are here .... it is purely volunteer. We like Crystal Reports

to prove what I say is true....strip out all of the formulas and in the Group1 header section put the following fields...and suppress all other sections

{Rebate.RebateID}; {Rebate.RebatePeriod}; {Exception.ExceptionType} ; {Rebate.RebateValue}

IF {Rebate.RebateID} is the header value for group1 then you should get only one row of data for each {Rebate.RebateID} NOT MULTIPLE values as you are trying to process in {@TOSarray}

Hopefully you will see this and find the flaw...{@TOSarray} formula does not work the way you think it does....in spite of the data you apparently see...this should prove it to you.



Jim
JimBroadbent@Hotmail.com

 

[addy80]

thanks for the enlightenment jim.
you reminded me of one thing that i tested once. i tried accessing all the exception table records in the group and only the first record was seen in the group header. so, i guess u were right. when i tried to access the records in the details section however, they could all be accessed. so i just assumed that they could all be accessed in the group header as well. i guess i was wrong.

but what puzzles me is how were the records processed then? how did u get a result in the array when there was no formula in the details section? and how are the records processed in the details section? so where do u advise me to put the @TOSarray formula then for all the records to be accessible?

and, how are the records and formulas processed?
is it like this:

for i=1 to number of records accessed
do formula one to last formula within that section
next i
or is it like this:
for i=1 to number of formulas
process first record then second until last records
next i

as in, are the records processed first ir are the formulas processed first?

and also, are the formulas in the group processed group by group or are they processed record by record?

will the array used by group1 be shared with group2 or are the arrays used separately within each group?

i really can't understand how CR processes records and formulas...could u please help.

thanks once again jim

 

[Ngolem]

records are processed by looping through the detail section...nowhere else...in header records you process ONLY the first record of a group....in the footer...only the last record of the group is seen.... the rest is in the details section.

WhilePrintingRecords {HIGHLY RECOMMENDED} makes certain that the formulas are processed as the report prints to the screen....this is important since then everything is co-ordinated....the ONLY time I do not include it in a formula is when the formula is used for GROUPING or is a Summary operation...failure to not use &quot;WhilePrintingRecords&quot; can lead to unpredictable results

Evaluateafter() works ONLY in a specific report section... otherwise it is at best useless at worst unpredictable.
to garuantee a formula or subreport executing before another formula often it is best to create a subsection before the critical formula....

Crystal is linear...it processes records in a preset (developer designed) order. IT DOES ONLY ONE PASS THROUGH THE RECORDS AS IT PRINTS THE REPORT.

That formula filling the array has bothered me from the beginning of this long post....but until that test you refused to accept my comments.

&quot;For-next&quot; loops in Crystal are quite limited...they Are useful in searching arrays and filling in array values....but in a formula they work SOLELY on one record.
The same is true of &quot;While loops&quot; as well.

You must stop thinking VB when working in Crystal...even though there is a Basic syntax to Crystal the functions don't necessarily perform like VB.

As to why some of the data looks right is just an accident...probably cases where {Exception.ExceptionType} does = 3 in the first record.

Your report needs to be re-thought out....personally this is difficult for me because I have only snippets of how each record looks.

show me the structure of each record (ie. each field name)

Much of what is being done by @TOSarray is done elsewhere in the report AND the formula AS_IS WILL NOT WORK for reasons outlined above.

Show me the fields in a record and we will go from there...I sort of know what you want but I want the raw information first.


Jim
JimBroadbent@Hotmail.com

 

[addy80]

ok, here goes then.
Exception table:
------------------------------------------------------------
exc value1 value2 value3 value4 value5 value6 desc
Type
1 <NULL <NULL> <NULL> <NULL> <NULL> <NULL> TI
2 3/13/02 5/13/02 <NULL> <NULL> <NULL> <NULL> LBV
3 2/1/01 2/21/01 4/1/01 4/21/01 9/9/09 9/9/09 TOS
3 12/1/01 12/21/01 1/1/02 1/21/02 9/9/09 9/9/09 TOS
4 3/1/02 3/21/02 9/9/09 9/9/09 9/9/09 9/9/2009 TOS

------------------------------------------------------------

This is the rebate table:
------------------------------------------------------------
RebateID startDate ExpiryDate rebateValue rebatePeriod
MT1999 11/20/2001 4/20/2002 15 6
MT2000 11/20/2000 6/20/200120 8 2
------------------------------------------------------------

hope it's clearer now

 

[Ngolem]

so how are we linking the 2 tables together? What is the commonality?

I assume 2 exception type 3's is a typo Jim
JimBroadbent@Hotmail.com

 

[addy80]

hey jim, just wanted to let you know that everything is working fine now

you were right. i just chucked all the formulas into the details section and i added a &quot;whileprintingrecords&quot; to every single formula and it just solved all my problems like the wave of a wand! Thanks. also, i read about it it the Crystal reports: 8.5 Osborne. great book. thanks once again jim