[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1810
  • Last Modified:

Using Crystal Reports Array to Store Records?

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
melegant99
Asked:
melegant99
  • 5
  • 4
2 Solutions
 
mlmccCommented:
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
 
melegant99Author Commented:
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
 
mlmccCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
melegant99Author Commented:
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
 
melegant99Author Commented:
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
 
mlmccCommented:
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
 
melegant99Author Commented:
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
 
mlmccCommented:
End all the formulas in the subreport with this line

''

You can then set the sections to SUPPRESS BLANK SECTION

mlmcc
0
 
melegant99Author Commented:
Beautiful, you are the best mlmcc.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now