How to Sum "detail section" array data?

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

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?

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

Select allOpen in new window

mlmcc

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

Thanks for both of your comments.

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

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

Select allOpen in new window

mlmcc

Sorry Michael,

   I did this formula before and I got 0 in total. (Where should I insert this formula?)

got the last element in QTY X 3 - 2100 in Total

Can you upload the report?

mlmcc

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?

The report file itself

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

mlmcc

SAP-Crystal-Reports---14080065.pdf
Detail-Section.rtf

I can't tell from the picture what is happening.  I really need to see the RPT file.

mlmcc

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

Thank you James,

I tried this formula as well it returns this number (last elements X 3) to me.

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.

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

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)

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

What was your final formula?

mlmcc

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.

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

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.

You're welcome.

 James