Crystal 8 Cross-tab & field > 254 char 1

[tcgoth]

Crystal 8.0
SQL 2000

Two problems that I need help with. I have reviewed all prior posts and cannot find a solution. I should note that we are moving to Crystal 11 soon but that does not help my problem now!

1.) Dealing with field lengths greater than 254 characters. I am trying to display Verbatim Survey Responses. I tried to write a SQL view to break the field into chunks of 254 characters and bring them into crystal to join them back together - but Crystal still will not recognize any field that goes beyond the initial 254. Any other approaches I can use?

2.) As I mentioned I am tring to display the results of a survey in the report using a Cross-tab.

I want the data to display like this:

Section 1 Section 2
q1 q2 q3.... Q1 Q2 Q3
Joe Blow 5 3 5 4 3 4
John Doe 4 5 3 5 4 5

A.) I think I have the structure set-up Ok but the data always has to be summarized. Is there anyway to get around using a "Summary" function in a cross-tab in 8.0?

There are 40+ questions so I don't relish the idea of having to do a manual cross-tab but maybe that is the only way?

Not sure if pertinent but all of the responses are in separate datafields - 5 Scaled Scores from Excellent to Unsatisfactory (i.e. s1.chk_excellent, s1.chk_satisfactory, etc., 2 fields for Yes/No questions (s1.chk_yes, s1.chk_no), and 1 verbatim response (s1.verbatim). I wrote a Formula Field to calculate the responses to a numerical value.

Thanks for any assistance!

 

[lbass]

1) You can use SQL expressions to break the comments into substrings. If they are memo fields, you will have to manually type in the field name. Go to field explorer->SQL expression->new and enter either:

substr(table.`comments`,1,254)

Or:

{fn substring(table.`comments`,1,254)}

The numbers represent the starting position and the number of characaters, so the next segment would be:

substr(table.`comments`,255,254)

Check database->Show SQL Query to see how you should punctuate your field in the SQL expression area.

2) How are you summarizing the responses per question? Please show the content of your formula. I'm guessing it looks like this:

if s1.chk_excellent = 'Y' then //or true?
5 else
if s1.chk_satisfactory = 'Y' then
4 else //etc.

Then all you need to do is insert a maximum as your summary on this formula.

-LB

 

[tcgoth]

LB,

Thanks for the quick response. Here is the code that I am using for translating the fields to numerical values in the report. I changed to "Maximum" but now it appears that it might be evaluating all questions in that Section and displaying that Max (rather than evaluating each question independently.) I currently have "Section Name" and "Question Number" in the columns of the cross-tab.

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
else if {form_0164_customer_satisfaction_survey_section_detail.chk_yes} = 'Y' then 5
else if {form_0164_customer_satisfaction_survey_section_detail.chk_no} = 'Y' then 1
else 0

 

[tcgoth]

LB,

After I have created the SQL Query Expressions how do I bring them back together? I tried adding them to a new formula but I get the error "A string can be at most 254 characters."

Here is the formula where each expression represents a 254 character segment of the verbatim response field.

{%QResponse}+{%QResponse2}+{%QResponse3}+{%QResponse4}+{%QResponse5}+{%QResponse6}+{%QResponse7}+{%QResponse8}

 

[Madawc]

You could create a text box and put the 254-long fields into it. Text boxs are unlimited.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I forgot to mention that you should then drop the SQL expressions into a text box to bring them together.

The maximum would work only at the question cell level--as long as you have {table.person} as the row, and {table.question} as your second column field. In other words, the results would be correct per question, but not if you are showing a summary at the section level. If you want to summary across people, what kind of summary do you want? If there is only one response per question per person, then you should also be able to use a sum or an average as the summary.

-LB