[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Help with a Crystal Report

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%

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.
  • 2
  • 2
  • 2
1 Solution
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.
FourHorsemenAuthor 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.
MIKESoftware Solutions ConsultantCommented:

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.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

MIKESoftware Solutions ConsultantCommented:
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.
FourHorsemenAuthor 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!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now