# How to Sum "detail section" array data?

It could be a stupid question but I got into trouble and can't figure out (Newbie of Crystal Reports)
I have StringVar (ITEM) and NumberVar (QTY) Array when I put them in report "detail section" It showed data in report (for example they all have three elements)

Report Detail Section as

CUSTOMER 1
ITEM         QTY
-----------   --------
Item A       250
Item B    1,200
Item C       700
-----------------------------------------------------

CUSTOMER 2
ITEM         QTY
-----------   --------
Item A       500
Item B    1,500
Item C       800

How do I Sum Item A to Item C's  QTY for each CUSTOMER? -  and get answer 2,150 (CUSTOMER 1), 2,800 (CUSTOMER 2)

I reference some formula posted from Experts Exchange or Google but I got answer - 750 (250 X 3) , 3,600 (1,200 X 3), 2,100 (700 X 3) - each row of data multiply three times - when I put formula in "detail section" - If I Group By CUSTOMER and got 2,100 (700 X 3) for CUSTOMER 1 and 2,400 (800 X 3) for CUSTOMER 2 in Group Footer - Last row of data multiply three times.

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
How are you populating the "array" of data?

One way to do what you want is add a formula

If ({ItemField} in ["Item A", "Item C"]) then
{QtyField}
Else
0

You can then summarize that to get the totals.

mlmcc
0
Author Commented:
Michael,
Thank you for your quick response.

I got both array data from another subreport - They all have three elements - For example : Array ITEM (Item A, B, C) and Array QTY (250, 1200, 700)

I place two formula @Array in report Detail Section
---------------------------------------------------------------------------------------
@Array ITEM                                         @Array QTY
---------------------------------------------------------------------------------------

Report Preview shows

Item A                    250
Item B                  1200
Item C                    700

Maybe I did something wrong here, but in your formula - {ItemField},  ["Item A", "Item C"], {QtyField} - To me
they are all elements of Array I don't know how to do them with your expression - Where do I apply your formula in report?
0
Commented:
My formula assumed you were getting the data from the database and building the array.  How about using it in the subreport and returning the total you want along with the array.

If that doesn't work then you could write a formula like

``````WHilePrintingRecords;
Shared StringVar Array Item;
Shared NumberVar Array Qty;
Local NumberVar Total := 0;
Local NumberVar Index;

For Index := 1 to UBound(Item) do
If Item[Index] = "Item A" or Item[Index] = "Ïtem C" then
Total := Total + Qty[Index];

Total
``````

mlmcc
0
Commented:
How do I Sum Item A to Item C's  QTY for each CUSTOMER?

To clarify, you're not trying to total the quantities for _only_ Item A and Item C, correct?  The sentence above could be taken to mean "add the quantities for Item A and Item C together, and leave out Item B".  That's what mlmcc was trying to give you -- A total for just Item A and Item C.

But the totals that you posted indicate that you're looking for a total for all of the items, and  "Item A to Item C" was indicating a range.

In theory, you can just use Sum (QTY) in a formula to get the total of the elements in the QTY array.  Whether or not that will actually work will depend on exactly what's in the array, where you're trying to display the total, etc.

James
0
Author Commented:

I was trying to SUM all QTY for ITEM A, B and C (but it could be ITEM A, B, C, D, E, F, ...  because it is array and it all depends on how many elements it has in that array - sorry for the confusion - just make it simple we used three elements for both Array as example)

I tried SUM(Array QTY) but it returns 3 X last element  of Array QTY : 2100 (i.e. 3 X 700).

Let's assume Array ITEM[x],  Array QTY[y] and they all get data from different tables of database, the elements for each Array :  Array ITEM[x] --- (Item A, Item B, Item C) and Array[y] --- (250, 1200, 700)

Two formulas @GetArrayITEM and @GetArrayQTY are defined to get Array data then
I insert these two formulas in report "Detail" section and report preview shows:

Item A      250
Item B    1200
Item C      700

That's what I do so far - maybe I did it wrong so I couldn't get the correct number of SUM(@GetArrayQTY) here
0
Commented:
Just eliminate th etest

``````WHilePrintingRecords;
Shared StringVar Array Item;
Shared NumberVar Array Qty;
Local NumberVar Total := 0;
Local NumberVar Index;

For Index := 1 to UBound(Item) do
Total := Total + Qty[Index];

Total
``````

mlmcc
0
Author Commented:
Sorry Michael,

I did this formula before and I got 0 in total. (Where should I insert this formula?)
0
Author Commented:
got the last element in QTY X 3 - 2100 in Total
0
Commented:

