Display Calculated Results of Summaries in Group Footer

Please excuse my beginners level ... I'm eager to learn.
Using Crystal Reports 2011

I have a Database consisting of Tables 'Article' and Sales'
Article: (Amongst other Fields):
ArticleNo, Article.Name, Article.Price, Article.Factor
Sales:
Sales.ArticleNo, Sales.Quantity,Sales.StaffNo

I need a report for the calculation of Staff Bonuses:
Assume the following detail records in Sales:

11;2;14
11;3;9
11;1;4
12;1;9
....
And the Article Tables looks like:
11;Adapter;1;100
12;USB Cable;0.4;250

The report I am trying to create should now Group by StaffNo First, and then by ArticleNo

It should Display:
(GH1): StaffNo  StaffName
(GH2): ArticleNo  ArticleName
(Detail):
Quantity
(GF2): (SUM of Quantity)*(Article.Factor)   (SUM of (Quantity*Article.Factor)*Article.Price

Example:
GH1:
4 George
GH2:
11 Adapter
Detail:
1
1
2
1
5
GF2:
10  1000

Next:
GH2:
12 USB Cable
Detail:
11
2
17
3
6
GF2:
16 (REM: Rounded result of 39*0.4) 4000

How do I proceed?
ChiaYossarianAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

James0628Commented:
You said that the first calculation in GF2 is (SUM of Quantity)*(Article.Factor), but, looking at your examples, it looks like it's actually (SUM of Quantity)*(Article.Price).

 Either way, it looks like you can just use simple formulas.  Create a formula like the following under Formula Fields in the Field Explorer (name it whatever you like):

{Sales.Quantity} * {Article.Factor}

 Or, if I was right earlier:

{Sales.Quantity} * {Article.Price}

 Put that formula in the detail section and then you can have CR do a summary on that formula for group 2.

 Then create another formula for the other total and do a summary on that:

{Sales.Quantity} * {Article.Price} * {Article.Factor}

 James
1
ChiaYossarianAuthor Commented:
Thanks, that was my first thought too, but this doesnt work:

The Summary over Adjusted Amount will now give me the sum over all amounts in the Group, so in case I have Details like this example:
ArticleName    Factor   Price             (Group Header)
Screws              1            100
Quantity      Adj. Qty      Adj. Amt.    (The latter two are derived from Formulas like you suggested) (Details Section Header)
1                        1            100
3                        3            300
2                        2            200
--------------------------------------
6                        6            600      (Group Footer) Hre it looks ok as the Factor here is 1

But look at another example with Factor 0.5:

ArticleName    Factor   Price             (Group Header)
Bolt                   0.5          300
Quantity      Adj. Qty      Adj. Amt.    (THe latter two are derived from Formulas like you suggested) (Details Section Header)
1                        0.5          150
3                        1.5          450
2                        1             300
--------------------------------------
6                        2             900      (Group Footer)

What I would need here are the following results in summary:

ArticleName    Factor   Price             (Group Header)
Bolts                 0.5         300
Quantity      Adj. Qty      Adj. Amt.    (THe latter two are derived from Formulas like you suggested) (Details Section Header)
1                        1            300     (I will have to first create a Rounding here, so 0.5 will display as 1 here, etc ....)
3                        2            600     The Summary AdjAmt needs to be calculated from the Article.Price Value multiplied by (rounded!) AdjQty!
2                        1            300
--------------------------------------
6                        4          1200      (Group Footer)

Any idea how to accomplish that?
0
James0628Commented:
Is the issue simply that you need the adjusted quantity to be rounded?  If so, try the Round function:

 Adjusted Quantity:
Round ({Sales.Quantity} * {Article.Factor})

 Adjusted Amount:
Round ({Sales.Quantity} * {Article.Factor}) * {Article.Price}


 FYI, you can add an argument to the Round function to specify how many decimal places to round to (eg. Round (1.234, 2) would give you 1.23).  The default is 0 places, so you don't need that argument in the formulas above.

 James
1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ChiaYossarianAuthor Commented:
No, actually I need the rounded sum instead of the sum of the rounded values.I wonder if it is possible to create formulas with the summaries in the group footers? That way I could first round the sum of the Adjusted QUantities, and then somehow calculate the sum of the Adjusted Amounts as a multiplication of the Factor with the sum of adjusted amounts.
0
James0628Commented:
I thought that the lines in your previous post (eg. with 1, 0.5 and 150) were details.  If so, then rounding the adjusted quantity on those details should give you the results that you wanted.

1                        0.5          150
3                        1.5          450
2                        1             300
--------------------------------------
6                        2             900      (Group Footer)

 becomes

1                        1            300
3                        2            600    
2                        1            300
--------------------------------------
6                        4          1200      (Group Footer)


 But, to answer your question, you can use a summary in a formula.  To get the total for the entire report, use

Sum ({your field or formula})

 To get a group total, add the group field to the function

Sum ({your field or formula}, {group field})

 So, rounding the total adjusted quantity for the ArticleNo group would be something like:

Round (Sum ({adjusted quantity formula}, {Article.ArticleNo}))

 Add the # of decimal places to the Round function if you don't want an integer result.

 James
1
ChiaYossarianAuthor Commented:
That was the first missing link, James!
I didnt know that the syntax SUM(@FORMULA,Group) gives me summaries per group, that was surely helpful, and I now managed the "Details" Part of my report in No Time.

I use Article.Number as base for my one Group, and will suppress the details section later with formatting, as all the results I require are the summaries per Article.

My formulas are now:
@AdjustedSumQuantity:
ROUND (Sum ({Quantity}, {ArticleNo})*{Factor})
@AdjustedSumAmount:
ROUND({@AdjustedSumQuantity}*{Article.SellPrice})

That gives me correct results in all cases I checked, with various "Factor" values.
What I cant achieve now is a report footer total over all AdjustedSumAmount Values for the report. When I attempt to create a SUM with  this Formulas, I receive an Error: This Field cannot be summarized.
The AdjustedSumQuantity can be summarized, although it displays with fractions, so I assume there might be Rounding Errors lurking if I ROUND this Field, as it seems to summarize the unrounded calculated results and not the rounded ones that are displayed.

In other words:
I need the sum of AdjustedSumQuantity and the Sum of AdjustedSumAmount over all Groups at the report footer.
0
James0628Commented:
Are you sure that you can summarize @AdjustedSumQuantity?  You should not be able to, since it uses Sum (according to your post).

  I actually started to mention this in my last post, but I didn't know if it was going to be an issue in this case, so I decided not to get into it and possibly cloud the issue.

 CR won't do a summary on a summary, so if you use Sum (or any other "summary" function, like Count or Maximum) in a formula, CR will not do a summary on that formula.

 Yes, it does seem like summarizing the unrounded values and rounding that summary would produce different results.  I think you'll need to use variables to calculate the summaries "manually".

 You're calculating @AdjustedSumQuantity and @AdjustedSumAmount for each group, correct?  And then you want report totals for those two group totals?

 Create a formula like the following (call it whatever you like) and put it in the report header, to declare the variables:

WhilePrintingRecords;
Global NumberVar TotalAdjustedSumQuantity;
Global NumberVar TotalAdjustedSumAmount;
""

Open in new window


 The "" at the end is just so that the formula doesn't produce any visible output on the report.  You could also suppress that field, or the section that the field is in.  The formula should still be evaluated.

 Change your AdjustedSumQuantity formula as follows, to add the group total to the variable, and then display the group total:

WhilePrintingRecords;
Global NumberVar TotalAdjustedSumQuantity;
TotalAdjustedSumQuantity := TotalAdjustedSumQuantity +
 ROUND (Sum ({Quantity}, {ArticleNo})*{Factor});
ROUND (Sum ({Quantity}, {ArticleNo})*{Factor})

Open in new window


 Of course you'll need to use your actual field names.  I just copied the formula from your last post.

 Change your AdjustedSumAmount formula as follows, to add the group total to the variable, and then display the group total:

WhilePrintingRecords;
Global NumberVar TotalAdjustedSumAmount;
TotalAdjustedSumAmount := TotalAdjustedSumAmount +
 ROUND({@AdjustedSumQuantity}*{Article.SellPrice});
ROUND({@AdjustedSumQuantity}*{Article.SellPrice})

Open in new window


 Don't worry about the formula referencing the @AdjustedSumQuantity formula twice.  @AdjustedSumQuantity should only be evaluated once (IOW, TotalAdjustedSumQuantity won't be updated twice).  CR should just use the existing result when the formula is referenced the second time.

 Then create formulas to output those variables in the report footer:

WhilePrintingRecords;
Global NumberVar TotalAdjustedSumQuantity;
TotalAdjustedSumQuantity

Open in new window


WhilePrintingRecords;
Global NumberVar TotalAdjustedSumAmount;
TotalAdjustedSumAmount

Open in new window



 FYI, if you were using a variable to calculate a group total, instead of a report total, then you would need to "reset" the variable for each group.  For example, if AdjustedSumQuantity was a total for group 2 and you wanted a total for group 1, then, in addition to having a formula that added the group 2 totals to the variable, you would have a formula like the following, typically in the group 1 header, to reset the variable for each group 1.

WhilePrintingRecords;
Global NumberVar Group1AdjustedSumQuantity;
Group1AdjustedSumQuantity := 0;
""

Open in new window


 Just to be clear, you don't need to do that for a report total.  I just thought I'd throw that in as a bonus tip.  :-)

 James
1

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
ChiaYossarianAuthor Commented:
Thanks; that was a big help!
I was now able to finalize the report; next step for me is learning to understand what I just did (I admit that on some stage I just copied and adapted your formulas without knowing what some lines actually do ....)
0
James0628Commented:
You're welcome.

 FWIW, if you have any questions about those formulas, just ask.

 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
Databases

From novice to tech pro — start learning today.