Crystal Reports with sql command one to many issue
I have a crystal report that was written for employees that work a 28 day cycle. So the user selects the number of 28 cycles they want to see. The data is broken out by different hour types within the 28 day cycle so there are multiple records for each 28 day cycle. Now they have decided they also want to see the salary. Annual Salary is calculated as 2704 * Hourly_Rate. That will provide the current Annual Salary. However, that is not what they want. Since the hourly rate changes, they want the salary calculate for each 28 day cycle and totaled up for the entire period selected. The hard part is the multiple hour entries within the 28 day cycle. I am currently using an sql command to create a table to use for the report. How can I add a calculation for the salary they want? See code for sql. Garcia-Report3-SQL.txt
ASKER
We are not looking for the amount actually paid. We are looking for the salary, which is different. Hours + Rate will give what they were paid, not salary. The problem is the one to many.
ASKER
I am considering a second record with a union.
I guess I don't follow the what the salary you are calculating is.
If you calculate HoursWorked * Rate for each record then sum that over the period it will give the amount paid for the period.
If that is then multiplied by the number of periods in a year, you will get the annual compensation.
mlmcc
If you calculate HoursWorked * Rate for each record then sum that over the period it will give the amount paid for the period.
If that is then multiplied by the number of periods in a year, you will get the annual compensation.
mlmcc
ASKER
Not in this case. People have a standard salary. They can work overtime and other things, So they may be paid more than their salary. These employees are firemen. So they are pretty much always paid more than their annual salary. The purpose of the report is to compare what they are paid versus their salary.
How is the salary calculated?
Is the hourly rate fixed for the 28 day period or can it change based on task?
If fixed for a 28 day period, then you can use 208 * hourly rate for that period to get the salary for the period.
Calculate it for each period and add.
If it can change in a period then you will have to use 52 hour for a week.
mlmcc
Is the hourly rate fixed for the 28 day period or can it change based on task?
If fixed for a 28 day period, then you can use 208 * hourly rate for that period to get the salary for the period.
Calculate it for each period and add.
If it can change in a period then you will have to use 52 hour for a week.
mlmcc
ASKER
In Crystal how do I make it do the calculation on the period break only instead of at the detail level?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works. Thanks
Group by person
Create a formula as
{HoursField} * {PayRate}
Use a summary to get the total for the period for the person
Multiply that total by the number of periods in a year.
mlmcc