Calculate without using domain for sum, avg, stdev 2

[SidCharming]

I am working with reports, and find that using the domain syntax (dsum, davg, dstdev, dmin...) cause the report to take extra time to open. If I setup my report to contain the information I need(report Specific SQL clause), how do I perform an average of an integer field where the recordset has a report field that is x?

example:

<rptDemographics>
Report PatientID Height
x 001-001 63
008-005 50
x 010-002 60

where the sum would be based on the 2 x(ed) patients.

Normally I would do is call an external query (outside the local report generated one):

davg(&quot;[height]&quot;,&quot;tblPatientInfo&quot;,&quot;[report]='x'&quot


Sid from Minnesota

 

[PaulBricker]

I think you would use the expression
=Avg([Height])
in the control source for your textbox. It would have to be calculated in a Group or Report Footer but that should do it. You have Functions that do just about everything a Domain Function does
DSum()
Sum()
DAvg()
Avg()
etc.


Paul

 

[SidCharming]

I have to do this in the header, and it is a single page report that the output looks like this:

37.5 +/- 32.7 (14)
[0.03, 86.0]

<break out of what these are>

[tt][mean] +/- [StDeviation] ([N])
[ [min], [max] ][/tt]

So I need to calculate the davg, dstdev, dcount, dmin and dmax. but I don't want to use the domain function because my query only exists in the report. I really want to use the values I have derived specifically for this report in the embedded SQL statement.

I hope this helps.



Sid from Minnesota

 

[PaulBricker]

You can use an expressions like this in the Report Header.

=Avg(IIf(Report = x,[Height]))
=Sum(IIf(Report = x,[Height]))

See if that helps.

Paul

 

[SidCharming]

Thanks Paul,

Tomorrow I will try it with my reports. Looks good from here...

Thanks!



Sid from Minnesota

 

[SidCharming]

Sweet!

Paul you earned a star for that solution...

Thank you!!



Sid from Minnesota

 

[SidCharming]

I updated my reports and built new ones using this technique. I am excited to see how fast my reports generate now!! Cha-CHING!



Sid from Minnesota

 

[PaulBricker]

Awesome! Nobody likes to see their apps crawl along.
Thanks for the star.

Paul