Solved

# Using Crystal Reports Array to Store Records?

Posted on 2009-05-02
1,482 Views
Hi,
I have a report where there are two tables that hold info that are related to the same key field.
However, both of these tables hold multiple rows so I am having trouble figuring out how to deal with it in one sub-report (I can't do a sub-report in a sub-report, which would have been awesome).
Plus, table 1 gets displayed in details, table 2 info goes in the footer.

I was thinking to use an array. I would create the array in a formula, but I only want to add distinct values of the NAME on table 2. I then am thinking on looping through that array in the footer and displaying my values,

I am looking for some direction on the code for this, I have an idea but I am not sure how to only add distinct values into the array, how to declare the array so that the size is based on the # of distinct values, where to place the formula (I think I need to add something like whilereadingrecords right?)

Thanks,
0
Question by:melegant99

LVL 100

Expert Comment

Are you saying bith tables have multipe rows for the common field?

Does the main report use a different table?

Why can't you use the main report for table 1 then table2 goes in the subreport?

mlmcc
0

Author Comment

Because the main report is using another table that is related to the order #.

There are in total 6 or 7 tables that all are related to the order #, but all have multiple rows. This is a rare time where I cannot break them out into seperate sub -reports.
0

LVL 100

Expert Comment

Problem is you are going to get many "duplicates"

Main report
Table1 - Order1
Subreport
Table2 - Order1  - 5 rows
Table3 - Order1  - 4 rows
Result - 20 rows

Is there anything thin in table1 or 2 that can be used to limit the rows.

mlmcc
0

Author Comment

No, but I was looking at another post of mine where I asked a question that was along the same lines, and based on something you said mlmcc you have given me an idea, and my question is how to do this:

1) Create a subreport that gathers the data I need. I would need to create 2 arrays, (1) one dimensional array that would have the value, and a 2 dimensional array that would hold the name and description. I could use whileprintingrecords to read these into the array right? Question is, how do I declare/size the array to match the number of records?

2) Pass these back to the main report? (how can I pass an array between reports?)

3) Pass the arrays to whatever sub report where I want to use that data and create a formula to show the array?  (again, how to pass)

0

Author Comment

Wait, to further what I said before,
can I just declare a Shared Array Variable? If I do that in a sub-report, does it make it available everywhere?

Other than that, the other key thing that I am struggling with is how to create the array with the size of records returned, is there a function that would give me that number?
0

LVL 100

Accepted Solution

You have to use shared variables.

Basic idea for shared variables
In the main report you have to declare them before the subreport.
I generally put the declaration in the report header
WhilePrintingRecords;
Shared NumberVar MyValue;

In the subreport
WhilePrintingRecords;
Shared NumberVar MyValue;
MyValue := SomeValue;

To display it
WhilePrintingRecords;
Shared NumberVar MyValue;
MyValue

Arrays can only be 1 dimension so you will need value, name, and description arrays.
Unless you can know how many values when the report starts you can't declare the size.
There are two ways to handle it/.
1. Declare them so large they can never be too small.  1000 is the max
2. Change the dimension when needed
I generally use the 2nd method

In the main report declaration
WhilePrintingRecords;
Shared NumberVar Array Values;
Shared StringVar Array Names;
Shared StringVar Array Descriptions;
Shared NumberVar CurrentSize;

In the subreport as you need to add a row to the array
WhilePrintingRecords;
Shared NumberVar Array Values;
Shared StringVar Array Names;
Shared StringVar Array Descriptions;
Shared NumberVar CurrentSize;

CurrentSize; := CurrentSize + 1;
ReDim Preserve Values[CurrentSize];
ReDim Preserve Names[CurrentSize];
ReDim Preserve Descriptions[CurrentSize];
Values[CurrentSize] := NewValue;
Names[CurrentSize] := NewName;
Descriptions[CurrentSize] := NewDescription;

Generally to display them I concatenate them into a string then display the string
WhilePrintingRecords;
Shared NumberVar Array Values;
Shared StringVar Array Names;
Shared StringVar Array Descriptions;
Shared NumberVar CurrentSize;
Local NumebrVar i;
Local StringVar NameList := '';

For i := 1 to CurrentSize do
(
NameList := NameList & Names[i] & chr(13);
);

NameList

mlmcc

mlmcc
0

Author Comment

All right, that works out great
except:
The subreport that is gathering data, well, it is not a report I want to show....it's sole purpose is to create that array so I can use it in conjuction with other sub-reports. If I supress the section where I place that subreport, well, it does not populate (WhilePrintingRecords). It seems that you cannot use WhileReadingRecords with a shared variable.

Is there a way around this?
0

LVL 100

Assisted Solution

End all the formulas in the subreport with this line

''

You can then set the sections to SUPPRESS BLANK SECTION

mlmcc
0

Author Closing Comment

Beautiful, you are the best mlmcc.
0

## Featured Post

### Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…