Move array info to Mainreport - several info 3

[FinnB]

Hi
I'm creating af report where some of the
pnr's have info in another table which I cannot directly
link to - .
Therefore I have created a subreport where I find all the
info for all the pnr's.

I want to place the subreport
in the mainreport reportHeader
so the subreport only send the sql to
the database(DB2).
What I want to do is add each pnr who has information into
a array and then move the data to the mainreport.
The problem is that I don't have the right syntax.

the data I want to move is:
pnr startdate enddate
0101301448 25022004 29022004
0201361549 09022004 29022004

----subreport
//@subreport
whileprintingrecords;
shared array pnr;
numbervar Counter;
(Counter := Counter + 1;
//The line below ensures that the size of the array does
//not exceed 1000 values. An array can contain a maximum
//of 1000 values.
if Counter <= 1000
//Redim Preserve is function that changes the size of the
//array according to the new value of Counter. Also, the
//existing values in the array are not removed or changed.
//The array now has space for a new value.
then (Redim Preserve pnr[Counter];
//The new value is added to the newly created space in the
//array.
pnr[Counter] := {MyDatabase.pnr}));
-------
In the mainreport when pnr='array pnr' it shall
find the right startdate and enddate for that pnr

how is the syntax for the
Mainreport ???

-fb

 

[lbass]

Have you tried linking the subreport to the main report on the pnr fields or on formulas that convert the fields so that they are linkable? If you are able to do this, you could place the subreport in the group header (assuming you have a group on pnr) and would not need to use shared variables.

-LB

 

[FinnB]

Hi lb,
The report has 10225 pnr
and it is only around 79 pnrs which
have any data comming from the subreport
therefore I would like to place the subreport
in the reportheader and use, if possible,
shared array variables.
This way it will only take 1-2 minute to get the
data for the whole report.
By linking the subreport to the groupheader it will
take at least 1 hour to get all the data.
The problem is :
I can find the pnr from the array but then I don't
know the syntax to get the right startdate and enddate
for that pnr.
-fb

 

[tektipdjango]

you can create in the subreport 2 other shared arrays
startDate[counter] and endDate[counter] the same way as pnr
thus when you retrieve in the main report the pnr you can as well retrieve the startdate and end datte with the same rank.

Anyway if you need the information about the pnr in the details of the main report it means that those details are based on the pnr, so there's a link between the information and the best way would be to join the pnr table with others.
When you say you can't join, please can you give information?

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[FinnB]

Hi,
I can naturally join on pnr
but for speed of the report
it would be better to add
the subreport in the reportHeader of
the Mainreport.

I need the help with the syntax
for finding the pnr and the equal startdate
and enddate which belong to the indivual pnr.

-fb

 

[GJParker]

for djangos way to work you would create 3 arrays in your sub report

i.e.

//@subreport
whileprintingrecords;
shared array arrPNR;
shared array arrStartDate;
shared array arrEndDate;
numbervar Counter;
(Counter := Counter + 1;

if Counter <= 1000 then
(Redim Preserve arrPNR[Counter];
arrPNR[Counter] := {MyDatabase.pnr}));

(Redim Preserve arrStartDate[Counter];
arrStartDate[Counter] := {MyDatabase.StartDate}));

(Redim Preserve arrEndDate[Counter];
arrEndDate[Counter] := {MyDatabase.EndDate}));

Then in the Main report create a formula to loop through the arrays and return the values if pnr is found.

//@pnrMatch
WhilePrintingRecords;
//Declare array variables
shared array arrPNR;
shared array arrStartDate;
shared array arrEndDate;

//Declare string variables to hold values
Local stringVar strPNR;
shared stringVar strStartDate;
shared stringVar strEndDate;

numbervar i;

//Loop through the pnr array to see if it exists
For i = 1 to Count(arrPNR)
Do( If arrPNR = {Table.pnr} Then
strPNR := arrPNR;
strStartDate := arrStartDate;
strEndDate := arrEndDate);

//Display the PNR
strPNR

Then create 2 seperate formulas

//@StartDate
EvaluateAfter({@pnrMatch});
shared stringVar strStartDate;
strStartDate

//@EndDate
EvaluateAfter({@pnrMatch});
shared stringVar strEndDate;
strEndDate

This should return the correct data

HTH


Gary Parker
Systems Support Analyst
Manchester, England

 

[FinnB]

Hi Gary,
thks for your help!!

I get an error message when I
create the
variabel @pnrMatch

'An assignment is expected here
and cursor flashes
where I have written (here)

/Loop through the pnr array to see if it exists
For i (here)= 1 to Count(arrPNR)
Do( If arrPNR = {Table.pnr} Then
strPNR := arrPNR;
strStartDate := arrStartDate;
strEndDate := arrEndDate);

//Display the PNR
strPNR

---
Do you have any idea what is missing ???

Much appreciated
-fb

 

[lbass]

I'm sure Gary meant:

For i := 1 to Count(arrPNR)

-LB

 

[FinnB]

Hi LB,
yes that's right

I would like to place the subreport
in the reportHeader
Because that will speed up the reportdata collection

how shall the loop variabel be in that case????

when using the variabels as created by Gary
I find every time the correct pnr but the two
date variabels is always giving the last date from
subreport.

when the mainreport find the matching pnr from the
subreport then it should use the place no in array
to uptain the startdate and enddate-

Is there a way to count on which place the pnr is placed
in the array when the match is done at Mainreport??

This number is the key to find the startdate and enddate.

-fb

 

[lbass]

The following revisions of Gary's formulas worked for me:

//@subreport
whileprintingrecords;
shared stringvar array arrPNR; //if PNR is a number field,
//change to numbervar
shared stringvar array arrStartDate;
shared stringvar array arrEndDate;
numbervar Counter;

Counter := Counter + 1;

if Counter <= 1000 then
(
Redim Preserve arrPNR[Counter];
arrPNR[Counter] := {MyDatabase.pnr};

Redim Preserve arrStartDate[Counter];
arrStartDate[Counter] := {MyDatabase.StartDate};

Redim Preserve arrEndDate[Counter];
arrEndDate[Counter] := {MyDatabase.EndDate}
);

//{@pnrMatch} for display of pnr in main report and setting of dates:
WhilePrintingRecords;
shared stringvar array arrPNR;
shared stringvar array arrStartDate;
shared stringvar array arrEndDate;
stringVar strPNR;
stringVar strStartDate;
stringVar strEndDate;
numbervar i;

For i := 1 to Count(arrPNR)
Do( If arrPNR = {table.pnr} Then
(
strPNR := arrPNR;
strStartDate := arrStartDate;
strEndDate := arrEndDate)
);
strPNR

//@StartDate
EvaluateAfter({@pnrMatch});
whileprintingrecords;
stringVar strStartDate;
strStartDate

//@EndDate
EvaluateAfter({@pnrMatch});
whileprintingrecords;
stringVar strEndDate;
strEndDate

If {table.PNR} and {MyDatabase.PNR} are number fields, change the variable type to numbervar in all formulas where it is referenced. I'm assuming that the dates are strings based on their appearance.

-LB

 

[FinnB]

Hi django, Gary Parker & lbass,

Many thks for your help in solving the challenge !!

It works beutifully,

-fb

 

[GJParker]

Glad it worked fb, thanks for the star

Gary Parker
Systems Support Analyst
Manchester, England