Bar chart, percnetage - need idea of how to complete this

[mcaizpp2]

Hello,
I'm using crystal version 9.
My problem is that I need to create a percentage barchart and I am not sure where to begin.

My prob -

I have 4 fields in my table (sql server 2000) salary, bonus, deferredbonus, band. The band field has the following values - A, B, C, D.

1)I have to sum the salary field

2) I need to sum the bonus field for records that have a band b and then calculate the percentage it is of the sum of the salary field.

So for example - the sum of the salary field is 100. The sum of the bonus field for records with band B is 10. The percentage then is 10% of the sum of the salary field.

I then need to do exactly the same for the deferredbonus field and stack the 2 percentages on top of each other.


So in this example - lets assume deferredbonus is also 10%.

So there would be 2 bars equalling 20% and then blank space for the other 80%.

I hope that makes sense!

Does anyone know how tp implement this?

 

[lbass]

Place the chart in the report header or footer. Choose percent bar chart->advanced layout, and add {table.employeeID} as the "on change of" field. Create the following formulas:

//{@bonus}:
if {table.band} = "B" then {table.bonus}

//{@deferred bonus}:
if {table.band} = "B" then {table.defbonus}

//{@bonus%}:
sum({@bonus},{table.employeeID}) % sum({table.salary},{table.employeeID})

//{@defbonus%}:
sum({@defbonus},{table.employeeID}) % sum({table.salary},{table.employeeID})

//{@nonbonussalary%}:
100 - {@bonus%} - {@defbonus%}

Use the last three percent formulas as your summary fields in the chart. Do not summarize them.

-LB

 

[mcaizpp2]

Hi Lbass, thanks for your response it worked great!.

The requirements for this chart have been changed since I posted this question. The client now wants it to show bands B,C & D.

My initial thought was to maybe create 3 separate charts and make them "look" as though they are 1 single chart - Do you think I should continue down thatpath or is there a better way around that?

Thanks again lbass.

 

[lbass]

You need to provide more explanation--I don't know how the bands fit in overall to know the implications.

-LB

 

[mcaizpp2]

Hi lbass - for every person in the table(record) ther will have been assigned a band.

Hope thats what you wanted to know - thanks again

 

[lbass]

No, that doesn't really mean anything to me. I don't know what a band is or how that would relate to other fields or the chart you want.

-LB

 

[mcaizpp2]

Every employee is grouped into 1 of 4 bands, 'A','B','C','D'.

Each employee record has one it is ust a way to classify each employee. So what the client wants to know is how much bonus(as a percentage) per each band.

I'm not sure if I've answered your question but this is how the tables link

employee ->* performanceReview. *inner join

Employee Table Fields
EmployeeID
Band
Salary


performanceReview Fields
EmployeeID
Bonus
Defbonus
Period - (2004, 2005 etc)

 

[lbass]

Try adding {table.band} as the highest order "on change of" field, followed by {table.employeeID}. Then I think you can just change the formulas to:

//{@bonus%}:
sum({table.bonus},{table.employeeID}) % sum({table.salary},{table.employeeID})

//{@defbonus%}:
sum({table.defbonus},{table.employeeID}) % sum({table.salary},{table.employeeID})

//{@nonbonussalary%}:
100 - {@bonus%} - {@defbonus%}

This assumes that you still want to chart per employee within each band. If you really only want per band, then remove employeeID from the on change of area, and replace {table.employeeID} in the percent formulas with {table.band}.

-LB

 

[mcaizpp2]

Hi Lbass,

Sorry for taking so long tro get back to you - just wanted to say that what you suggested worked perfectly - thanks a lot for helping us!