Retrieve running total from outside group. 1

[HardJeans]

With the way the database is normalized, this is how the data is returned to Crystal.

Kingdom|Type|Appendage|# of|

Animal|Dog|Leg|4
Animal|Dog|Arm|0
Animal|Cat|Leg|4
Animal|Cat|Arm|0
Animal|Human|Leg|2
Animal|Human|Arm|2
Animal|Caterpillar|Leg|100
Animal|Caterpillar|Arm|0
Plant|Carrot|Leg|0
Plant|Carrot|Arm|0
Plant|Lettuce|Leg|0
Plant|Lettuce|Arm|0

And I want it to end up looking like this:

===============================================
Animal
Dog
Graph of Average Legs
dog: 4
Animal: 27.5
Overall: 18.3

Graph of Average Arms
dog: 0
Animal: .66
Overall: .33

Cat
same

Human
Graph of Average Legs
Human: 2
Animal: 27.5
overall: 18.3

Graph of Average Arms
Human: 2
Animal: .66
Overall: .33
etc...
===============================================

I could easily do it if it was just a sum, but I need to use a running total to separate the arms from the legs. Is there any way to accomplish this without having to run a subreport to get the average animal/plant, and overall through each grouping?

 

[HardJeans]

Sorry...forgot all about this...Crystal 11.

 

[kenhamady]

I think you should add a group on the appendage rather than using running totals. Then you can refer to the totals of the higher level groups by using 3 formulas like these:

Average ( {# of} , {type} ) // for Group 2

Average ( {# of} , {kingdon} ) // for Group 1

Average ( {# of} ) //for the overall

You can refer to these formulas in your chart.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[HardJeans]

Thanks for the response Ken. I've found your website very handy on many occasions.

I already have a group on the appendage:
Group1: Kingdom (animal/plant)
Group2: Type (dog/cat/human)
Group3: Appendage (Leg/Arm)

then for example dog leg chart does this:
average({# of}, {type}) -
outputs 2 (4legs+0arms/2 leg&arm records) when it should be 4 (4 legs/1 leg records)

average({# of}, {kingdom}) -
outputs 14 (112/8) instead of 27.5 (110/4)

average({# of}) -
outputs 9.333 (112 legs&arms /12 leg&arm records) instead of 18.333 (110 legs/6 leg records)
It should just be leg records when it's under a leg appendage.

Unless you meant group1 should be the appendage, and the others moved down. However, I'd like to keep the graph of Dog legs right next to Dog arms. Do you see my dilemma?

Here's a quick example of a report/data:
Report
Excel data

 

[kenhamady]

The problem with Running totals is you can't refer to higher levels because they are incomplete. Instead use two IF THEN formulas to separate arms from legs like this:

If {Appendage} = "Arms"
then {# of}
else {@null}

Then you can use these formulas in your summary functions to get the average of Arms/Legs from any level. Same idea as the running total but more flexible.

The {@null} formula is a separate formula that you save at first with just a zero, then you use it in the arms/legs formula, and after it is being used you delete the zero and save it empty. This way it will be truly null when the condition isn't met. Otherwise you would get zeros and that would lower your averages.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[HardJeans]

I can do the {@Null} on formulas that return text, but it seems to just returns zeroes when the formula otherwise returns numbers.

 

[kenhamady]

Make sure you don't have report options or the formula option set to 'convert nulls to default'. Also, make sure you deleted the zero from the @null formula. It is only there to set the initial data type of the formula to numeric.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[HardJeans]

I knew there was something I was missing. For some reason the formula option had the default values for nulls. Thanks Ken!