Calculating Average in a Cross-tab 1

[tcgoth]

Crystal XI
SQL 2005

I am displaying the results of survey responses in the Detail Section of the Report and then summarizing those results using a Cross-tab in the Report Footer.

The survey responses that are being summarized are a mix of Scaled Scores (1-5) and Yes/No responses. Based upon the selection criteria there may be 50-60 DIFFERENT Scaled Score Questions and 30-40 DIFFERENT Yes/No questions that need to be Summarized separately.

Question #1 I am using the below formula to calculate the summarized Scaled Score. The problem is that when it comes to Calculating the Average in the Cross-tab, the Null Values are being included in the calculation.

For example, if the values in detail section were:

5
Null
Null
Null

The Average would calculate as 1.25 instead of 5.

Is there a way to write the formula to set the result to Null when appropriate so that it is not included in the calculation?

if {form_0164_customer_satisfaction_survey_section_detail.chk_excellent}='Y'then 5
else if {form_0164_customer_satisfaction_survey_section_detail.chk_good}='Y' then 4
else if {form_0164_customer_satisfaction_survey_section_detail.chk_satisfactory}='Y'then 3
else if {form_0164_customer_satisfaction_survey_section_detail.chk_poor}='Y'then 2
else if {form_0164_customer_satisfaction_survey_section_detail.chk_unsatisfactory}='Y'then 1

 

[lbass]

Create a formula {@null} by opening and saving a new formula without entering anything. Then change your formula to:

if isnull({form_0164_customer_satisfaction_survey_section_detail.chk_excellent}) and
isnull({form_0164_customer_satisfaction_survey_section_detail.chk_good}) and
{form_0164_customer_satisfaction_survey_section_detail.chk_satisfactory}) and
{form_0164_customer_satisfaction_survey_section_detail.chk_poor}) and
{form_0164_customer_satisfaction_survey_section_detail.chk_unsatisfactory}) then
tonumber({@null}) else

if {form_0164_customer_satisfaction_survey_section_detail.chk_excellent}='Y'then 5
else if {form_0164_customer_satisfaction_survey_section_detail.chk_good}='Y' then 4
else if {form_0164_customer_satisfaction_survey_section_detail.chk_satisfactory}='Y'then 3
else if {form_0164_customer_satisfaction_survey_section_detail.chk_poor}='Y'then 2
else if {form_0164_customer_satisfaction_survey_section_detail.chk_unsatisfactory}='Y'then 1

-LB

 

[tcgoth]

LB,

Tried exactly as you described but result in the Cross-tab is still evaluating to Average as if Nulls are being counted.

I created a new Formula @Null with nothing in it.

Changed code to:

if isnull form_0164_customer_satisfaction_survey_section_detail.chk_excellent}) and
isnull({form_0164_customer_satisfaction_survey_section_detail.chk_good}) and
isnull({form_0164_customer_satisfaction_survey_section_detail.chk_satisfactory}) and
isnull({form_0164_customer_satisfaction_survey_section_detail.chk_poor}) and
isnull({form_0164_customer_satisfaction_survey_section_detail.chk_unsatisfactory}) then
tonumber({@null}) else


if {form_0164_customer_satisfaction_survey_section_detail.chk_excellent}='Y'
then 5
else if {form_0164_customer_satisfaction_survey_section_detail.chk_good}='Y'
then 4
else if {form_0164_customer_satisfaction_survey_section_detail.chk_satisfactory}='Y'
then 3
else if {form_0164_customer_satisfaction_survey_section_detail.chk_poor}='Y'
then 2
else if {form_0164_customer_satisfaction_survey_section_detail.chk_unsatisfactory}='Y'
then 1

Any other thoughts or ideas? Thank you very much!

 

[lbass]

What fields are you using for your rows and columns? Did you simply insert an average on the above formula?

-LB

 

[lbass]

Can you verify that your fields are actually null rather than blank? Test using isnull() in the detail section.

-LB

 

[tcgoth]

LB,

You got me on that one. I tested the fields and they are in fact NOT Null Values. My apologies to you for not having my facts together.

I added your "Else {@null} to the end of the original formula and it nows works like a charm.

Thanks!