Crystal 8.5 - Cross tabs & percentages- Tecnical User

[maruja]

Greetings:

I work at a medical facility. I have two cross tabs. One that counts visits which has worked fine. The other takes those visits and turns them into percentages. For example:
Row: admission source: Emergency Room. Column: Hospital Name. Summarized field: Should reflect 80.22%. (Emergency Room had 215 patients. TOTAL patients for all admission sources was 268. 215/268=80.22%.) I am having trouble coming up with a formula to derive the percentages for summarized fields. I have tried:{visits/Sum({visits}) but it does not like it. I have also tried some other things. Can you help me? Thank you.

 

[GJParker]

Youd didn't state which version of CR ayou are using but in Version 9 and higher you can do this by going into the crosstab expert -> crosstab tab -> highlight the summarised field -> click on change summary -> select show as percentage of

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[maruja]

GJPARKER:

I am on Crystal 8.5. I do not have Cross Tab Expert. I have looked for it. Thanks.

 

[GJParker]

right click on the crosstab and it's the second option down.

HTh

Gary Parker
MIS Data Analyst
Manchester, England

 

[IdoMillet]

I don't think this is an option in Crystal 8.5.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[maruja]

Ido:

I appreciate your response but I just can't believe that a formula can't be set up to do a simple division to give me a percentage? I haven't been doing Crystal for very long (about 6 months) but it appears to be a very powerful tool. Isn't there some kind of formula that can be derived? Thanks.

 

[GJParker]

Try

{Visits} % sum({visits})

alos be more specific is visitsa db field or a formula ? and rather than say it does not like it. let us know exactly what you are attempting and any error messages raised.



Gary Parker
MIS Data Analyst
Manchester, England

 

[maruja]

Gary:

I have tried your calculation also. It did not work. Visits is a data base field. The reason I said it didn't like it is because the formula never showed up in my 'available fields'. Thanks for your help.

 

[lbass]

If you have the option of using SQL expressions, the following will work. Create a SQL expression like
{%alladmissions}:

(select sum(A.`visits`) from Table A where
A.`hospital` = Table.`hospital`)

Replace "visits" and "hospital" with your exact field names. Replace "Table" with your table name. Leave "A" as is, since it represents an alias table name. If you have any other selection criteria, these may need to be built into the expression.

Then create a formula {@percent}:

{table.visits} % {%alladmissions}

Add this to your crosstab as your summary field and use sum as the summary. In preview mode, click on the % icon in the toolbar.

This will be accurate for the inner cells of the crosstab. If you also need the percentage for the row total, you will need to create a second SQL expression, add it to a new crosstab, and overlay the second crosstab on the first. Let me know if you need to do this.

-LB

 

[maruja]

Lbass:

Your response was very good. It gave me zeroes in the inner circles because the calculation involves dividing (for example) the Home patients: 215 by the Total number of patients: 268 to give a percentage of 80.22%. I need this for each of the admission sources you see there. I also have other statistics of this type that I need the same calculation for. I appreciate this very much. Thanks!

adm_src_cd_desc Patients
HM - Home 215
OU - Structured Outpatient Unit 4
RM - HMO Referral 3
TE - Transfer from Long Term Care 39
TH - Transfer from Short Term Hospital 4
TR - Transfer from Rehabilitation Center 3
Total: 268

 

[lbass]

I understand what you are trying to do, and you shouldn't have gotten zeros. Please copy the SQL expression that you created into the thread. Also provide your record selection formula--go to report->selection formula->record and copy the formula and paste it into the thread.

-LB

 

[lbass]

PS. Also expand the number of decimals in the crosstab cells to make sure the results are just not being displayed in a rounded fashion.

-LB

 

[maruja]

lbass:

My mistake. I didn't expand the number of decimals in my cells. Now I see the numeric data I need. Thank you so very much!!