Creating formula with specific Groups..

[gyfu]

Hi All..
I thought this is a simple problem but then I just realize that I cannot do it. Need to know if I am missing something here or is there a possible way of doing it.

Problem:

I have a summaries of group headers: Eg.

Grp 1 - 2000
Grp 2 - 4000
Grp 3 - 1000
Grp 4 - 5000

I need to create in the report footer the percentage of grp 1 to Grp 2. So in this example it would be,

2000/4000 *100 = 50%.

However, in the Crystal Report formula, I don't seem to be able to specify the summary of grp1 / summary of grp2.

Am I missing something here or does it seem that I have to do each total in a running total formula?

Please advise.

Thanks.

Using CR10


-- gyfu --
Crystal Report 2008
Microsoft SQL 2005

 

[Madawc]

Crystal allows group totals, but clears them for each new group, so you can't do a comparison.

You've got close to the right answer. Do a pair of running totals, using the same criteria used for group 1 or group 2, but not tied to the group as such. At the end of Group 2, you will have both figures and can use them for calculations using a formula field.

This would depend on the criteria being fixed. If they vary, then it would be harder. Maybe saving values using variables based on some count of how many groups there are.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[CoSpringsGuy]

do you only want the ratio for the first two records in the report footer? Sorry if I am just repeating your request just making sure I understand. If so, create this formula and place it in the group footer AND in the report footer
//
whileprintingrecords;
numbervar value;
numbervar out;
if groupnumber < 3 then
out := value/{@summaryformula}; //summaryformula is the formula you created
value := {@summaryformula};
out;

if you need the ratio of each group in relation to the previous group, remove the "if..." line and only place it in the group footer.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

You could do separate running totals that sum some field and in the evaluation section, use a formula:

{table.group} = "Group 1" //or Group 2

Reset never.

Then use the rts in a formula like this to be placed in the report footer:

{#Group1}%{#Group2}

-LB

 

[gyfu]

Thanks guys,

I think the running total is the way to go. Though hate the fact that I have a lot of running totals to do then..

-- gyfu --
Crystal Report 2008
Microsoft SQL 2005

 

[lbass]

What are the comparisons you need to do? You made it sound like there was only one comparison necessary. There might be a better way.

-LB

 

[gyfu]

Hi LBass...
Let me give you a more detailed explanation of what I need to do.

The report sample below shows 7 groups with the amount in the month of January. This will of course expand to the other months of the year. I used a summary field for the amount in January and the other months.

Group January

0 900
1 800
2 500
3 350
4 300
5 400
6 100
7 230


The grouping here is based off a category, which is category 1 to 7.

Now, below this section, I need to come up with the ratios between each category. The following ratios are:

Category Formula
0 - 1 800/900 * 100
1 - 2 500/800 * 100
2 - 3 350/500 * 100
3 - 4 300/350 * 100
4 - 5 400/300 * 100
5 - 6 100/400 * 100
6 - 7 230/100 * 100

Total Ratio : To multiply all values above.


So currently, now my only solution is to create separate running totals so that I can do the formula above.

What do you think?


Thanks for the effor, LBass

-- gyfu --
Crystal Report 2008
Microsoft SQL 2005

 

[lbass]

Are there always only these 8 group instances? Are there any outer groups? Do you mean you would have one column per month in a crosstab-like format, not in separate vertical groups? How did you create the January column? Are you using conditonal formulas like:

if month({table.date} = 1 then {table.amt}

Or do you have separate fields for each month? Please show the formula you used for the January group total.

-LB

 

[lbass]

On second thought, it might be simplest if you insert a subreport in the report footer and use cospringsguy solution (without the "if" line)--or replacing it with a conditional clause related to the month under consideration.

-LB

 

[gyfu]

Thanks LBass and CoSpringsGuy..

I think I do get the idea.. I will try it out.

-- gyfu --
Crystal Report 2008
Microsoft SQL 2005