| Author |
Message |
jackws123
Newbie
Joined: 07 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 5
|
   Topic: Summary and Grand Total
Posted: 07 Feb 2008 at 8:46am |
|
I have a field salary. I also have three level of groupby. i have a field in the details section, which is members/sum(salary). How can i achieve this. When the reports prints the first record it calculates the sum only upto the field. I want the entire sum of the salary field to be available for use in the formula in the details section. and in the groupby footer section too.
Please some one help
|
 IP Logged |
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 07 Feb 2008 at 12:25pm |
For sum of Salary, use the Sum() function with the grouping field in it. This returns the sum for the whole group.
Sum({table.salary}, {table.groupfield})I cover the details of sorting and grouping data in Chapter 3 of my book Crystal Reports Encyclopedia.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
jackws123
Newbie
Joined: 07 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 5
|
Posted: 07 Feb 2008 at 3:33pm |
|
My problem is : i have a salary field. I have a field next to salary which is a formula field: salary/sum(salary). This sum is the grand total of the salary field from the data. say there five records in the details section, salary values are 100,200,300,400 and 500. The total for salary is 1500(100+200+300+400+500). But when displaying row 1 ie,100 the total is calculated as 100 only so my formula 100/100 gives 1. for the next row ie 200, the total is 300 (100+200) and my formula returns 200/300 which is wrong. In all the rows i want the formula as 100/1500 , 200/1500,300/1500 , 400/1500 and 500/1500. and not the total upto the current field
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 07 Feb 2008 at 4:17pm |
|
Did you try the formula in your Details section? Put it in there and see if it returns the sub-total for that group (e.g. 1500 in your example). It should. Once you get that working, use it in your formula where you divide the current salary by the subtotal.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
jackws123
Newbie
Joined: 07 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 5
|
Posted: 07 Feb 2008 at 5:03pm |
|
it works the same way. i dont want the group total . i want the grand total.
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 07 Feb 2008 at 9:07pm |
I see. I was totally mis-reading the question. In that case you will still need to use a group, but you'll have to use a fake group and make it the first group level. Then use the group formula I showed in you in the previous post to get the subtotal by group (which, in this case, will also be the grand total). The fake group formula is:
WhileReadingRecords;
"";
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
aspxkid
Newbie
Joined: 07 Feb 2008
Online Status: Offline
Posts: 10
|
Posted: 07 Feb 2008 at 10:10pm |
i too have a similar problem . I have a sales field. I have the grand total of the sales field in the report footer. I have three groupby levels.
I want the total sales in each groupby footer to be divided by the grand total like : totalsalesgrpby3/grandtotalsales to be placced in grpbyfooter 3 section and
totalsalesgrpby2/grandtotalsales to be placed in in grpbyfooter 2 section and
totalsalesgrpby1/grandtotalsales to be placed in grpbyfooter1 section
|
|
IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 08 Feb 2008 at 5:36am |
|
It's pretty much the same solution.
@Grp3Fraction:
SUM({MyReport.Salary},{MyReport.Group3Name})/SUM({MyReport.Salary})
Also, as a possibly easier solution, when you add the summary to the group footer, you do have the option to display it as a percentage of the grand total. Depending on your needs, this may give you want you want.
|
|
IP Logged |
|
aspxkid
Newbie
Joined: 07 Feb 2008
Online Status: Offline
Posts: 10
|
Posted: 08 Feb 2008 at 5:47am |
The sum is caluated only for that group. I want the groupsum divided by grand total always.
say
grpftrsum = 1000
grpftrsum = 1500
grpftrsum = 1200
and total sum = 3700
I want a field in the footer as grpftrsum = 1000 required : 1000/3700
grpftrsum = 1500 required : 1500/3700
grpftrsum = 1200: 1200/3700.
but the value of 3700 is not calculated in the footer. it calculates only till the record it sees.
|
|
IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 08 Feb 2008 at 10:01am |
|
Huh. It should use the grand total, as written. Try putting WhilePrintingRecords; at the beginning of the formula.
Did you also try doing it with the Show As Percentage option of the summary? Did that not do what you want?
|
|
IP Logged |
|
|
|
