SUBREPORT - PASSING VARIABLE FROM MAIN 1

[reidtw]

Aaaaah!!!! I have spent ages trawling this forum but couldn't resist any longer.

I am using cr8, sql server odbc link.

I have been trying to share a number variable FROM the main report to the subreport. The reports are not linked.

Main report: -

In group header (only one group)
//@VPOOLID_reset
whileprintingrecords;
Shared NumberVar x:=0

In details section
//@VPOOLID
whileprintingrecords;
Shared NumberVar x;
x:= {Pool.Id};

The subreport is in the main report footer b and the following variable is in the subreport details section

//@VPOOLID
whileprintingrecords;
Shared NumberVar x;

Now, when viewed, the pool id from the last record in the main report cascades down all the records in the subreport thus:-

svar x sub pool id
1111 1110
1111 1111
1111 1112

Obviously I would like to see the records match up, though some of the records in the subreport should not have a matching record in the main report.

I have tried different positions for all the variables but this is as close as I got and now need some expert help to resolve it!

Cheers
Trev

 

[synapsevampire]

You misunderstand how Crystal works.

The details are the row section, setting the values there isn't going to help if you have the subreport at a group level...so by the time x gets to the group footer b, it has ONLY the last value from the details for that group (since you only have one, it's the last row in the datasource of the main report).

If you want to match these ids row for row, you'll need to put the subreport in a details B section of the main report.

But I'm guessing as you haven't clearly identified what you want, rather what you do not.

Also if it's details to details, why wouldn't you just join on this field?

-k

 

[reidtw]

I cannot join on this field because there are ids in the sub report that are not in the main report; these are the ids I want to report in the sub report separately from the records in the main report but in the main report footer b after the totals I have in the main report a!

T

 

[lbass]

If you want to proceed the way you are doing it now, then remove the reset formula and change your main report formula to:

//@VPOOLID
whileprintingrecords;
Shared StringVar array x;
NumberVar i := i + 1;

if i < 1000 then (
redim preserve x;
x:= totext({Pool.Id},"0000")
);

Then in the subreport, use a formula like this:

whileprintingrecords;
shared stringvar array x;
stringvar y;
numbervar j;

for j := 1 to ubound(x) do(
if x[j] = totext({Pool.Id},"0000") then
y := x[j])
;
val

-LB

 

[reidtw]

lbass,

Works great, thanks!

T

 

[reidtw]

Hi lbass,

Upon further analysis by the business user, the sub report is not quite returning the correct number of records.

Here are the formulas I input to the main and sub detail sections:-

Main report
//@POOLID
whileprintingrecords;
Shared StringVar array f;
NumberVar g:= g + 1;
if g < 1000 then (
redim preserve f[g];
f[g]:= totext({Pool.Id},"0000")
);

Subreport
//@POOLID
whileprintingrecords;
shared stringvar array f;
stringvar e;
numbervar j;

for j := 1 to ubound(f) do(
if f[j] = totext({Pool.Id},"0000") then
e := f[j])
;
val(e)

