|
|
Applies to versions: 3.1, 3.2
Using Expressions in Custom Report / Custom Payroll Export
There are various types of expressions in a Custom Report, each of which serve a different purpose and have access to a certain set of Expression Variables.
See the Supported Functions for Expressions article for more information on using expressions, and a list of available functions.
See the Expression Variables article for a detailed list of the variables available as Input Data Variables and General Report Variables .
All expressions for custom reports/custom payroll exports also have access to the [report_source] variable.
There are several special commands that will result in actions taken by the custom payroll/report engine. To use these, simply have the column evaluate to the Special Command and the Result will happen.
| Special Command |
Result |
Example Command | Example Return |
| action_remove_this_row |
The entire row will be removed from the report. Note: The report engine checks for this after evaluating each column, left to right. If it is found, additional columns are not evaluated. This can be helpful, for example, when a column to the right does something, such as setting a custom tracking field.
|
| action_new_page |
A new page will be created. Requires the report to be run in a mode that supports pages, such as PDF. |
| duplicate(list) |
Causes the current row to be duplicated. The first row gets the value at the beginning of the list. Subsequent rows will be created and given values from the comma (or array) of values passed in. If a list, format as "row1,row2,row3".
Columns without duplicate will simply have the data copied.
|
duplicate("Labor,Machine,Setup")
duplicate([payrollEventType].",Bonus")
|
Three rows, the first with "Labor" and the second with "Machine" and last with "Setup"
Two rows, the first with the payroll type, a second with "Bonus"
|
- reportSourceIsCustomReports
- reportSourceIsPayrollExport
- reportSourceIsAutomaticReports
- reportSourceIsPAPI
|
For Custom Payroll and Custom Reports these functions will tell if the report is being ran from Custom Reports, a Payroll Export, Automatic Reports, or PAPI respectively. This is used when you want to show different information or take different actions for a report being ran as a payroll export, for example, that you do not normally want to do. |
reportSourceIsCustomReports() |
"1" |
Column Values
- To use an expression for the value of a column in the columns tab:
- Select "Expression (in Default)" from the drop down list
- Input the expression into the "Default or Expression" input box
- Expressions used for the value of columns have access to Input Data Variables
- When a row is summarized (the "Total (sum)" or other summarize function is selected for at least one column in the report) it's possible to have an expression that evaluates for each row and a separate expression that evaluates on the summarized row. (The summorizeExpression runs after the rows are summarized, but before formatting, sorting, subtotal rows, spreadsheet substutions and dulpciate row removal.)
- In the summarized row, access the now-summarized data with [n] where n is the column number. [columnValue] is also available which is the summarized value of this column. This is useful, for example, when you need to compare summarized values, or when you want to show something only once, such as the total worked hours in a year.
- The summarize expression does not have access to Input Data Variables.
- Example: Total hours worked in the current calendar year, expression is in column 3.
Note: The summarized expression only sees the summarized data, so it cannot directly access [usersID] needed by findWorkedTime.
So, the first line puts the employee's ID in the column so it can be used as the first argument to findWorkedTime:
=[usersID]
--summarizeExpression--
=round(findWorkedTime([3],strtoutctime(utcdate("Y-01-01")))/3600,2)
For header style expressions or data style expressions see the below section about style expressions.
Preamble/Footer Values
Using expressions for preamble/footer columns is similar to using them in the columns tab, however preamble/footer expressions only have access to General Report Variables.
Subtotal Expressions
In the subtotals tab there are two types of expressions (as well as some style expressions, see below section about style expressions).
Filter Expressions
To use a filter expression simply input an expression into the "Filter by Expression" section for the desired column.
- The result of the expression for each row is what the totalling method will act on.
- Filter expressions have access to two copies of the data for each row:
- [raw][n] is used to access the values in the row at column n before custom formatting
- [report][n] is used to access the values after custom formatting is applied
- [raw][columnValue] or [report][columnValue] is also available which is the value of this column
- Filter expressions can also access the begin and end times of the report as [begtimestamp] and [endtimestamp]
Format Expressions
In addition to just formatting the subtotal result, the "Format" field can contain an expression to change the value after the data is totalled.
- Format expressions must start with two equals signs instead of one. e.g. "==[5]-[6]"
- The totaled values are available as [n] where n is the column number, or [columnValue] for the totalled value of this column.
- There are more examples of Formatting expressions inside the subtotal tab
Style Expressions
Expressions can be used to more dynamically change the style of a report output for advanced formats like xls, xlsx, pdf, or html.
Style Selecting Expressions
Wherever there is an option to choose a style for part of the report, the style can also be determined by an expression.
- The result of the expression should be the /name/ of a style defined in the Styles tab, or an empty string for no styling.
- Style selecting expressions are available as:
- Report Style (general tab). Style for each cell in the report, this expression will evaluate for every cell individually. Has access to General Report Variables as well as [columnValue] for the final value in this cell.
- Header Style (columns tab). Style for the header printed above this column. Has access to General Report Variables .
- Data Style (columns tab). Style for each individual cell in this column. Has access to the same Input Data Variables as a normal expression for the column value. - This expression supports a --postExpression-- section which instead has access to the General Report Variables as well as numbered cell values [n] and [columnValue] for the current cell value.
- Preamble/Footer Data Style. Style for a preamble/footer cell. Has access to General Report Variables as well as numbered cell values [n] for the values in the preamble/footer row and [columnValue] for the value in this cell.
- Subtotal Styles (subtotals tab). Style for each cell in the subtotals row as well as the subtotal title style. Has access to numbered values [n] for the values in the subtotal row as well as [columnValue] for the value in this cell.
Expressions in Styles
In the Styles tab, every element of a style can be determinded using an expression instead of a static value
- These expressions are run for each cell that the style is applied to.
- The expressions have access to General Report Variables as well as numbered cell values [n] and [columnValue] for the value in this cell.
- When the style is being applied as a data style in the columns tab, the expression can contain a --dataStyleExpression-- section which instead has access to Input Data Variables , the same as the normal data expression for a column.
- This expression field can also contain a simple value instead of an expression, such as a hexidecimal color code "ffe456", or a color name "black".
|
Information