Does this required some type of array formula??

[edsaf]

Hi All,

Crystal XI - DB2 Database.

I have a report with 2 subreports:

Subreport 1 contains the available time per team
Subreport 2 contains the tasks closed per team

What i want to do is create a shared variable so that the available time for each team can be called in Subreport 2 so i can measure (tasks closed/ available time).

I don't want to create a shared variable for each team statically either.

Thanks

Eddie

 

[Madawc]

If you group by team and put the subreports in the group header and footer, it should be possible to share, or pass it back as a Subreport Link. Inefficient, of course.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[edsaf]

I have attached a pdf copy of the report and a screen shot of Crystal Reports design view so you can see what im trying to achieve.

http://members.iinet.net.au/~dj_eddie/crystal_design.png

http://members.iinet.net.au/~dj_eddie/task_report.pdf

I have used the header/footer method successfully for the results on pages 2 onwards (as highlighted by the red and blue lines), but on page 1, i wanted a summary level page showing a team per line which i have done by inserting a subreport on RHb. But it doesnt have the available time (and closure rate). Looking at the crystal_design.png (the green line), is it possible to push through the available time from the subreport in RHa to the subreport in RHb??

 

[lbass]

Can we assume that available time is a number field?

In the RHa subreport, you would set up a formula like this in a group footer based on team, assuming that available time is the sum of time for the team group:

whileprintingrecords;
shared numbervar array avtime;
shared stringvar array team;
shared numbervar i := i + 1;
shared numbervar j := distinctcount({table.team});

if i <= j then (
redim preserve avtime[j];
redim preserve team[j];
avtime := sum({table.time},{table.team});
team := {table.team}
);

Then in the second subreport, you would create a formula like this to place in the team group header or footer:

whileprintingrecords;
shared numbervar array avtime;
shared stringvar array team;
shared numbervar i;
shared numbervar j;
numbervar array k;

for i := 1 to j do(
if {table.team} = team then (
redim preserve k[j];
k[j] := avtime
));
k[j]

-LB

 

[edsaf]

Hi lbass,

That worked, fantastic!

Also, i duplicated the process to show the available time at the site level, but it returns this message:

'The subscript must be between 1 and the size of the array'

here is the code:

[in RHa group footer for 'site']

whileprintingrecords;
shared numbervar array avtimesite;
shared stringvar array site;
shared numbervar x := x + 1;
shared numbervar y := distinctcount({@site});

if x <= y then (
redim preserve avtimesite[y];
redim preserve site[y];
avtimesite[x] := Sum ({@true_wait}, {@site})+({#tasking_time_site}*5)*60;
site[x] := {@site}
);

[in RHb group footer for 'site']

whileprintingrecords;
shared numbervar array avtimesite;
shared stringvar array site;
shared numbervar x;
shared numbervar y;
numbervar array z;

for x := 1 to y do(
if {@site} = site[x] then (
redim preserve z[y];
z[y] := avtimesite[x]
));
z[y]

 

[edsaf]

also, just wanted to quickly add that its producing a blank page as page 1.

even though i have suppressed all groups in subreport RHa

 

[lbass]

I don't see anything incorrect with these formulas. What is the formula for {@site}?

-LB

 

[edsaf]

Hi - Sorry, my bad.

The blank page 1 was because the size of the subreport was larger than a page, so it decided to start it on page 2, dont know why, but thats what its doing.

As for the error message for the 'site' version of the formula, as long as the group are suppressed in subreport 1 its all good.(which is what i intended anyways)

Much appreciated.

Thanks

Eddie

 

[edsaf]

hi lbass,

what can i do in cases where y (distinctcount{table.team}) will be less than the distinctcount({table.team}) in the second subreport??

I think thats the reason why i sometimes i get the error:

'The subscript must be between 1 and the size of the array'

there are some cases where i am out between 1 and 5 in the array size.

What can i do to the first code to always have the array size larger by 10 (to allow for differences in team count between the 2 subreports)???

 

[lbass]

I don't see why there should be different numbers of teams per subreport--or why you would allow that. Why wouldn't you ensure that they were the same?

Which formula is generating the error message? And where is the cursor when the error message appears?

-LB

 

[edsaf]

When teams are created in one system, it sometimes take a day or two to update everywhere else.

The formula generating the error is in the seconds sub report and the cursor is on the last line.

variable x = 36
variable y = 35 (this is the distinct count of {team.name})

whileprintingrecords;
shared numbervar array avtimesite;
shared stringvar array site;
shared numbervar x;
shared numbervar y;
numbervar array z;

for x := 1 to y do(
if {@site} = site[x] then (
redim preserve z[y];
z[y] := avtimesite[x]
));
z[y]