|
Widget Co. is concerned with maximizing the morale of its people. Every employee is entitled 3 days of paid vacation per month. Employees are given the option of selling back their vacation days at 1 1/2 times their normal wage.
The production manager has tasked you with creating a report with the following requirements:
| • | Look presentable - The report will be going to the VP. |
| • | automatically display generation date and page numbers. This needs to be a "one click" report. |
| • | Group employees by department. |
| • | Be dynamic - Widget Co. anticipates rapid growth. The report needs to be able to deal with a large number of employees, possible new departments, and separate pages automatically without cutting off data. |
| • | Calculate equations automatically - The manager is interesting in knowing how much money in vacation time each employee is owed, as well as a running total by department. |
| • | Sort employees by vacation days. Widget Co. gives preferential approval to the employee with the most days. |
| • | Support custom row versions. A special paid vacation is offered when an employee's vacation sellback value exceeds $5000. Such employees need to stand out! |
Employee data can be retrieved from the accounting database with the following SQL query:
SELECT * FROM employees;
We will modify our SQL query to include the derived value buyout, the monetary value of employee's vacation days.
CAST is used so that MySQL returns buyout as a number instead of a string.
SELECT *, CAST(income/360 * 1.5 * vacationdays AS SIGNED) AS buyout FROM employees;
Previous (Index) Next (Getting Started)
TIP
|
We could use expressions within the report to calculate buyout. In this tutorial we use the SQL database because we will be using buyout in many places. We will: display it as a column, use it as the basis of our custom row versions, and may want the option of sorting our report based on it. Other reasons include: leveraging the SQL database's rich function library and only needing to change the expression in one place.
|
| 
Tutorial 1 - Background