asked on

Reporting seperate percentages in Crystal report

Greetings experts,

I have a Crystal report I am working on which I need to report percentage of times a value is not recorded in a field.

For example, I have a field named, Vehicle ID, and the users are supposed to enter their vehicle ID, if they do not enter a value it defaults to -20, they can also choose -25,
-15, -10, or -5 (all mean something to our agency).

What I need is a way to report the percentage of each entry above so I can identify how close we are adhering to our data entry requirements.

Currently I have written “if” statements for each value above like the one below for each value:

If E02_02 = “-20” then 1

From here I write another formula for the percentage for each value as well.

As you can see, this is not very efficient and I have to do it for every field in a 500 plus field database. Is there a cleaner way to do this without writing 1000+ if statements?

The fields are all strings and I am using Crystal 8.5 on a SQL server.

Thanks
Task

Mike McCracken

You could try using a cross tab to summarize the data.
FIelds are displayed as summary values and you could display them as percentages.

mlmcc

James0628

Is a cross-tab practical with 500 different fields? Then again, is anything really "practical" when you have 500 different fields to deal with?

James

Mike McCracken

Agree. At least with the cross ta you don't have to write all the formulas.

mlmcc

taskhill

ASKER

I am finding it difficult to get the answers I need witht he cross tab function. However, I must admit I do not have much experince with them. I am working through a book right now to see if I can figure them out.

Right now I am not getting any results but I can see the values in my tables so I know I am doing something wrong. I will continue to post as I find out more.

Mike McCracken

How are you building the cross tab?

What data do you have?

mlmcc

taskhill

ASKER

I have table with the following structure

pk_E01_01 | E02_01 | E02_02 | E02_03 | E02_04| etc…
---------------------------------------------------------------------------------------------
517 | JoesService | 20110331001 | K1 | -20 |
518 | HarriesSvc | 20110331002 | R5 | 51 |
519 | -5 | 20110331003 | -15 | 81 |
520 | HarriesSvc | -15 | -25 | 79 |
521 | -15 | 20110331004 | Mic-6 | -15 |

I need to report how many times “JoesService” reported one of the negative values (-5, -10, -15, -20, -25) in each field (E02_01, E02_02, E02_03, E02_04, etc).

When I set up the cross tab like this

Rows: E02_01
Columns: E02_02
Summarized Fields: Count of E02_02 (where E02_02 = one of (-5, -10, -15, -20, -25))

It works fine.

But I need it for all the fields and when I tried:

Rows: E02_01
Columns: E02_02, E02_03, E02_04
Summarized Fields: Count of E02_02, E02_03, E02_04 (where E02_02 = one of (-5, -10, -15, -20, -25))

I get no results at all, just a blank page.

This is because I don’t understand cross tabs and I don’t expect this to be a “how to” session. I should learn that on my own and come back for “expert” advise if I continue to have problems. Right?

Mike McCracken

So you expect to see
E02_01 E02_02 E02_03 E02_04
JoesService 0 1 1
HarriesSvc 1 1 0

Is that the data you expect?

mlmcc

taskhill

ASKER

Yes that is what I am looking for.

Mike McCracken

To do it you will need to base the report on a command

Check the report and look at the command it uses.

mlmcc

Q-26919806.rpt
Database6.mdb

taskhill

ASKER

Sorry for the delay I am at training but will be able to work on this in the afternoon.

taskhill

ASKER

I could not open this report. I received an "Invalid report version" error. I am using Crystal 8.5.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

taskhill

ASKER

How correct you are! I can easily creat a view and go from there.

Thanks,

Task

Mike McCracken

Here is the Command I used

SELECT `Table2`.`E02_01`, `Table2`.`E02_02`, "E02_02" As Col
FROM `Table2` `Table2`
UNION ALL
SELECT `Table2`.`E02_01`, `Table2`.`E02_03`, "E02_03" As Col
FROM `Table2` `Table2`
UNION ALL
SELECT `Table2`.`E02_01`, `Table2`.`E02_04`, "E02_04" As Col
FROM `Table2` `Table2`

Here is a picture of the output using your data

mlmcc

Picture1.png