Print Page | Close Window

Cross tab averages

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9031
Printed Date: 04 May 2024 at 1:09am


Topic: Cross tab averages
Posted By: Apoc21
Subject: Cross tab averages
Date Posted: 03 Feb 2010 at 11:00am
Hi Guys.

I have a report in Crystal 2008

Im using a cross tab to display students with there marks.

on the end i have a SUM of all the marks and a average.

but where the trick comes in the mark is returned from the stored procedure as a string, because if a student did not write an exam it must display 'xx' instead of a '0' or '45' or whatever the mark may be.

so i created a formula field with the folowing code:

///////////////////////////////////////////////////////////
Local NumberVar MarkInt;

If NumericText({sp_Register_Reports;1.Mark}) Then
ToNumber({sp_Register_Reports;1.Mark});

///////////////////////////////////////////////////////////

And for the SUM it works great.

but now on the AVG it doesnt work correctly.

example there is supposed to print 21 guys on a list.
and their marks sum up to be 627, but 2 of those students did not write exam it throws the average out.

now : 627 / 21 = 30
should be : 627 / 19 = 33

How can i tell the avg to only COUNT the students that has a mark and not an 'xx' to use for the avg?

Please Help



Replies:
Posted By: kevlray
Date Posted: 03 Feb 2010 at 12:50pm
You would need to create your own counter (running total?) and have the formula 'NumericText({sp_Register_Reports;1.Mark})' for the Evaluate condition.
 
That should give you a true count.

Posted By: Apoc21
Date Posted: 03 Feb 2010 at 9:46pm
Originally posted by kevlray

You would need to create your own counter (running total?) and have the formula 'NumericText({sp_Register_Reports;1.Mark})' for the Evaluate condition.
 
That should give you a true count.


Thanks but how do I tell the average on the cross tab to take that running total field in to the calculation?

Posted By: Apoc21
Date Posted: 08 Feb 2010 at 9:49pm
bump

Posted By: kevlray
Date Posted: 09 Feb 2010 at 12:27pm
I wish I had a answer,  I am having issues with using a crosstab myself. 


Print Page | Close Window