Percentages in a report 2

[Faded]

Hello,

I'm a total newbie to Crystal Reports, and have tried without success to create a report that will display the total number rows returned by an SQL query and then the percentage of each of the field values.

Here's an example:
An SQL Server table is called HotelCards, and in that table are several fields that contain three possible values. For ease of understanding, let's say the values are 'a','b', and 'c'. Now, I want the report to list the total number of rows that contain a 'not null' value, say 100 and then the percentage of those that are 'a', 'b', etc... So if 35 of the 100 rows that are returned contain the value 'a', and ten rows contain 'b', then the report will say a - 35%, b - 10%, etc. Do you get it?

I have tried using formulas and SQL Expressions, but I don't understand them enough to know which (if either) is the appropriate one to be using. Can some one steer me in the right direction? Thanks in advance

Faded

 

[synapsevampire]

First, suppress getting rows that are null:

Not sure what:

"several fields that contain three possible values."

means exactly. If you have 7 rows that might have these values, it's a bit of a pain to count, because presumably you might have more than 1 A, B or C value...

I'll assume that you mean that there are 3 rows that might contain either an A, B, or C - correct me if I'm wrong.

Use the record selection criteria and place something like:

not({MyTable.Field1} in ['A', 'B', 'C'])
and
not({MyTable.Field2} in ['A', 'B', 'C'])
and
not({MyTable.Field3} in ['A', 'B', 'C'])

Please clarify what your data looks like (and the above statement in question), and the version of Crystal you're using and I'll show you how to do the counts of each A,B and C.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Faded]

I'll try to clarify. I'm using Crystal Reports 8.5.

The database it to track guest questionnaire's for a hotel.
In the table 'HotelCards', there is a column for each of the questions asked on the questionnaire, for example there is a question 'How would you rate the overall attitude of our staff?' that corresponds to HotelCards.overallAttitude. A guest filling in the questionnaire can answer 'excellent', 'average', or 'poor'.

Now... there are many questions like this on each questionnaire filled out, and I need to report on each question, how many of the guests (both in numbers and percentages) rated our overallAttitude as 'excellent' and how many rated us as 'average' etc... So yes, there are many rows that will contain the same values, and I need to gather results from all of the rows in the database for each column of the database into one report.

I hope I'm actually clarifying things rather than confusing them further

 

[synapsevampire]

Please provide the structure of the tables involved, or at least the relevent fields.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Faded]

create table HotelCard
  (pkHotelCardID int primary key,
   fkGuestID int,
   checkIn varchar(8),
   cleanRoom varchar(8),
   roomService varchar(8),
   checkOut varchar(8),
   shuttleBus varchar(8),
   resStaff varchar(8),
   bellStaff varchar(8),
   housekeeping varchar(8),
   phoneOperators varchar(8),
   skiSchoolValue varchar(8),
   skiDesk varchar(8),
   guestInfo varchar(8),
   overallAttitude varchar(8),
   meetExpectations varchar(8),
  )

 

[synapsevampire]

OK, I think I understand now...

You have A,B or C stored in each question field, and need to get the percentage of each based on the total number of questions that were answered. I can't think of a simple means to do this, so I'll give you a means using variables (though you could also use conditional formulas or multiple Running Totals):

For each Question, you need to have 4 values returned, the number of A, B, or C's, and the total count of questions answered.

I'd create 3 formulas per to handle each question, as in:
Reportheader formula:
whileprintingrecords;
global numbervar Q1A:= 0;
global numbervar Q1B:= 0;
global numbervar Q1C:= 0;
global numbervar Q1Cnt:= 0;

Detail section formula:
whileprintingrecords;
global numbervar Q1A; // Count of A
global numbervar Q1B; // Count of B
global numbervar Q1C; // Count of C
global numbervar Q1Cnt; // Total questions answered

If {Hotelcards.checkIn} = 'A' then
numbervar Q1A := Q1A +1; // Add to A's answered
Q1Cnt := Q1Cnt+1 // Add to the total questions answered
else
if
{Hotelcards.checkIn} = 'B' then
numbervar Q1B := Q1B +1;
Q1Cnt := Q1Cnt+1
else
if
{Hotelcards.checkIn} = 'C' then
numbervar Q1B := Q1B +1;
Q1Cnt := Q1Cnt+1

Report Footer Section:
whileprintingrecords;
numbervar Q1A;
numbervar Q1B;
numbervar Q1C;
numbervar Q1Cnt;
stringvar MyOutput;

if Q1Cnt <> 0 and Q1A <> 0 then // Check for 0 conditions
MyOutput := &quot;checkIn Excellent: &quot;+totext((Q1A/Q1Cnt)*100)+&quot;%&quot;+chr(13)
else
MyOutput := &quot;checkIn Excellent: 0%&quot;;
//Use the same if type of conditions around the following:
//&quot;checkIn Average: &quot;+totext((Q1B/Q1Cnt)*100)+&quot;%&quot;+chr(13)
//&quot;checkIn Poor: &quot;+totext((Q1C/Q1Cnt)*100)+&quot;%&quot;+chr(13)

MyOutput

This would be done for every question asked. It's a lot of mundane work...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Faded]

Yeah, I think you've got it

Wow, not only is it a huge amount of mundane work, but as a Crystal newbie it will take alot of time getting used to the Crystal IDE as well. Judging from what you know of my task, do you think that this is the most viable solution? Would there be an easier way if the database was structured differently? Is this typical of a report that a Crystal developer would be producing?

So many questions...

Thanks for all of your help.

Cheers, Faded

 

[synapsevampire]

This is not a typical, and yes, the dataabse could be tweaked to more readily facilitate this.

A table called questions, with question and rating fields might simplify your process.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com