Print Page | Close Window

% of group total

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
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=21133
Printed Date: 27 Apr 2024 at 3:00pm


Topic: % of group total
Posted By: natali
Subject: % of group total
Date Posted: 24 Oct 2014 at 9:45am
I need to calculate % of one group over another of the same field. How can I do it?

Example: Total Salaries 5000
        Total Benefits 2000
I need to find % of benefits over salaries.

Thank you
Natalie


Replies:
Posted By: DBlank
Date Posted: 24 Oct 2014 at 10:37am
sum(benefits,group)%sum(salary,group)

Posted By: natali
Date Posted: 27 Oct 2014 at 5:32am
Thank you. I have tried this before but it doesn't work.
I get a message "Division by zero". Sum of each group is from one field. May be there is another way to do it?

Posted By: z9962
Date Posted: 27 Oct 2014 at 6:12am
IF sum(salary,group)<> 0 then
sum(benefits,group)%sum(salary,group)

Posted By: natali
Date Posted: 27 Oct 2014 at 10:10am
Thank you. I tried, but for some reason the result is zero for everything.

Posted By: DBlank
Date Posted: 27 Oct 2014 at 10:16am
what exactly is the formula you used that got the division by zero error and then exaclty the one that is only giving you the result of 0

Posted By: natali
Date Posted: 27 Oct 2014 at 10:30am
First I created groups in the payment field.
Then I created running total fields for benefits and for salaries separately.
I used formula:
total benefits: Sum of transactionsEntryValue/total salaries: Sum of transactionsEntryValue
and I got error message.
When I used the same formula but with If total benefits<>0 then I get 0.

Posted By: DBlank
Date Posted: 27 Oct 2014 at 10:34am
is this a crystal running total field or variable formulas?

Posted By: natali
Date Posted: 27 Oct 2014 at 10:51am
I am not sure what this is.
I used Running Total Fields in Field Explorer.
I choose field to summarize (entryValue), then to evaluate I used formula: if group is salary then true.
And, reset on change of group.
So, I got totals for each group.

Posted By: DBlank
Date Posted: 27 Oct 2014 at 11:02am
1. running totals (RTs) only work in footers (or detail sections) so if you placed your  % formula in a header it likely would result in 0 .
2. resetting on each group makes the value go back to 0.
 
Your set up:
sounds like you have one dollar vlaue field that is defined by a type field (e.g. salary or benefit)
you grouped on the 'type' field
you created a RT to sum the dollar value field.
IN each group footer it dispaly the correct value for the benefits group and the slary group.
Now you are trying to show a %.
The % is zero.
Is this correct?
 

Posted By: natali
Date Posted: 27 Oct 2014 at 11:06am
I placed my % formula in the footer.
The rest is correct.

Posted By: DBlank
Date Posted: 27 Oct 2014 at 11:13am
INstead of using the grouping, create two formula to get your values to sum
then you have two reults to use for the %
//Salary
if table.typefield = 'salary' then table.valuefield else 0
 
//benefits
if table.typefield = 'benefits' then table.valuefield else 0
 
if sum(@salary) <> 0 then
sum(@benefits)%Sum(@salary)
 
if you have another level of grouping in that then you can do the sum of each of the formulas at that group level (like summaries at an organizational team or an employee group)

Posted By: natali
Date Posted: 28 Oct 2014 at 3:30am
Thank you very much for your help.
I will try this. Hopefully it works.
Natalie.


Print Page | Close Window