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

FourHorsemen
FourHorsemen asked
on
Medium Priority
284 Views
Last Modified: 2012-08-13
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

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.

Author

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.

Ask the Experts
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
Also,....who is the FORTH HORSEMAN......?        : )
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.

Author

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!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.