sub total
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14757
Printed Date: 20 May 2024 at 11:55pm
Topic: sub total
Posted By: Wouter
Subject: sub total
Date Posted: 24 Oct 2011 at 7:33am
Hallo,
I'm new with crystal reports and have a problem with the sub total of my report. We are using a btrieve database and crystal repots 2008.
In de database we have data about food recipes. I'm using the 2 tables which are linked with a join left outer. In the first table i have the recipenumber and their weights and in the other the base materials and weights which we are using in the recipes. When i select the recipenr and amount i get a total of 21,02 kg. That's correct
Recipenr weight
14525 chicken/onion 20,00
9675 rice/sauce 1,02
total 21,02
But when i select the base materials to the reports the total is changing incorrectly
Recipenr weight base materials in recipe weight
14525 chicken/onion 20,00 chicken 10
14525 chicken/onion 20,00 onion 5
14525 chicken/onion 20,00 jus 5
9675 rice/sauce 1,02 rice 1
9675 rice/sauce 1,02 sauce 0,02
total 62,04 Must stay 21,02
I tried to group the recipenr but everytime when i add the base materials to the report the total is changing.
Are there some tricks or formulas to count the weight of the recipenumber 1 time instead of copying it?
Thanks.
|
Replies:
Posted By: DBlank
Date Posted: 24 Oct 2011 at 10:39am
|
use variable fomrulas or Running Totals.
I prefer running totals
example:
name=weight_w_Ingredients (or wjhtaver you want)
field to summarize=weight
type=sum
evaluate=on change of field (select recipenr)
reset=never (or on change of group if you have multiple recipes in the report)
|
Posted By: Wouter
Date Posted: 25 Oct 2011 at 12:14am
|
Thnx, I used the running totals and it works. But i also want tot use the overal total (receipe16665 total weight 0.5 kg, sub.9855 total 107.53Kg) of the recipe and subrecipe in an other formula. Is this possible?
|
ArtikelNr |
Name |
RecNr |
Subrecept |
Weight |
RTotaal |
Subrecept |
Ingredients |
Weight |
RTotaal1 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
254421 |
25,00 |
25,00 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
558231 |
18,00 |
43,00 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
149561 |
10,00 |
53,00 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
429204 |
10,80 |
63,80 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
481358 |
1,40 |
65,20 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
910174 |
0,03 |
65,23 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
360086 |
3,80 |
69,03 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
373908 |
1,60 |
70,63 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
967184 |
0,06 |
70,69 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
252403 |
1,00 |
71,69 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
167190 |
2,00 |
73,69 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
150910 |
0,84 |
74,53 |
|
229293 |
GAMBA'S |
16665 |
9855 |
0,26 |
0,26 |
9855 |
99 |
33,00 |
107,53 |
|
229293 |
GAMBA'S |
16665 |
16594 |
0,24 |
0,50 |
16594 |
16595 |
99,92 |
99,92 |
|
229293 |
GAMBA'S |
16665 |
16594 |
0,24 |
0,50 |
16594 |
9582 |
5,06 |
104,98 |
|
Posted By: DBlank
Date Posted: 25 Oct 2011 at 3:29am
|
you cannot summarize a summary (e.g. Sum group level RT's) but you can create another Running Total that is the same as the group level summary except that the reset value is set to Never for a report total.
|
Posted By: Wouter
Date Posted: 25 Oct 2011 at 6:06am
I want tot calculate how much the total weight of the subrecipe is comparring the recept.
So for 9855: (16665) 0,5 / 107,53 = 0,0046
9582: (16665) 0,5 / 104,8 = 0,00477
The result of that i want to use as a factor for the weight of the ingredients.
Is this possible?
Or is it possible to convert a result to a fix value? X = "Rtotal of {subrecept}"
So x of 9855 = for example 107,53
|
Posted By: DBlank
Date Posted: 25 Oct 2011 at 8:38am
|
not sure I understand your data well enougth to answer your question exactly but
when using a running total or a variable formula the final total value does not exist until all rows have been read and it stops 'existing' after it hits a 'reset'. So if you want to use the 'final amount' in another calculation, that calculation formula has to be placed in the location of the report that allows for its reference to the RT to have read all of the approriate rows (e.g. a footer).
|
Posted By: Wouter
Date Posted: 04 Nov 2011 at 5:04am
|
Thnx for your help but it didn't work like i hoped to. I hoped that de subrecepture and their weight didn't multiply when i add the ingredients but staid unique value.
When i use the formule DISTINCTCOUNT the database recongize that there are 2 unique (subrecept) values. But is there also a formula which display which these 2 unique values are?
For Example 2 unique (subrecept) values 9855 and 16594. the wieght of these unique values is 0.26 and 0.24 so sum is 0.500.
thnx
|
|