PERCENTAGE CALCULATION PROBLEM 1

[shaunk]

I am running Crystal 8.5 against SQL Server.

I have a simple report that returns the number of appointments and total appointment durations in four groups.

Discipline (of Clinician)
Age-Group (of Client)
Program (financial Class)
Site (Setting of Service Deliver)

I am using distinct counts to calculate the number of appointments, and running totals to calculate total appointment duration in each of the four groups. This works fine.

The problem I have is when I attempt to express total appointment duration under Site (RTOTAL1) as a percentage of total appointment duration under Discipline(RTOTAL4).
This should simply be ( RTOTAL1/RTOTAL4 ) * 100.

The results I am getting are incorrect.

 

[kenhamady]

How is the value wrong (too hig, too low, etc).

Post the actual formula that calculates the percentages and tell us where it is placed.

Are the running totals done with variables or running total fields?
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Hillary]

Do you have anything suppressed? When using the running total it will include values that are suppress even though you can't see them.

 

[shaunk]

To describe where it is placed I will sketch a rough view of the report output.

DURATION % OF DISCIPLINE TOTAL
NURSING ( discipine)
AGE > 65 ( age-group)
ACUTE/POST-ACUTE ( Program)
HOME ( Site ) 40.00 (HRS) 10 %

Summary for Discipline ( Nursing)
Total Duration 400 (HRS)

As you can see , I am placing the formula that expresses Duration at Site as a percentage of Total Duration for Discipline in the Group Footer for Site. This is where I also have the running total to calulate the actual duration for site (RTOTAL3)- this is calculating correctly.

I have the running total that calculates the total duration for Discipline in the Discipline group footer (RTOTAL4) this is also calculating correctly.

From the above example, the "site % of discipline total" should be 10% but I am getting 100%.

The formula I am using is :

(RTOTAL3/RTOTAL4) * 100

I have also tried

WhilePrintingRecords;
(RTOTAL3/RTOTAL4) * 100

and

WhilePrintingRecords;
RTOTAL3 % RTOTAL4

Thanks for sticking to this.

 

[kenhamady]

I still can't tell how your running totals are created, via viariables or running total fields.

It would be obvious from your actual formulas, but these can't be the actual formulas. Please post the complete actual formulas.

Also, when you specify locations, please refer to the group numbers, like Group Header 3, etc.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[shaunk]

Ken,

Using Group Header n..n+1 ets the report looks like this

DURATION % OF DISCIPLINE TOTAL
GROUP HEADER 1 ( discipine)
GROUP HEADER 2 ( age-group)
GROUP-HEADER 3 ( Program)
GROUP-FOOTER 4 ( Site ) 40.00 (HRS) 10 %

GROUP FOOTER 3 ( summary for Program)
GROUP FOOTER 2 ( summary for age-group)

GROUP FOOTER 1
Summary for Discipline ( Nursing)
Total Duration 400 (HRS)


I am supressing the Group header 4 ( site) and calculating the total duration for Group 4 by using a running total {#RTOTAL3} and putting it in the Group Footer 3 (site)
Similiarly, I am calculating the total duration for Group 4 by using a running total {#RTOTAL4} and placing it in Group Footer 4.

The percent summary is being placed in Group-Footer 4 and in the formula is exactly this :

({#RTotal3}/{#RTotal4}) * 100

 

[kenhamady]

I see the problem.
You can't do percent of total using a running total, becuase it isn't complete when you use it.

Are you using running totas for a specific reason? Because if you used regular summary fields you wouldn't have this problem.

What settings are in the running totals? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[shaunk]

I made a mistake in the last post. It should have read
"{#RTOTAL3} and putting it in the Group Footer 4 (site)".

Each appointment ( identified by a unique ID ) may return up to 100 rows depending on how many clinicians attend and how many activities are performed. So if I sum duration for each appointment by using the normal summary functions, the one duration for the one unique appointment may be summed up to 100 times.
For this reason, I use a running total which evaluates on change of unique appointment id and resets on change of group ( for {#RTOTAL3 } it resets on change of SITE- Group 4, and for {#RTOTAL4} it resets on change of DISCIPLINE - Group 1)

I tried using next and previous to establish if we were in the same Appointment ID, but then CRYSTAL refused to do summary calculations on the results of a formula.(F$#%^&**ui^)

It looks as if I'm snookered at this stage.

 

[kenhamady]

Yes, you will need a running total to use the evaluate function, and these can't be done early. You can't do percent of total with running totals.


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[kenhamady]

I just wrote an discovered an article on using cross-tabs in the group header to calculate running totals before the details of a group. If you are handy with variables you might be able to do your "percent of total" using this technique. I wrote a description with a link to the article in my Dec newsletter:

http://www.kenhamady.com/news0212.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[shaunk]

Thanks ken,

I am working as a lone programmer in a lonely outpost in Australia and find this site and your site invaluable resource materials.

I will give the cross-tab a go when less busy

 

[Jacque]

Hi Shaunk,
I do a similar type of average using 'durations' hh:mm:ss. I create a formula as follows:

//total appointment duration under Site
numberVar vTotAptDurSite := {RTotal1};
//total appointment duration under Discipline
numberVar vTotAptDurDiscpl := {RTotal4};

(vTotAptDurSite%vTotAptDurDiscpl);

Hope this helps,
Jacque

 

[shaunk]

Thanks Jacque,

I have tried this as well but as Ken has described above, {#RTOTAL1} has been calculated (total for inner group), but not {#RTOTAL4}(total for outer group). This means I get a result but not the correct one.

Cheers
Shaun

 

[kmarino]

Jacque

Thanks!!! This is exactly what I needed because my running totals are on the same level.

 

[IdoMillet]

Shaunk,

It should be relatively simple to insert in the Group Header a linked subreport that sets a shared variable value to the total duration for the group.

The subreport doesn't need to join to the clinicians and the activities, so you may be able to even compute the total duration for the group using a simple (rather than a Running total).

In the main report, you would use the shared variable throughout the group to compute the % of group.

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

http://www.MilletSoftware.com