Conditional Minimum/Maximum

[wesleycrusher]

I am trying to create a one page summary to show ranges of efficiencies for gas heaters. Here's a sample data set:

FlowRate GasType Effy
2.5 propane .72
2.4 natural .71
2.6 both .70
3.5 both .78
3.7 natural .77

I am grouping the results on a range of flow rates and want to display the associated range of efficiencies for each gas type. The report prints flow rate range, then natural gas efficiency range, then propane gas efficiency range.

I can successfully list the range of efficiencies without regard for fuel type by inserting a summary that gives min efficiency and placing a summary of max efficiency next to it. However, I just can't find a way to separate the fuel types. The problem I'm having is finding a way to include the efficiency in both the min/max results of propane and natural when it is listed in the database as both.

I don't think group selection formulas are the answer b/c it would filter out data I need to include. I've also tried this SQL statement and inserted it into the flow rate group footer (wehre everything is placed) but it returns the minimum efficiency regardless of the flow rate range grouping.

(SELECT MIN(energy_factor)
FROM directory_rwh
WHERE"directory_rwh"."gas_type" = 'natural' OR "directory_rwh"."gas_type" = 'both')

Any help is greatly appreciated. TY.

 

[lbass]

Your CR version?

-LB

 

[wesleycrusher]

My apologies, CR9.

I'm about to try creating 4 formulas (a min and max for each fuel type) that will dump the result in a global number. I'll then try to print the number in the flow rate group footer.

I hope it works but will appreciate additional suggestions.

 

[wesleycrusher]

I got it!

I did the 4 formula thing I described above. These are what I came up with for the natural gas, the propane are the same with the obvious changes:

numberVar NatGas_MinEffy:=100;
If ({directory_rwh.gas_type}="natural" or {directory_rwh.gas_type}="both")
Then NatGas_MinEF:={directory_rwh.energy_factor}
Else 100;

numberVar NatGas_MaxEF:=0;
If ({directory_rwh.gas_type}="natural" or {directory_rwh.gas_type}="both")
Then NatGas_MaxEF:={directory_rwh.energy_factor}
Else 0;

I then inserted a summary on each of the 4 formulas and placed it in the flow rate gropu footer. It worked out!

Sorry to trouble you but thanks for the kind intentions!

 

[wesleycrusher]

EDIT:

The first variable declaration above should be NatGas_MinEF instead of NatGas_MinEffy.