Minimizing Responses to One Line 1

[Oglesbay]

After a few years of not being able to use Crystal (different job), I was thrust back into Crystal and was given a report to do. I'm not sure how to describe what I'm looking to do so I'm going to try to recreate the problem below.


Example Data
**********

Microsoft Excel
Chad Oglesbay
Question 1 Agree
Question 2 Strongly Agree
Question 3 Neutral
Kevin Schmitt
Question 1 Strongly Agree
Question 2 Agree
Question 3 Strongly Agree

*********

There could be many employees in a class and all will follow the same format. Basically what I would like to do to minimize the details in my report and to create a formula that will look to see what the answer is in question 1 and put a corresponding number next to the name (i.e. Strongly Agree=5, Agree=4, etc...) and so forth for each question. This is what I would like to have the report look like:

Microsoft Excel
Chad Oglesbay 4 5 3
Kevin Schmidtt 5 4 5
Ken Brantingham 5 4 4

Can any of you experts point me in the right direction in trying to get the response I'm looking for?

 

[lbass]

Assuming that Question is a column with multiple instances, you should be able to insert a crosstab that uses Name as the row, Question as the column, and a formula as the summary field:

select {table.answer}
case "Strongly Agree" : 5
case "Agree" : 4
case "Neutral" : 3
case "Disagree" : 2
case "Strongly Disagree" : 1

-LB

 

[Oglesbay]

lbass, that is pretty close to what I'm looking for. Thanks! I found that I can get rid of the totals on the top and left, but is there a way to get rid of the Grand Totals on the bottom and right? I do not want any totals at all on this cross-tab (it just wouldn't make sense in this report)

 

[lbass]

On the customize style tab you can suppress row totals and/or suppress column totals.

-LB

 

[Oglesbay]

Is there a way to sort or arrange the fields in the Summarized Fields section of the Crosstab table?

 

[lbass]

Can you explain more what you mean?

-LB

 

[Oglesbay]

In the Cross-tab there are three responses and I'll just label them response 1, 2 and 3. The order that they appear in the croos-tab is 1-3-2 (because that is the alphabetical sort.) Is there a way I can specify the order I want the responses in?

 

[lbass]

Are you referring to a row or column in the crosstab? Is so, in the crosstab expert, highlight the field->group options and select "specified order" and then order the instances as you wish.

-LB

 

[Oglesbay]

Yeah, right after I posted that I went back and I must have been blind cause I knew I could do that and I jsut didn't see it earlier. Thanks lbass.

 

[Oglesbay]

Going back to my original question, and your first post lbass, what if I also wanted to show data like this:

Microsoft Excel
Chad Oglesbay
Question 1 Agree
Question 2 Strongly Agree
Question 3 Neutral
Comment 1 The class was amazing and....
Kevin Schmitt
Question 1 Strongly Agree
Question 2 Agree
Question 3 Strongly Agree
Comment 1 One thing I would do is....

You can see that I added another question called "Comment" where the answers will never be the same. lbass, the formula you put in your first post worked wonderfully originally but am wondering if there is something I can do to get the comments to show up? Can you help?

 

[lbass]

Change the summary formula to:

if {table.question} = "Comment" then
{table.answer} else
(
select {table.answer}
case "Strongly Agree" : 5
case "Agree" : 4
case "Neutral" : 3
case "Disagree" : 2
case "Strongly Disagree" : 1
)

Then use a maximum as your summary field.

-LB

 

[Oglesbay]

LB -

I tried your formula and I get and error with the "else" section highlighted saying "A string is required here. Here is my formula exactly as I have it:

if ({HealthStream_Evals.Evaluation} startswith "The most useful information" or
{HealthStream_Evals.Evaluation} startswith "Of all the things that were" or
{HealthStream_Evals.Evaluation} startswith "If I were teaching") then
{HealthStream_Evals.Student_Answer} else
(
select {HealthStream_Evals.Student_Answer}
case "Strongly Agree" : 5
case "Agree" : 4
case "Neutral" : 3
case "Disagree" : 2
case "Strongly Disagree" : 1
)

I have three "comment" fields which is why I used the OR varialbe. Is it possible to do that?

 

[lbass]

Sorry, silly mistake. Put quotes around the numbers in the case statement--the results of an if-then have to be of the same datatype. This should work as long as you are not using the values for the other questions for calculations.

-LB

 

[Oglesbay]

LB - Works like a charm now! All the data shows up but the problem with it is that the first three columns are only single digit numbers and the last three are comment fields with unlimited information. So the all the data can't be shown if I resize to fit the comments. Is there a way to change to column width individually in the Crosstab? Right now if I resize one column it resizes all of them. What about wrapping text in the cells?

 

[lbass]

Crosstab cells don't wrap. You could break out the type of response based on formulas so that you have two summary fields:

//{@Comments}:
if ({HealthStream_Evals.Evaluation} startswith "The most useful information" or
{HealthStream_Evals.Evaluation} startswith "Of all the things that were" or
{HealthStream_Evals.Evaluation} startswith "If I were teaching") then
{table.answer}

//{@Ratings}:
if {table.answer} in ["Strongly Agree", "Agree", etc.] then
(
select {HealthStream_Evals.Student_Answer}
case "Strongly Agree" : 5
case "Agree" : 4
case "Neutral" : 3
case "Disagree" : 2
case "Strongly Disagree" : 1
)

Each summary could be resized as necessary. You can format them in the customize style tab to a horizontal display with labels.

-LB