Help with a Crystal Report

Posted on 2009-02-18
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%

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.
Question by:FourHorsemen
    LVL 1

    Expert Comment

    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 Comment

    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.
    LVL 17

    Accepted Solution


    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.
    LVL 17

    Expert Comment

    Also,....who is the FORTH HORSEMAN......?        : )
    LVL 1

    Expert Comment

    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 Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now