Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Crystal Reports with sql command one to many issue

Posted on 2011-10-04
9
Medium Priority
?
323 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:qbjgqbjg
  • 5
  • 4
9 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36909940
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
 

Author Comment

by:qbjgqbjg
ID: 36909970
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
 

Author Comment

by:qbjgqbjg
ID: 36909982
I am considering a second record with a union.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 101

Expert Comment

by:mlmcc
ID: 36910319
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
 

Author Comment

by:qbjgqbjg
ID: 36910347
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 36910509
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
 

Author Comment

by:qbjgqbjg
ID: 36910632
In Crystal how do I make it do the calculation on the period break only instead of at the detail level?
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 36911211
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
 

Author Closing Comment

by:qbjgqbjg
ID: 36911744
That works. Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Loops Section Overview
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question