mlmcc
0
Author Commented:
I used your formula with fixed elements (Shared StringVar Array Item := ["Item A", "Item B", "Item C"]; and Shared NumberVar Array Qty := [250,1200,700]; and got the right number in Total.

So it's back to my original assumption I did something wrong in Array (it was my first time use Array when in last posting you showed me how to use it). It seems the formula only get the last element of Array three times (right formula with questionable Array).

How do I upload the report to you? - the report output in PDF file? or report file itself?
0
Commented:
The report file itself

There is an ATTACH FILE link below the comment box.
Click that and follow the prompts.

mlmcc
0
Author Commented:
0
Commented:
I can't tell from the picture what is happening.  I really need to see the RPT file.

mlmcc
0
Author Commented:
Sorry, I thought I only can upload Valid: jpg, txt, xls, doc, bmp, png, more (50 MB max)
I didn't check more ...
Laydown-Print-Change.rpt
0
Commented:
You used SUM (@GetArrayQTY) in a post.  That's not what you want.  That's going to produce a total of the result from that formula (Maybe added once for each record or something.  I'm not sure).  Presumably you just want to total the elements in the array.  Sum can be used for that too.  That's what I meant by Sum (QTY) in my earlier post.

For example, you could create a formula similar to the following:

WhilePrintingRecords;
Shared NumberVar Array QTY;
Sum (QTY)

Wherever you put that formula, it should give you the total of the elements in the array at that point in the report.

James
0
Author Commented:
Thank you James,

I tried this formula as well it returns this number (last elements X 3) to me.
0
Commented:
Does the report have a subreport?

Why are you building the array of values?
You are creating an array that has n elements all are the same.

mlmcc
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Originally both Array data were from another subreport recently I merged two as one. I still using array to developed this subreport and forgot just simply get data from fields directly - It's all my fault - Sorry guys - till Michael asked me why I created the array to show data repeatedly I woke up from obsession of using array.

Thanks for all your help again - Thank you guys.
0
Commented:
Edit: Obviously I wrote all the stuff below before seeing your last post.  It sounds like you figured it out, but here it is anyway ...

The For loop below just fills the array with the value in estdiech-qty in the _current_ record.  You put the formula in the detail section, so when the report reads the next record, it refills the array with the value from that record (replacing the values from the previous record).  So, at the end, the array just contains PCQTYi copies of the value from the last record that the report read.

For Index := 1 to PCQTYi do
(
PCQTYArray[Index] := {estqtydiechanges1.estdiech-qty};
);

What is Maximum ({estqtydiechanges1.k-estdiech-seq}) ?

Whatever it is, you probably don't need it.

The way that you would usually save the values from a series of records would be to use a shared/global variable to keep track of which element the next value(s) should go in.  Something like this:

WhilePrintingRecords;
Shared NumberVar Array PCQTYArray;
Shared NumberVar index1;
index1 := index1 + 1;
ReDim Preserve PCQTYArray[index1];
PCQTYArray[index1] := {estqtydiechanges1.estdiech-qty}

Then, at the end, you could use a formula like the following to get the total for the array:

WhilePrintingRecords;
Shared NumberVar Array PCQTYArray;
Sum (PCQTYArray)

I would also have a formula in the report header that declared index1 and set it to 0.  As long as index1 is shared, I don't think that's required, but I like to do it.  And if you were actually using this to save the values for a group, instead of the entire report, you would use a formula like that in the group header to reset index1 to 0 for each new group (and empty out the arrays).

Having said all of that, arrays don't really seem necessary for the report that you posted, but you mentioned a subreport earlier, so I'm guessing that that was a simplified example.

James
0
Author Commented:
Thank you James

Your last post was really helpful but it was too late when I read it. From this discussion I learned a lot from both of you especially "Array" - you guys are real experts and all comments are useful and true enlightenment - Thank both of you.

p.s What is Maximum ({estqtydiechanges1.k-estdiech-seq}) ? - that's is number I used to double check totally number of ITEM or QTY - in this case is 3. (check FOR ... DO cycle)
0
Commented:
What is Maximum ({estqtydiechanges1.k-estdiech-seq}) ? - that's is number I used to double check totally number of ITEM or QTY - in this case is 3. (check FOR ... DO cycle)

I figured it was something like that.  That assumes that k-estdiech-seq will start at 1 and be incremented by 1 for each new record in that "set", with no skipped values.

But, IAC, it's simpler to just increment a counter for each new value.

James
0
Commented:

mlmcc
0
Author Commented:
Michael,

I didn't use an array but I will try yours and James later - currently I have deadline to meet so I just simply get data from fields directly since I merged two subreports as one and it contains all database fields I need -  sum(QTY_field) then divide by number of Step_Description (Process ID) - because you found my array outcome repeatedly in report so I think it will add up repeatedly in Sum(QTY_fields) as well - it will add up X times (X is number of Process ID) so divide by X will get ITEM A to ITEM C total correctly (only get one set of total not 3 times in this example).

James,

You're right, it is another way to get maximum number of ITEM or QTY (they show as pair) so it can double check the right number cycle of loops should run.
0
Commented:
You're right, it is another way to get maximum number of ITEM or QTY (they show as pair) so it can double check the right number cycle of loops should run.

Just to be clear, you shouldn't be looping at all.  When a formula references a field, it's only going to see the value from one record at a time, so your loop is just going to be putting multiple copies of the same value in the array.  Say you put the formula in the detail section, and the max value for k-estdiech-seq is 4.

The report would read the first record, and your loop would put the value from that record in the array 4 times.
Then it would read the next record, and put the value from that record in the array 4 times, replacing the value from the first record.
Then the same thing for the third record, and then the fourth record.

So, at the end, the array would contain the value from the last (fourth) record, repeated 4 times.

James
0
Author Commented:
yes that was the answer I got - the last element times three (is 3 in this simplified example and max value for k-estdiech-seq is 4 in uploaded report) - when I go back to use an array I will avoid this mistake.  Thank you James.
0
Commented:
You're welcome.

James
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.