Sum Group Total & Percentage

[idehen]

Hello Everyone,

Need some help please. Using CR 2008 and will like to calculate percentage.

Situation:

I have the follwoing info

Name Value Percentage
Joe 20
Ann 50
Prince 10

Total 80

Note that my Total = 80 is a formula because the individual values are formula and as such could not sum them up to get my totals but use a formula shown below to get the Total=80:

----------------------------------------
whileprintingrecords;
Numbervar x;
if not inrepeatedgroupheader then x := 0;


whileprintingrecords;
numbervar x := x + {@Value};


whileprintingrecords;
Numbervar x;
------------------------------------------

What i want to do now is calculate the percentage of the values based on the total, however because of my formula as shown above when i try to do something like:

(Value*100)/Total

i get an error message saying division by zero. How can i please work my percentage based on this formula (Total).

Any help appreciated.

Thanks

Sol

 

[idehen]

Any ideas or help guys. Hope everyone is not off for Christmas already. Any input or ideas will be appreciated.

 

[swiss2007]

Not sure, why you are using whileprintingrecords,

Just Right click and insert summary at your group level.
create this formula for percentage

if {value} = 0 then 0 else ({value}/sum({value},{your group}))*100
this will give you a number.

if you want to display in % then
if {value} = 0 then totext(0,1)+"%"
else totext({value}/
sum({value},{your group})*100,1)+"%

 

[idehen]

Hello Swiss.

Thanks for your response. The reason i am using whileprintingrecords is because the value uses something like (10*2) where my 2 is from a subreport which stores price history amount. The 2 is passed from Subreport to main report and so because of this can't do a sum of a formula field.

And as such can't use the formula you have advised. Appreciate the response.

Cheers

 

[lbass]

You will have trouble doing this since the total is not yet calculated in order to use for prior percentages and because your value is already making use of a subreport. Ordinarily I would say to save the report as a subreport so that you can pass the calculated total to the main report before the percentages are calculated--but you can't do that because you can't have a subreport nested within a subreport. The question then is whether you really need that original subreport, or is there another way to bring that value into the main report.

-LB

 

[idehen]

Hi Lbass,

Unfortunately, looks like i can't bring the value in the subreport into the main report directly, as it's a historic table for price or rates and adding it to the main report just cause alot of duplicates to my total value and also given that the Subreport returns price as at a date specified, and where there is not price on the date specified then return the most recent price in the table, it be hard running this as part of the main report.

Thanks for all your response. I might have to export the report out to excel and reuse again as part of the main report, so i can use the total Amount.

Again thanks and Have a Merry Christmas everyone.

 

[lbass]

If you don't have that many names in the name field, you could use variables to display the results in the report footer where you set up a variable specific to the name. Or you could use arrays to do the same thing if there were fewer than 1000 names. Then the total would be available for the percentages.

-LB

 

[idehen]

Can you please explain how to go about the variable, I'll like to give it a go just incase it might help.

Thanks again.

 

[lbass]

How many names are there?

-LB

 

[idehen]

I have about 8 Names in the field.

 

[lbass]

Create a formula and place it in the name group section:

whileprintingrecords;
stringvar array name;
numbervar array value;
numbervar i := i + 1;
numbervar j := distinctcount({table.name});
numbervar tot := tot + {@value}; //{@value} = your value formula
if i <= j then (
redim preserve name[j];
redim preserve value;
name := {table.name};
value := {@value})
);

Then create three formulas like this and place them next to each other in the report footer. Format each of these to "can grow":

//{@names}:
whileprintingrecords;
stringvar array name;
join(name,chr(13));

//{@values}:
whileprintingrecords;
numbervar array value;
stringvar dispvals;
local numbervar i;
local numbervar j := ubound(value);
for i := 1 to j do (
dispvals := dispvals + totext(value,0,"")+chr(13);
);
dispvals

//{@percents}:
whileprintingrecords;
numbervar array value;
stringvar disppct;
local numbervar i;
local numbervar j := ubound(value);
numbervar tot;
for i := 1 to j do (
disppct := disppct + totext(value%tot,1,"")+"%" + chr(13);
);
disppct;

-LB

 

[idehen]

Lbass, Thanks alot for your help. Have tried this and as you rightly said where there are not many names, this can be used. And it works.
But have been given most of the data and uploaded to the system and there are lots lots of names in the table. So won't be helpful unless i replicate the formula for every name.
I have however gone with the Excel option at the moment. I know it's slow but best alternative for now.

This is just incase anyone is reading this and going through same problem. All i have done is export my reporting data as above to excel and then use the excel as a table to workout my percentage.

Much Appreciated LBass and everyone who have contributed.

Apologies for the late response as i have been away on holiday and just got back. Cheers