How do I use array to hold counts of unknown data?

[meryls]

I will be processing customer rental records to get total counts of each type of unit rented by each customer, then totals for all the customers. For each customer, there may be any number of records, e.g, Smith may have 3 records with rental of "type" Table and 8 records of rental of "type" Chair. These rental "types" need to be collated with their quantities. I figure that the best solution would be to add counts to an array or rental types, but I'm not sure how to set this up in Crystal. Whe I am done, I need to print out:

Type Quantity
---- --------

for each customer and for the totals.

I have no idea in advance what the different data may be in the "Type" field.

How might I do this?

Thanks in advance for the help!

Meryl

 

[lbass]

Try grouping on {table.customer} and {table.type}, and then right click on the {table.type} field in the detail section, and insert summaries (count) for all groups and for the grand total. Then you can drag the group results to the appropriate group headers and suppress the details.

-LB

 

[Ngolem]

Ok...you should give a more concrete example but I think you want something like this

ID Type Quantity

JDoe Chair 4
Table 6
----
total items 10

TSmith glass 40
chair 5
table 1
----
total items 46

Summary of items rented

Glass 40
Chair 9
Table 2
----
Grand total items 51

If this is basically what you want then you can do it this way.

Report header (Initialize Arrays)
Group 1 header (by Customer ID)
Group 2 header (by Type)
details
Group 2 footer
Group 1 footer (displays total rented items/customer)
Report footer (displays Grandtotal rented items/type)

There will be 2 arrays used in the report footer.
One for the Type and another for the Quantity. The reason for this is to be able to dislay the results with a crisp left hand edge.

So let's start the report from top to bottom

In the report header place this formula (suppressed)

//@InitGrandTotalArrays

WhilePrintingRecords;

//estimate the number of Types needed then add 50% more to the total
//eg. if you have 100 rentals then dimension the array for 150 items
//or just do it for 1000 items and forget about it....

StringVar Array Type := [ "","","","","","",...add more...,"","" ];
StringVar Array Qty := [ "","","","","","",...add more...,"","" ];
NumberVar position := 0;
numberVar GrandTotalItems := 0;

NOTE: if there are more than 1000 rented items it can still be done with arrays....just use more than one + some tricks

In the Group 1 header place this formula

//@initCusttotal (suppressed)

WhilePrintingRecords;
if not inRepeatedGroupHeader then
numberVar totalTypeItems := 0;

You don't say if there is only one record/type in the detail section...I'll assume there isn't. Therefore place the {Table.quantity) field in the detail section and suppress the Detail section entirely.

in the Group footer for Group 2 (Type) place the following fields

{Table.Type} {Sum of {Table.Quantity}}

Also in the Group 2 footer place this formula

//@CalcTotals (suppressed)

WhilePrintingRecords

StringVar Array Type ;
StringVar Array Qty ;
NumberVar position ;
NumberVar totalTypeItems ;
numberVar GrandTotalItems ;
Numbervar loc;
BooleanVar flag := False;

totalTypeItems := totalTypeItems + sum({Table.quantity},{Table.type});

GrandTotalItems := GrandTotalItems + sum({Table.quantity},{Table.type});

//find out if you have already saved type data before

for loc := 1 to ubound(Type) do
(
if Type[loc] := {Table.type} then
(
Qty[loc] := totext(tonumber(Qty[loc]) + sum({Table.quantity},{Table.type}),0);
flag := True;
)
if flag or type[loc] = "" then Exit For;
);

//if not in the list add it
if not flag then
(
position := position + 1
Type[position] := {Table.type];
Qty[position] := totext(sum({Table.quantity},{Table.type}),0);
);

In the Group 1 footer place this formula

//@DisplayCustomerTotal
WhilePrintingRecords

NumberVar totalTypeItems ;

"Total Items Rented: " + totext(totalTypeItems , 0);

the report footer contents will depend on several factors. Not the least of them being the number of characters in the Type description.

Let us say the max size of the description is 10 char (eg. "wine glass" ) then unless using CR 9.0 or greater you are limited to 254 chars/formula...ie. you can place about 20 items/formula (have to include carriage returns into the total...perhaps 25 if on average each item 9 chars or less with the occassional 10 char)

You should be able to place at least 4...perhaps 5 sets of type/qty totals side by side in one Report footer section giving you about 100 items/section. The display formulas will look like this

//@DisplayType 1-20
WhilePrintingRecords
StringVar Array Type ;
NumberVar position ;
StringVar result := "";

for position := 1 to 20 do
(
result := result + Type[position] + chr(13);
);
result;

//@DisplayQty 1-20
WhilePrintingRecords
StringVar Array Qty;
NumberVar position ;
StringVar result := "";

for position := 1 to 20 do
(
result := result + Qty[position] + chr(13);
);
result;

similarly

//@DisplayType 21-40
WhilePrintingRecords
StringVar Array Type ;
NumberVar position ;
StringVar result := "";

for position := 21 to 40 do
(
result := result + Type[position] + chr(13);
);
result;

//@DisplayQty 21-40
WhilePrintingRecords
StringVar Array Qty;
NumberVar position ;
StringVar result := "";

for position := 21 to 40 do
(
result := result + Qty[position] + chr(13);
);
result;

You get the picture.....cloning does wonders in making these formulas

Now you create a number of section in the report footer based on the total size of the Type array...if it is 1000 elements then you need 1000/100persection or 10 section of these display formulas.

enable the "suppress blank section" of each of these sections to eliminate them if not used (now you see why we make number values into strings...easier to display )

Add one more Report footer section and add this formula

//@displayGrandTotal

WhilePrintingRecords;

NumberVar GrandTotalItems;

"GrandTotal items rented: " + totext(GrandTotalItems,0);

And there you have it...simple eh!








Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

When giving an example of how a report is to look, include what it's really to look like, you don't show the customer.

The reason this is important is that you might just use a crosstab (Insert->Crosstab) grid for this, but it depends on how you really want the report to look.

Drop the crosstab into the Customer group and you'll have a seperate one for each customer.

Or place the Customer as the row and the type as the column with your qty as the summarized field and you'd get one crosstab as in:

Type Type
Customer qty qty

-k

 

[meryls]

Thanks, Ngolem, lbass and synapsevampire for your suggestions.

I tried the formula as Ngolem had suggested. I ran into some problems with Crystal complaining the array was out-of-bounds in formula calcTotals. Since I had run into this before when trying to use arrays, I took a break ( I was quite tired ... it was late last night).

Today, I tried synapsevampire's suggestion of the cross-tab, which I had never used before. This worked great, after I mastered a few cross-tab fundamentals.

So, thanks again to all of you! Crystal definitely has a learning curve, and I couldn't have done it without the help of this great group!

Meryl