Report Design: Summary and Grand Total

Print Page | Close Window

Summary and Grand Total

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2257
Printed Date: 20 Apr 2024 at 1:28am

Topic: Summary and Grand Total

Posted By: jackws123
Subject: Summary and Grand Total
Date 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

Replies:

Posted By: BrianBischof
Date 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 http://www.amazon.com/exec/obidos/ASIN/0974953601/bischofsystem-20 - 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>

Posted By: jackws123
Date 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

Posted By: BrianBischof
Date 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>

Posted By: jackws123
Date Posted: 07 Feb 2008 at 5:03pm

it works the same way. i dont want the group total . i want the grand total.

Posted By: BrianBischof
Date 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>

Posted By: aspxkid
Date 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

Posted By: Lugh
Date 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.

Posted By: aspxkid
Date 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.

Posted By: Lugh
Date 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?

Posted By: aspxkid
Date Posted: 08 Feb 2008 at 10:14pm

Thanks that worked! show as percentage of grand total is the correct soulion. I have another problem. I have three levels of group. I want each grp to start in a new page. A group can span for more than a page. if the group end in a page the footer of the prv grp should also be in the same page

Plase refer this chart:

G1 Hdr
G2a Hdr
       G3a Hdr
               G3a Dtls
         G3a Ftr
<new Page>
       G3b Hdr
             G3b Dtls
       G3b Ftr
<new page>
        G3c Hdr
              G3c Dtls
         G3c Ftr
G2a Ftr
<new page>
   G2b Hdr
       G3a Hdr
           G3a Dtls
     G3a Ftr
   G2b Ftr
G1 Ftr
<new page>
So any grp should start in a new page . but if a prev level group ends the footer of the prev level group should reside in the same page and lower lever gorup

Print Page | Close Window