Formulas are one of the more-powerful features available within Report Designer. Using formulas you can perform calculations using data from other columns, and create unique results that would otherwise be impossible using just the sources alone.
Use C-Variables to pull data from other columns into formulas.
Rename the column header used when printing a report.
We will go over the steps to create a new Report Definition that will calculate an estimate of the Vacation Earnings amount for each employee.
Create Report Definition
The report we will be making will contain five columns—four which will use data sources, and the last column will use a formula to calculate the Vacation Earning Amount of each employee.
You can add columns to the report a couple of ways:
Add the following columns to the report:Figure 2. The four columns using data-source for this report.Sources
Employee Profile > Personal > Employee Number
Employee Profile > Personal > Employee Name
History > Pay Period > Earning Amount
Employee Profile > Employment Data > Vacation Percentage
Add a formula column to the report.
We’ll go over the steps to setup the formula column with a formula that will calculate the Vacation Earning Amount in the following section.
Open the Formula Settings
Before we setup the formula, you will need to open the formula settings window for the formula column.
Select the formula column.
From the Properties Panel select the
Press the […] button to open the formula.
The formula that we are going to use looks like this:
Formulas F1 = C3 * (C4 / 100) (1) Value = F1 (2)
|1||The Vacation Percent is converted to a decimal here to make the result correct.|
|2||The result from |
What the formula is doing, in plain English, is converting the Vacation Percentage to a decimal then multiplying the Earning Amount column by the decimal result to calculate the Vacation Earning Amount.
C3 * (C4 / 100))into the
Save the formula by pressing
In the formula above we used two C-Variables (
C4) to bring data into the formula from other columns.
Each C-Variables refers to the value of the current row from the numbered column.
Column 1would be referenced using the
C1C-Variables. (The column numbers are viewable above the column headers in the Report Definition.)
Rename the Formula Column
The final thing that you may want to adjust being printing the report is to change the header text of the Formula Column. Right now the formula column header will be set to Formula, but this should be changed to something more meaningful.
Select the formula column.
From the Column Properties select Column Settings > Header Text and enter a new value to use for the column header.
Press Apply to save the changes to the report definition.
Note:These steps will work to rename the header of any column, not only the formula column.
Example of the Completed Report
After you have setup the Report Definition with a formula column you will want to preview the report. The following is an example of what the report may look like when it is run.
|1||The values from the Earning Amount column (Column 3) are referenced using the C3 C-Variables|
|2||The values from the Vacation Percentage column (Column 4) are referenced using the C4 C-Variables|
|3||The formula column uses the formula we setup to calculate the Vacation Earning Amount from each row of Column 3 and Column 4. (Note that the formula column uses the header text we entered previously.)|