Here are the poolid results from the subreport (correct until it gets to #1980): -

A = Main report poolid
B = Subreport poolid

A B
1837 3237
112 1912
1828 3241
121 2032
1500 3238
1838 3235
543 3239
2003 3234
144 145
1520 3232
1836 3236
1581 3315
1859 3310
1881 3233
1914 1977
1972 1976
1980 228
1980 229
1980 230
1980 231
1980 1353
1980 1884
1980 1354
1980 1357
1980 1350
1980 1346
1980 1347
1980 1342
1980 1345
1980 1344

Sorry, some background required for you to understand what I am trying to achieve.

The main report returns account recs that have been performed and the sub should show those that have not. A user can enter a date range so in the main report a poolid may appear more than once. Therefore, the subreport should return a record for each instance a poolid has had no rec done.

Example: -

Poolid #1 has a successful rec on the 10th and no rec on the 11th.
Poolid #2 has a successful rec on both days.
Poolid #3 has no recs on either day.

Result is...

No. recs performed = 3
No. recs not performed = 3

At the moment the reports have a many to one relationship; the main is pulling in many recs (if more than one day chosen) and the sub is pulling in a list of poolids as single records. The rec table does not hold a record for poolids where no rec was performed hence why I am trying to create this record with the sub report.

I would very much appreciate your feedback on whether I can achieve what I am trying to achieve or if you think this can be better organised.

T

 

[lbass]

First, my earlier suggestion used an array that can only have 1000 elements. However, it looks like there is probably a better way of accomplishing this. What tables are you working with? Do you have a table that contains all dates? (I ask because it looks like you are evaluating whether pool IDs do not have a rec per day.) Please identify the join types between tables. Also, I don't know what you mean by "rec". Could you describe in more descriptive terms what you are trying to do?

-LB

 

[reidtw]

Apologies for the vagueness in my earlier post.

"Rec" means reconciliation (rec); I am reporting on account reconciliations.

The rec table is in the main report and indeed has many dates and within each date a poolid (account) can have more than one successful rec as well as recs on each of the dates.

There is a static table listing the accounts that are on the system but the rec table does not record which accounts have not been reconciled so I cannot take a direct count from the rec table.

I had previously tried to link the account table as the primary to the rec table as a child but could not get the correct total counts.

So with the method you gave me I was trying to identify the accounts that had not been reconciled by identifying those in the static table that do not appear in the rec table. However, this has not eliminated some of the accounts for whatever reason!

If the above is not any clearer, please confirm.

Thanks
T

 

[lbass]

It sounds like you want to list to report the number of times a poolID is or is not reconciled over a period of days, and that you expect at least one reconciliation per day during the parameter period. Try using a left join from the static table to the rec table on the poolID field. Insert a group on {static.poolID} and then a second group on {table.date} (set this per day, if your field is a datetime). Then insert a distinctcount on {table.date} per poolID. Add a formula for no reconciliations like this:

//{@norecs}:
datediff("d",minimum({?daterange}),maximum({?daterange})+1 - distinctcount({table.date},{table.poolID})

Place this in the poolID group footer, along with the distinctcount of {table.date}, which represents the number of days a reconciliation did occur.

-LB

 

[reidtw]

Thanks lbass, I tried this but the count of no recs may include days over the period that should not be included e.g. holidays and weekends where there was no activity. To try to resolve this is tricky, I know.

I also need to show the detail of the poolids that have no rec; there must be a list of them (not just a count) with some sort of narrative e.g. "NO REC PERFORMED", so the list would look like this: -

Detail: -

PoolId Name Rec_date Other_column_headings....
1 A1 07/04/06
1 A1 10/04/06
2 A2 NO REC PERFORMED
2 A2 10/04/06
3 A3 07/04/06
3 A3 10/04/06

Summary: -

Count recs 5
Count no recs 1


Any other ideas?

T

 

[lbass]

The problem is that I'm guessing you would want to know which days the PoolID wasn't reconciled, not just that they are missing reconciliations, which implies that you have a table with all possible dates. This means you need both the all possible poolids table ("static") and a date table, and some way of relating the two. I can't quite figure out how to do that.

You could save the main report as a subreport, add it to the report footer, and then add a group selection formula like this:

distinctcount({table.date},{table.PoolID}) < distinctcount({table.date})

This would display the poolIDs and the reconciliations that did occur for those poolIDs that are missing at least one reconciliation, but it would not ID the missing dates. Maybe you could set up a formula using an array of possible dates and then check actual dates against this in order to display the missing date(s) per PoolId.

I'm not sure what the rest of the data is per PoolID to know how that fits into the problem. If there is data for every possible date of reconciliation, then you could probably just check for a null reconciliation date.

-LB

 

[reidtw]

Thanks lbass, I'll have a look at those suggestions.

The array works well, as you say only up to the 1000th record. Is there any way I could start an array from the 1001st record and so on?

T

 

[reidtw]

Maybe you could set up a formula using an array of possible dates and then check actual dates against this in order to display the missing date(s) per PoolId."

lbass, how would I do this?

Thanks
T

 

[lbass]

First, you could modify my first suggestion with the array to the following, adding additional variables for each additional array of 1000, as in:

whileprintingrecords;
Shared StringVar array x;
shared stringvar array k;
NumberVar i := i + 1;
numbervar n;

if i < 1000 then (
redim preserve x;
x:= totext({table.poolID},"0000")
);
if i >= 1000 then (
n := n + 1;
if n < 1000 then(
redim preserve k[n];
k[n] := totext({table.poolID},"0000"))
);

Then in the subreport, you would use a formula like:

whileprintingrecords;
shared stringvar array x;
stringvar y;
numbervar j;
shared stringvar array k;

for j := 1 to ubound(x) do(
if x[j] = totext({table.poolID},"0000") then
y := x[j]);
for j := 1 to ubound(k) do(
if k[j] = totext({table.poolID},"0000") then
y := k[j]
);
val

My other thought was that you could insert a subreport in the report header with your main report criteria, and in that subreport, accumulate all dates where there is at least one reconciliation--these would be the relevant dates to check against each poolID. In this subreport, you would set up a formula like the following in the detail section:

whileprintingrecords;
shared stringvar y;

if instr(y, totext({Orders.Order Date},"MM/dd/yyyy")) = 0 then
y := y + totext({Orders.Order Date},"MM/dd/yyyy") + ", ";
y

Then in the subreport report footer, add a formula:

whileprintingrecords;
shared stringvar y;

You can suppress all sections within this subreport, so that it is not visible. Then in the main report create these formulas:

//{@reset} to be placed in the group header for({table.poolID}):
whileprintingrecords;
shared stringvar y;
stringvar x := "";
stringvar z := "";
numbervar i := 0;
numbervar j := 0;

//{@currentdates} to be placed in the details section and suppressed:
whileprintingrecords;
stringvar x := x + totext({Orders.Order Date},"MM/dd/yyyy")+", ";

//{@missingdates} to be placed in the group footer (poolID):
whileprintingrecords;
shared stringvar y;
stringvar x;
stringvar z;
numbervar i;
numbervar j := ubound(split(y,", "));
numbervar cnt;

for i := 1 to j do(
if instr(x, split(y,", ")) = 0 then (
cnt := cnt + 1;
z := z + split(y,", ") + ", "));
z

Then to summarize at the report level, insert a count on the datefield to get the number of reconciliations, and to get the number not reconciled, create this formula for the report footer, which will show the count of the missing dates:

whileprintingrecords;
numbervar cnt;

-LB

 

[reidtw]

Hi lbass,

I've put the following syntax into the reports: -

Main report variables -

whileprintingrecords;
Shared StringVar array f;
Shared StringVar array f1;
Shared StringVar array f2;
Shared StringVar array f3;
Shared StringVar array f4;
Shared StringVar array f5;
Shared StringVar array f6;
Shared StringVar array f7;
Shared StringVar array f8;
Shared StringVar array f9;
Shared StringVar array f10;
Shared StringVar array f11;
Shared StringVar array f12;
Shared StringVar array f13;
Shared StringVar array f14;
Shared StringVar array f15;
Shared StringVar array f16;
Shared StringVar array f17;
Shared StringVar array f18;

NumberVar g:= g + 1;
Numbervar g1;
Numbervar g2;
Numbervar g3;
Numbervar g4;
Numbervar g5;
Numbervar g6;
Numbervar g7;
Numbervar g8;
Numbervar g9;
Numbervar g10;
Numbervar g11;
Numbervar g12;
Numbervar g13;
Numbervar g14;
Numbervar g15;
Numbervar g16;
Numbervar g17;
Numbervar g18;

if g <= 1000 then (
redim preserve f[g];
f[g]:= totext({AccountPool.Id},"0000")
);

if (g > 1000 and g <= 2000) then (
g1 := g1 + 1;
if g1 <= 1000 then(
redim preserve f1[g1];
f1[g1] := totext({AccountPool.Id},"0000"))
);

if (g > 2000 and g <=3000) then (
g2 := g2 + 1;
if g2 <= 1000 then(
redim preserve f2[g2];
f2[g2] := totext({AccountPool.Id},"0000"))
);

if (g > 3000 and g <=4000) then (
g3 := g3 + 1;
if g3 <= 1000 then(
redim preserve f3[g3];
f3[g3] := totext({AccountPool.Id},"0000"))
);

if (g > 4000 and g <=5000) then (
g4 := g4 + 1;
if g4 <= 1000 then(
redim preserve f4[g4];
f4[g4] := totext({AccountPool.Id},"0000"))
);

if (g > 5000 and g <=6000) then (
g5 := g5 + 1;
if g5 <= 1000 then(
redim preserve f5[g5];
f5[g5] := totext({AccountPool.Id},"0000"))
);

if (g > 6000 and g <=7000) then (
g6 := g6 + 1;
if g6 <= 1000 then(
redim preserve f6[g6];
f6[g6] := totext({AccountPool.Id},"0000"))
);

if (g > 7000 and g <=8000) then (
g7 := g7 + 1;
if g7 <= 1000 then(
redim preserve f7[g7];
f7[g7] := totext({AccountPool.Id},"0000"))
);

if (g > 8000 and g <=9000) then (
g8 := g8 + 1;
if g8 <= 1000 then(
redim preserve f8[g8];
f8[g8] := totext({AccountPool.Id},"0000"))
);

if (g > 9000 and g <=10000) then (
g9 := g9 + 1;
if g9 <= 1000 then(
redim preserve f9[g9];
f9[g9] := totext({AccountPool.Id},"0000"))
);

if (g > 10000 and g <=11000) then (
g10 := g10 + 1;
if g10 <= 1000 then(
redim preserve f10[g10];
f10[g10] := totext({AccountPool.Id},"0000"))
);

if (g > 11000 and g <=12000) then (
g11 := g11 + 1;
if g11 <= 1000 then(
redim preserve f11[g11];
f11[g11] := totext({AccountPool.Id},"0000"))
);

if (g > 12000 and g <=13000) then (
g12 := g12 + 1;
if g12 <= 1000 then(
redim preserve f12[g12];
f12[g12] := totext({AccountPool.Id},"0000"))
);

if (g > 13000 and g <=14000) then (
g13 := g13 + 1;
if g13 <= 1000 then(
redim preserve f13[g13];
f13[g13] := totext({AccountPool.Id},"0000"))
);

if (g > 14000 and g <=15000) then (
g14 := g14 + 1;
if g14 <= 1000 then(
redim preserve f14[g14];
f14[g14] := totext({AccountPool.Id},"0000"))
);

if (g > 15000 and g <=16000) then (
g15 := g15 + 1;
if g15 <= 1000 then(
redim preserve f15[g15];
f15[g15] := totext({AccountPool.Id},"0000"))
);

if (g > 16000 and g <=17000) then (
g16 := g16 + 1;
if g16 <= 1000 then(
redim preserve f16[g16];
f16[g16] := totext({AccountPool.Id},"0000"))
);

if (g > 17000 and g <=18000) then (
g17 := g17 + 1;
if g17 <= 1000 then(
redim preserve f17[g17];
f17[g17] := totext({AccountPool.Id},"0000"))
);

if (g > 18000 and g <=19000) then (
g18 := g18 + 1;
if g18 <= 1000 then(
redim preserve f18[g18];
f18[g18] := totext({AccountPool.Id},"0000"))
);


Subreport formula -

whileprintingrecords;
shared stringvar array f;
shared stringvar array f1;
shared stringvar array f2;
shared stringvar array f3;
shared stringvar array f4;
shared stringvar array f5;
shared stringvar array f6;
shared stringvar array f7;
shared stringvar array f8;
shared stringvar array f9;
shared stringvar array f10;
shared stringvar array f11;
shared stringvar array f12;
shared stringvar array f13;
shared stringvar array f14;
shared stringvar array f15;
shared stringvar array f16;
shared stringvar array f17;
shared stringvar array f18;

stringvar h1;
numbervar h2;

for h2 := 1 to ubound(f) do(
if f[h2] = totext({AccountPool.Id},"0000") then
h1 := f[h2]);

for h2 := 1 to ubound(f1) do(
if f1[h2] = totext({AccountPool.Id},"0000") then
h1 := f1[h2]);

for h2 := 1 to ubound(f2) do(
if f2[h2] = totext({AccountPool.Id},"0000") then
h1 := f2[h2]);

for h2 := 1 to ubound(f3) do(
if f3[h2] = totext({AccountPool.Id},"0000") then
h1 := f3[h2]);

for h2 := 1 to ubound(f4) do(
if f4[h2] = totext({AccountPool.Id},"0000") then
h1 := f4[h2]);

for h2 := 1 to ubound(f5) do(
if f5[h2] = totext({AccountPool.Id},"0000") then
h1 := f5[h2]);

for h2 := 1 to ubound(f6) do(
if f6[h2] = totext({AccountPool.Id},"0000") then
h1 := f6[h2]);

for h2 := 1 to ubound(f7) do(
if f7[h2] = totext({AccountPool.Id},"0000") then
h1 := f7[h2]);

for h2 := 1 to ubound(f8) do(
if f8[h2] = totext({AccountPool.Id},"0000") then
h1 := f8[h2]);

for h2 := 1 to ubound(f9) do(
if f9[h2] = totext({AccountPool.Id},"0000") then
h1 := f9[h2]);

for h2 := 1 to ubound(f10) do(
if f10[h2] = totext({AccountPool.Id},"0000") then
h1 := f10[h2]);

for h2 := 1 to ubound(f11) do(
if f11[h2] = totext({AccountPool.Id},"0000") then
h1 := f11[h2]);

for h2 := 1 to ubound(f12) do(
if f12[h2] = totext({AccountPool.Id},"0000") then
h1 := f12[h2]);

for h2 := 1 to ubound(f13) do(
if f13[h2] = totext({AccountPool.Id},"0000") then
h1 := f13[h2]);

for h2 := 1 to ubound(f14) do(
if f14[h2] = totext({AccountPool.Id},"0000") then
h1 := f14[h2]);

for h2 := 1 to ubound(f15) do(
if f15[h2] = totext({AccountPool.Id},"0000") then
h1 := f15[h2]);

for h2 := 1 to ubound(f16) do(
if f16[h2] = totext({AccountPool.Id},"0000") then
h1 := f16[h2]);

for h2 := 1 to ubound(f17) do(
if f17[h2] = totext({AccountPool.Id},"0000") then
h1 := f17[h2]);

for h2 := 1 to ubound(f18) do(
if f18[h2] = totext({AccountPool.Id},"0000") then
h1 := f18[h2]);

val(h1);


When I tab to the last page of the main report all is fine but when it hits the subreport I get the error message "A subscript must be between 1 and the size of the array". I was sure I was passing through arrays of 1000 records but obviously not!

Any thoughts appreciated.

T

 

[lbass]

Nothing stands out to me. I did test my suggestion, so that worked. Where is the error message pointing? The cursor should tell you where the problem is.

-LB

 

[reidtw]

Hi lbass,

The error message appears against the subreport formula and when I click OK the cursor is at the start of the formula window at "whileprintingrecords". Not sure that this is helpful.

T

 

[reidtw]

Hi lbass,

I wondered if you might be able to comment on my hypothesis that the report is falling over when there are too many arrays being passed to the subreport.

For instance, I have 1800 records and this should produce two arrays, the forst for 1 to 1000 and the second for 1001 to 1800. However, if I have all the arrays producing values, from the third onwards the value is 1000 for each. If I comment out all the arrays from the third onwards then the subreport is produced and shows the correct figures.

Is there any way to only pass the correct arrays to the subreport?

Any assistance is appreciated and if you need clarification on the above please advise.

Thanks
T

 

[lbass]

Here's a thought:

If the point of your report is only to display the report footer results, then you could try instead to cluster the records in the main report into groups that yield 1000 records or less. Then you could link the subreport to that group field, and place it in the group footer for that group. Then use just one array. If you are suppressing the details, the display should appear much the same as if you placed the subreport in the report footer (I think, without reviewing all the previous posts in depth).

-LB