Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Minimizing Responses to One Line 1

Status
Not open for further replies.
Oglesbay

Oglesbay

Instructor
May 5, 2006
71
US
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?
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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)
 
Upvote 0 Downvote
On the customize style tab you can suppress row totals and/or suppress column totals.

-LB
 
Upvote 0 Downvote
Is there a way to sort or arrange the fields in the Summarized Fields section of the Crosstab table?
 
Upvote 0 Downvote
Can you explain more what you mean?

-LB
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmck1964
  • Locked
  • Question
Using "OR" to select data
Replies
4
Views
304
mmck1964
mmck1964
jchewsmith
  • Locked
  • Question
Report filter to run report yeartodate through yesterday
Replies
0
Views
203
jchewsmith
jchewsmith
BradCustom
  • Locked
  • Question
Passed variable from Subreport to Main skips one row
Replies
4
Views
104
BradCustom
BradCustom
Cpreston
  • Locked
  • Question
Field not displaying in one line
Replies
1
Views
87
Cpreston
Cpreston
AleXSR700
  • Locked
  • Question
How to find first non-zero decimal place?
Replies
8
Views
223
AleXSR700
AleXSR700

Part and Inventory Search

Sponsor

Back
Top