We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# Help with a Crystal Report

on
Medium Priority
284 Views
Greetings..I am having some trouble creating a report on Crystal Reports XI:

I have a single table that has all the data I need to work with.

- ID (Primary Key)
- Region
- Catgory
- Code
- Q1
- Q2
- Q3
- Q4
- Q5

Q1 through Q5 are questions that contain string values ranging from 1 to 6.

What I need is to come up with a report that will show a Table, with 6 columns: Region, Q1, Q2, Q3, Q4, and Q5, where each line will have the region and percentage of values for each question that were marked as 4 or 5, with regard to the total for thet region (provided the answer wasn't 6).

In other words something like:
Q1   Q2   Q3    Q4     Q5
Region1 90% 56% 23% 67% 12%
Region2 80% 87% 11% 34% 78%
etc.

Where  each percentage is calculated as:
For every Q column
(Total amount of entries where Qx = 4 or 5 )DIVDED (Total amount of entries where Qx <> 6).

I hope that was clear enough to state the problem I'm having.  I want to do as much of the calculation on the Crystal Side to avoid taxing the Database.  I've tried fiddling with functions and summary fields but go nowhere.

Anyhelp would be greatly appreciated and rewarded.
Comment
Watch Question

## View Solution Only

Commented:
You want to create a cross tab report, this way you can tell it which fields to use as columns. Then on your summaries you can select to show as a percentage. Explore the crosstab section.

Commented:
I've played with the Crosstab..the problem is that if I put the Five Q columns in they don't line up side by side like I'd like.  Instead the display in a hierachical format, which isn't how I want to display them.

I get Q1, Then Q1 in Q2, Then Q3 in Q2 in Q1, and so forth.

I wantem lined up so the percentages for Q1, Q2, Q3, Q4, Q5 are lined up side by side.
Software Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006
Commented:

Can you post a copy of what you are seeing in the display of your crosstab currently? I would like to see what you mean.

My first thought is that I think you'll need to create a MANUAL CROSSTAB to accomplish this. Crystal's automated Crosstab is designed for Basic / Simple summaries of data. And works fine for that, but if you want to do specialized calculations, you'll need to switch over and actually build the Crosstab manually, meaning that you basically use the Report Sections to manually build a REPORT that displays like a crosstab.

Not the solution you were looking for? Getting a personalized solution is easy.

Software Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
Also,....who is the FORTH HORSEMAN......?        : )

Commented:
In case like this that didn't want to cooperate with me in Crystal. I would use excel and do all formulas and line the data up the way you want it in excel. Then connect to that worksheet as the database and just pull over the numbers into you report,
You have to make any changes you need to the spreadsheet then go update your report. This works for me in a many case, it is easier also.

Commented:
Okay wouldn't you know...A few hours ago I managed to pop out a manual crosstab with exactly what I wanted.  I had to use 3 formulas per Q. (one to initialize, one to calculate, one to display) with local variables.  A little supression and proper placement and voilĂˇ it worked.  I appreciate both your contributions though!
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile