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
qbjgqbjgConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
SO each record has an hours value and a pay rate.
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
0
qbjgqbjgConsultantAuthor Commented:
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.
0
qbjgqbjgConsultantAuthor Commented:
I am considering a second record with a union.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

mlmccCommented:
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
0
qbjgqbjgConsultantAuthor Commented:
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.
0
mlmccCommented:
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

0
qbjgqbjgConsultantAuthor Commented:
In Crystal how do I make it do the calculation on the period break only instead of at the detail level?
0
mlmccCommented:
You can group by period and do the calculation in the group header or footer.

You will then have to use a running total or manual summary to calculate the total.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qbjgqbjgConsultantAuthor Commented:
That works. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.