Solved

How to Sum "detail section" array data?

Posted on 2014-09-19
26
485 Views
Last Modified: 2014-09-23
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.

Please help
0
Comment
Question by:City_Of_Industry
  • 12
  • 8
  • 6
26 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40333397
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 Comment

by:City_Of_Industry
ID: 40333728
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40333970
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

Open in new window


mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 40334265
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 Comment

by:City_Of_Industry
ID: 40336872
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40337053
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

Open in new window


mlmcc
0
 

Author Comment

by:City_Of_Industry
ID: 40337141
Sorry Michael,

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

Author Comment

by:City_Of_Industry
ID: 40337225
got the last element in QTY X 3 - 2100 in Total
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40337244
Can you upload the report?

mlmcc
0
 

Author Comment

by:City_Of_Industry
ID: 40337301
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40337317
The report file itself

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

mlmcc
0
 

Author Comment

by:City_Of_Industry
ID: 40337414
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40337835
I can't tell from the picture what is happening.  I really need to see the RPT file.

mlmcc
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:City_Of_Industry
ID: 40337854
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 50 total points
ID: 40337856
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 Comment

by:City_Of_Industry
ID: 40337876
Thank you James,

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

Accepted Solution

by:
mlmcc earned 450 total points
ID: 40337905
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
 

Author Comment

by:City_Of_Industry
ID: 40338054
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
 
LVL 34

Expert Comment

by:James0628
ID: 40338059
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 Comment

by:City_Of_Industry
ID: 40338075
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
 
LVL 34

Expert Comment

by:James0628
ID: 40338154
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40338241
What was your final formula?

mlmcc
0
 

Author Comment

by:City_Of_Industry
ID: 40339476
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
 
LVL 34

Expert Comment

by:James0628
ID: 40339532
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 Comment

by:City_Of_Industry
ID: 40339708
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
 
LVL 34

Expert Comment

by:James0628
ID: 40339800
You're welcome.

 James
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now