Cross Tab for Survey Summary

[tcgoth]

Crystal 11, SQL 2005

Background: I am trying to create a summary Report of Survey Information. There are multiple surveys of which each contains multiple sections and questions under each section. Questions can be 1 of 3 types: Scaled Score (1-5), Yes/No, or Verbatim. I am ignoring verbatim responses for this summary.

Desired Format of Output

All Detail Records Displayed In Details Section (already completed)

The following in Report Footer Section:

Section1 Name Provider1...Provider2...etc.
Q1 Description (Avg Scaled Score) or (% Yes for Question)
Q2 Description (Avg Scaled Score) or (% Yes for Question)

For example:

Destination Services Joe Cindy
How would you rate their service? 4.67 4.87
Were they available when needed? 92% 96%

Homesale Joe Cindy
Q1.
Q2..etc.

Questions:

1.) I can get close with Crystal Cross Tab but can't seem to format it exactly like I want. Section name drops to the side instead of being a "Header" above each group of questions.

2.) I'm struggling with how to display the appropriate calculation without displaying both (scaled score or the Yes/no %) - I want an average of responses for each specific question for scaled scores OR a % of "Yes" responses for each specific Yes/No question. Is there any way to write a formula that would work in the Crosstab?

3.) I am confused about how to create a % formula in the crosstab since there is not a summary operation for this. I created "Count Yes" and "Count Yes/No Total" fields but how do I put them in the Crosstab summary appropriately to get just a % to display?

4.) It semed like it might make more sense to do a Manual Crosstab - format and formaulas are much easier but I can't figure out how to get my full "Summary" Question set to generate in the footer. When I place the "question text" field in the footer it only generates one of the questions. Am I just missing something or will a Manual cross-tab not work in this case?

Thanks for any assistance that can be provided!

 

[lbass]

Since you don't like the crosstab format (issue #1), you might be better off with a manual crosstab. You can insert a subreport in the report footer, and then create the manual crosstab using groups based on your crosstab "row" fields, and suppressing the detail section. For the different summaries, you could either use an if/then to display alternative summaries, like:

if {table.question} in [1,3,5] then
average({table.scalescore},{table.question}) else
if {table.question} in [2,4,6] then
sum({@Yes},{table.question})%count({table.question},{table.question})

...where {@Yes} is a formula:

if {table.yes/no} = "Yes" then 1

Or you could insert/create separate summaries and conditionally suppress them based on the question.

-LB