The functions listed below by category are the predefined functions available for use in financial designs. Also included are the expressions which can be used as arguments of some of the functions.
Note
Parameters listed in brackets [ ] are optional.
Functions
Date functions| Function | Description | Format |
|---|---|---|
| PeriodBegin | Returns the begin date of the period. | PeriodBegin([Period] [,Company]) |
| PeriodEnd | Returns the end date of the period. | PeriodEnd([Period] [,Company]) |
| PeriodYearBegin | Returns the begin date of the period's year. | PeriodYearBegin([Period] [,Company]) |
| PeriodYearEnd | Returns the end date of the period's year. | PeriodYearEnd([Period] [,Company]) |
| AsOf | Returns the as of date, if specified; otherwise, returns 0. | AsOf() |
Examples
PeriodEnd("Current"). Retrieves the ending date of the current period.
"As of" & Text(PeriodEnd("Current"), "mmmm dd, yyyy"). Combines Excel formulas and ActivityHD functions to build a heading description like "As of March 31, 2014". The date is the end date of the current period. Text is a standard Excel function.
| Function | Description | Format |
|---|---|---|
| Activity | Returns the net activity for the period. | Activity(Period, Items[,Items] [,Company] [,Unit]) |
| Balance | Returns the balance as of the end of the period. | Balance(Period, Items[,Items] [,Company] [,Unit]) |
| BeginBalance | Calculates the beginning balance of a set of accounts as of the specified period. | BeginBalance(Period, Items[,Items] [,Company] [,Unit]) |
| CreditActivity | Returns the credit activity for the period. | CreditActivity(Period, Items[,Items] [,Company] [,Unit]) |
| CreditBalance | Returns the credit balance as of the end of the period. | CreditBalance(Period, Items[,Items] [,Company] [,Unit]) |
| CreditYearToDate | Returns the year-to-date credit activity as of the end of the period. | CreditYearToDate(Period, Items[,Items] [,Company] [,Unit]) |
| DebitActivity | Returns the debit activity for the period. | DebitActivity(Period, Items[,Items] [,Company] [,Unit]) |
| DebitBalance | Returns the debit balance as of the end of the period. | DebitBalance(Period, Items [,Items] [,Company] [,Unit]) |
| DebitYearToDate | Returns the year-to-date debit activity as of the end of the period. | DebitYearToDate(Period, Items [,Items] [,Company] [,Unit]) |
| PeriodNumber | Returns the number of the period. | PeriodNumber(Period [,Company]) |
| PeriodWorkDays | Returns the number of work days in the period. | PeriodWorkDays(Period [,Company]) |
| YearToDate | Returns the year-to-date activity as of the end of the period. | YearToDate(Period, Items [,Items] [,Company]) |
To produce a financial amount in a worksheet cell, three elements must be specified:
- Financial number. This number is determined by a numeric function. Numeric functions have the form function(p,i,i) where p represents the period parameter and i represents an items parameter.
- Date range. The date range is specified in the period parameter of the function.
- Accounts. An items parameter determines the account or group of accounts under consideration. The items are specified in the second and in the optional third parameter. You can use the keyword "and" to string together as many items parameters as you need for a single numeric function in a formula.
Examples
Activity("Current - 1 year", "Inc=Phone", "Ledger=A"). Retrieves the net activity of last year's current month for the phone expense accounts of ledger A.
Activity("Current", "GL.Account=1000-00-A"). Retrieves the net activity of the current month for account 1000-00-A.
-Activity("Current - 1", "Inc=Rev", "Ledger=B"). Retrieves the net activity of the budget for the revenue accounts in the previous period. The minus (-) in front changes the credit sign to positive for printing purposes.
Activity("Current", "Inc=" & $A22, "Ledger=A"). Retrieves the net activity in the current period for expense accounts that match the "Inc" rollup item in cell A22. Only accounts in ledger A are included.
Activity(" ", $A6 & "=" & $B6, "Ledger="D$1). Retrieves the net activity in the current period for the accounts that match the rollup item specified in cell B6 of the rollup specified in cell A6. Only accounts that contain the ledger item in cell D1 are included.
Activity(" ", "Inc=" & $A6 & " and Dept=12", "Ledger="D$1). Retrieves the net activity in the current period for the accounts that match the rollup item in cell A6 of "Inc" and also assigned to Dept 12. Only accounts that contain the ledger item in cell D1 are included.
YearToDate("Current", "Inc=Supplies", "Ledger=A"). Returns the year-to-date through this month of the office supply expenses.
CreditActivity("Period 12 - 1 year", "Inc=Phone", "Ledger=A"). (Credits for the period) Retrieves the credit activity for the previous year's last period of the accounts with a rollup item of "Phone" in the "Inc" rollup. Only accounts in ledger A are included.
DebitActivity("Period 12 - 1 year", "Inc=Phone", "Ledger=A"). (Debits for the period) Retrieves the debit activity for the previous year's last period of the accounts with a rollup item of "Phone" in the "Inc" rollup. Only accounts in ledger A are included.
Balance("Current", "Bal=CASH", "Ledger=A"). Retrieves the balance of the accounts in the "Bal" rollup with a rollup item of "CASH" as of the ending date of the current period.
-Balance("Period 1 of Year", "Inc=Rev", "Ledger=B"). (Total year's budget amount for a revenue account) Reverses the sign of the balance retrieved for the only period in a "Year" calendar for the accounts with a rollup item of "Rev" in the "Inc" rollup.
YearToDate("Period 12", "Inc=Phone", "Ledger=B"). (Alternate method for reporting the total year's budget amount) Retrieves the end-of-year balance of the accounts with the rollup item "Phone" in the "Inc" rollup.
YearToDate("Period 12", "Inc=Phone", "Ledger=B")*PeriodNumber("")/12. (Calculates year-to-date budget amount from total year's budget amount) Retrieves the total year budget, then multiplies it by the period number and divides by 12.
If(D6=0, 0, else C6/D6). (Budget variance column) Calculates a percent of actual/budget without giving a "divide by zero" error if the budget amount is zero. You can either multiply this amount by 100 or change the formatting of the cell in Excel to percent style.
Round(Activity("Current", "Inc=Supply", "Ledger=A"), 2). Rounds the current activity of the supply expense accounts to even dollars (the two decimal places are rounded). Round is a standard Excel function.
Text functions retrieve text such as segment item descriptions, company information, and calendar information, for inclusion on a financial statement.
| Function | Description | Format |
|---|---|---|
| Account | Returns the formatted account code. | Account(Account [,Company]) |
| AccountCustomField | Returns the value of a custom field on the account. | AccountCustomField(Field, Account [,Company]) |
| AccountDescription | Returns the account description. | AccountDescription(Account [,Company]) |
| AccountMemo | Returns any memo recorded on the account. | AccountMemo(Account [,Company]) |
| Segment | Returns the segment code. | *Segment(Segment [,Company]) |
| SegmentPluralName | Returns the segment plural name. | *SegmentPluralName(Segment [,Company]) |
| SegmentMemo | Returns any memo recorded on the segment. | *SegmentMemo(Segment [,Company]) |
| SegmentCustomField | Returns the value of a custom field on the segment. | *SegmentCustomField(Field, Segment [,Company]) |
| SegmentItem | Returns the segment item code. | *SegmentItem(Segment, Item [,Company]) |
| SegmentItemDescription | Returns the segment item description. | *SegmentItemDescription(Segment, Item [,Company]) |
| SegmentItemShortDescription | Returns the segment item short description. | *SegmentItemShortDescription(Segment, Item [,Company]) |
| SegmentItemName | Returns the segment item name. | *SegmentItemName(Segment, Item [,Company]) |
| SegmentItemAddress1 | Returns the first address line from the segment item address. | *SegmentItemAddress1(Segment, Item [,Company]) |
| SegmentItemAddress2 | Returns the second address line from the segment item address. | *SegmentItemAddress2(Segment, Item [,Company]) |
| SegmentItemCity | Returns the city from the segment item address. | *SegmentItemCity(Segment, Item [,Company]) |
| SegmentItemState | Returns the state from the segment item address. | *SegmentItemState(Segment, Item [,Company]) |
| SegmentItemZipCode | Returns the zip code from the segment item address. | *SegmentItemZipCode(Segment, Item [,Company]) |
| SegmentItemCountry | Returns the country from the segment item address. | *SegmentItemCountry(Segment, Item [,Company]) |
| SegmentItemMemo | Returns any memo recorded on the segment item. | *SegmentItemMemo(Segment, Item [,Company]) |
| SegmentItemCustomField | Returns the value of a custom field on the segment item. | *SegmentItemCustomField(Field,Segment, Item [,Company]) |
| Company financial functions, like all functions with the optional "Company" argument, return information only from companies the user can connect to. | ||
| CompanyName | Returns the company name. | CompanyName("Company") |
| CompanyLegalName | Returns the company's legal name. | CompanyLegalName("Company") |
| CompanyAddress1 | Returns the first line of the company address. | CompanyAddress1("Company") |
| CompanyAddress2 | Returns the second line of the company address. | CompanyAddress2("Company") |
| CompanyCity | Returns the city of the company address. | CompanyCity("Company") |
| CompanyState | Returns the state of the company address. | CompanyState("Company") |
| CompanyZipCode | Returns the zip code of the company address. | CompanyZipCode("Company") |
| CompanyCountry | Returns the country of the company address. | CompanyCountry("Company") |
| CompanyMemo | Returns any memo recorded on the company record. | CompanyMemo("Company") |
| CompanyCustomField | Returns the value of a custom field on the company record. | CompanyCustomField(Field, "Company") |
| PeriodCalendar | Returns the calendar code for this period. | Period Calendar(Period [,Company]) |
| PeriodCalendarDescription | Returns the calendar description. | PeriodCalendarDescription(Period [,Company]) |
| PeriodCalendarMemo | Returns any memo recorded on the calendar. | PeriodCalendarMemo(Period [,Company]) |
| PeriodCalendarCustomField | Returns the value of a custom field on the calendar. | PeriodCalendarCustomField(Field, Period [,Company]) |
| PeriodDescription | Returns the period description. | PeriodDescription(Period [,Company]) |
| PeriodYear | Returns the year of the period. | PeriodYear(Period [,Company]) |
| PeriodYearDescription | Returns the description of the year of the period. | PeriodYearDescription(Period [,Company]) |
| Unit | Returns the code of the passed unit or, if no unit is passed, the code of the current unit specified in the Financial Options dialog box. | Unit([Unit] [,Company]) |
| UnitDescription | Returns the description of the passed unit or, if no unit is passed, the description of the current unit specified in the Financial Options dialog box. | UnitDescription([Unit] [,Company]) |
| UnitMemo | Returns the memo from the passed unit or, if no unit is passed, the memo from the current unit specified in the Financial Options dialog box. | UnitMemo([Unit] [,Company]) |
| UnitCustomField | Returns the value of the specified custom field on the passed unit or, if no unit is passed, the value of the specified custom field on the current unit specified in the Financial Options dialog box. | UnitCustomField(Field [,Unit] [,Company]) |
* Put the parameter in quotes if it is not numeric.
Examples
SegmentItemDescription("Inc", "REV"). (Line description) Retrieves the description of the "REV" rollup item in the "Inc" rollup.
SegmentItemDescription("Inc", A6). Retrieves the description of the rollup item in cell A6 of the "Inc" rollup.
PeriodYear("Current"). Prints the year in a column heading.
Accounts Payable functions provide drill down to Accounts Payable information within a financial design worksheet. Use these functions to calculate totals based on AP information and to retrieve related information.
| Function | Description | Format |
|---|---|---|
AP EXPENSESThese functions calculate totals using selected GL postings and permit drill down to AP detail. "Items" in this section refers to expressions such as segment items expressions, attribute items expressions, and invoice items expressions. You specify attributes with AP usage and attribute items using comparison operators. The expense GL-related functions in this section can be limited (via items expressions) by the following data:
|
||
| APExpenses | Calculates total AP expenses from invoice distributions for the specified period. | APExpenses(Period [,Items] [,Items] [,Company] [,Unit]) |
| APExpensesYTD | Calculates the year-to-date AP expenses from invoice distributions for the specified period. | APExpensesYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
AP VENDORS |
||
| APVendor | Returns the code of the specified AP vendor. | APVendor(VendorCode [,Company]) |
| APVendorDescription | Returns the description of the specified AP vendor. | APVendorDescription(VendorCode [,Company]) |
| APVendorMemo | Returns the memo of the specified AP vendor. | APVendorMemo(VendorCode [,Company]) |
| APVendorCustomField | Returns the value of a custom field for the specified AP vendor. | APVendorCustomField(Field, VendorCode [,Company]) |
AP PAYMENTSThe payment-related functions in this section can be limited (via items expressions) by the following data:
|
||
| APPaymentsBank | Calculates total AP payments bank postings for the specified period. | APPaymentsBank(Period [,Items] [,Items] [,Company] [,Unit]) |
| APPaymentsBankYTD | Calculates the year-to-date AP payments bank postings for the specified period. | APPaymentsBankYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
| APPaymentsDiscount | Calculates total AP payments discount postings for the specified period. | APPaymentsDiscount(Period [,Items] [,Items] [,Company] [,Unit]) |
| APPaymentsDiscountYTD | Calculates the year-to-date AP payments discount postings for the specified period. | APPaymentsDiscountYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
AP EXTRA GLThe extra GL-related functions in this section can be limited (via items expressions) by the following data:
|
||
| APExtraActivity | Calculates the AP extra GL activity as of the specified period. | APExtraActivity(Period [,Items] [,Company] [,Unit]) |
| APExtraDebitActivity | Calculates the AP extra GL debit activity as of the specified period. | APExtraDebitActivity(Period [,Items] [,Company] [,Unit]) |
| APExtraCreditActivity | Calculates the AP extra GL credit activity as of the specified period. | APExtraCreditActivity(Period [,Items] [,Company] [,Unit]) |
| APExtraYearToDate | Calculates the AP extra GL year-to-date activity as of the specified period. | APExtraYearToDate(Period [,Items] [,Company] [,Unit]) |
| APExtraDebitYearToDate | Calculates the AP extra GL year-to-date debit activity as of the specified period. | APExtraDebitYearToDate(Period [,Items] [,Company] [,Unit]) |
| APExtraCreditYearToDate | Calculates the AP extra GL year-to-date credit activity as of the specified period. | APExtraCreditYearToDate(Period [,Items] [,Company] [,Unit]) |
AP ATTRIBUTES |
||
| APAttribute | Returns the name of the specified AP attribute. | APAttribute(AttributeName [,Company]) |
| APAttributePluralName | Returns the plural name of the specified AP attribute. | APAttributePluralName(AttributeName [,Company]) |
| APAttributeMemo | Returns the memo of the specified AP attribute. | APAttributeMemo(AttributeName [,Company]) |
| APAttributeCustomField | Returns the value of a custom field for the specified AP attribute. | APAttributeCustomField(Field, AttributeName [,Company]) |
AP ATTRIBUTE ITEMS |
||
| APAttributeItem | Returns the name of the specified AP attribute item. | APAttributeItem(AttributeName, Item [,Company]) |
| APAttributeItemDescription | Returns the description of the specified AP attribute item. | APAttributeItemDescription(AttributeName, Item [,Company]) |
| APAttributeItemMemo | Returns the memo of the specified AP attribute item. | APAttributeItemMemo(AttributeName, Item [,Company]) |
| APAttributeItemCustomField | Returns the value of a custom field for the specified AP attribute item. | APAttributeItemCustomField(Field, AttributeName, Item [,Company]) |
AP VENDOR CLASSES |
||
| APClass | Returns the code of the specified AP class. | APClass(APClassCode [,Company]) |
| APClassDescription | Returns the description of the specified AP class. | APClassDescription(APClassCode [,Company]) |
| APClassMemo | Returns the memo of the specified AP class. | APClassMemo(APClassCode [,Company]) |
| APClassCustomField | Returns the value of a custom field for the specified AP class. | APClassCustomField(Field, APClassCode [,Company]) |
Accounts Receivable functions provide drill down to Accounts Receivable information within a financial design worksheet. Use these functions to calculate totals based on AR information and to retrieve related information.
| Function | Description | Format |
|---|---|---|
AR TOTALSThese functions calculate totals using selected GL postings and permit drill down to AR detail. "Items" in this section refers to expressions such as segment items expressions, attribute items expressions, and invoice items expressions. You specify attributes with AR usage and attribute items using comparison operators. Example Suppose you have attributes named "Event" and "Department". An attribute expression might look something like this: Event=1545 AND Department LIKE Maintenance. Invoice items expressions are expressions in which you can limit amounts by customer, salesperson, customer class, and ARCode. Example Customer LIKE 1-* AND ARCode=TICKETS |
||
| ARSales | Calculates the total AR sales revenue for the specified period. | ARSales(Period [,Items] [,Items] [,Items] [,Company] [,Unit]) |
| ARSalesYTD | Calculates the year-to-date AR sales revenue for the specified period. | ARSalesYTD(Period [,Items] [,Items] [,Items] [,Company] [,Unit]) |
| ARQuantity | Calculates the total AR sales quantity for the specified period. | ARQuantity(Period [,Items] [,Items] [,Items] [,Company]) |
| ARQuantityYTD | Calculates the year-to-date AR sales quantity for the specified period. | ARQuantityYTD(Period [,Items] [,Items] [,Items] [,Company]) |
AR ATTRIBUTES |
||
| ARAttribute | Retrieves the name of the specified attribute with AR usage. | ARAttribute(AttributeName [,Company]) |
| ARAttributePluralName | Returns the plural name of the specified attribute with AR usage. | ARAttributePluralName(AttributeName [,Company]) |
| ARAttributeMemo | Returns any memo recorded on the specified attribute with AR usage. | ARAttributeMemo(AttributeName [,Company]) |
| ARAttributeCustomField | Returns the value of a custom field on the specified attribute with AR usage. | ARAttributeCustomField(Field, AttributeName [,Company]) |
AR ATTRIBUTE ITEMS |
||
| ARAttributeItem | Returns the name of the specified attribute item. | ARAttributeItem(AttributeName, Item [,Company]) |
| ARAttributeItemDescription | Returns the description of the specified attribute item. | ARAttributeItemDescription(AttributeName, Item [,Company]) |
| ARAttributeItemMemo | Returns any memo recorded on the specified attribute item. | ARAttributeItemMemo(AttributeName, Item [,Company]) |
| ARAttributeItemCustomField | Returns the value of a custom field on the specified attribute item. | ARAttributeItemCustomField(Field, AttributeName, Item [,Company]) |
AR CUSTOMER CLASSES |
||
| ARClass | Returns the specified AR customer class. | ARClass(ARClass [,Company]) |
| ARClassDescription | Returns the description of the specified AR customer class. | ARClassDescription(ARClass [,Company]) |
| ARClassMemo | Returns any memo recorded on the specified AR customer class. | ARClassMemo(ARClass [,Company]) |
| ARClassCustomField | Returns the value of a custom field on the specified AR customer class. | ARClassCustomField(Field, ARClass [,Company]) |
AR CUSTOMERS |
||
| ARCustomer | Returns the customer code of the specified customer. | ARCustomer(CustomerCode [,Company]) |
| ARCustomerName | Returns the name of the specified customer. | ARCustomerName(CustomerCode [,Company]) |
| ARCustomerMemo | Returns any memo recorded on the specified customer. | ARCustomerMemo(CustomerCode [,Company]) |
| ARCustomerCustomField | Returns the value of a custom field on the specified customer. | ARCustomerCustomField(Field, CustomerCode [,Company]) |
AR EXTRA GL |
||
| ARExtraActivity | Returns the net AR extra GL activity for the specified period. | ARExtraActivity(Period[,Items][,Items][,Items] [,Company] [,Unit]) |
| ARExtraDebitActivity | Returns the AR extra GL debit activity for the specified period. | ARExtraDebitActivity(Period[,Items][,Items][,Items] [,Company] [,Unit]) |
| ARExtraCreditActivity | Returns the AR extra GL credit activity for the specified period. | ARExtraCreditActivity(Period[,Items][,Items][,Items] [,Company] [,Unit]) |
| ARExtraYearToDate | Returns the year-to-date AR extra GL activity. | ARExtraYearToDate(Period[,Items][,Items][,Items] [,Company] [,Unit]) |
| ARExtraDebitYearToDate | Returns the year-to-date AR extra GL debit activity. | ARExtraDebitYearToDate(Period[,Items][,Items][,Items] [,Company] [,Unit]) |
| ARExtraCreditYearToDate | Returns the year-to-date extra GL credit activity. | ARExtraCreditYearToDate(Period[,Items][,Items][,Items] [,Company] [,Unit]) |
AR INVOICE TYPES |
||
| ARInvoiceType | Returns the invoice type code of the specified invoice type. | ARInvoiceType(InvoiceType [,Company]) |
| ARInvoiceTypeDescription | Returns the description of the specified invoice type. | ARInvoiceTypeDescription(InvoiceType [,Company]) |
| ARInvoiceTypeMemo | Returns the memo on the specified invoice type. | ARInvoiceTypeMemo(InvoiceType [,Company]) |
| ARInvoiceTypeCustomField | Returns the value of a custom field on the specified invoice type. | ARInvoiceTypeCustomField(Field, InvoiceType [,Company]) |
AR RECEIPT TYPES |
||
| ARReceiptType | Returns the receipt type code of the specified receipt type. | ARReceiptType(ReceiptType [,Company]) |
| ARReceiptTypeDescription | Returns the description of the specified receipt type. | ARReceiptTypeDescription(ReceiptType [,Company]) |
| ARReceiptTypeMemo | Returns the memo on the specified receipt type. | ARReceiptTypeMemo(ReceiptType [,Company]) |
| ARReceiptTypeCustomField | Returns the value of a custom field on the specified receipt type. | ARReceiptTypeCustomField(Field, ReceiptType [,Company]) |
AR RECEIPTSThe receipt-related functions in this section can be limited (via items expressions) by the following data: From the invoice:
From the invoice customer:
|
||
| ARReceiptsBank | Calculates the total bank receipts for the specified period. | ARReceiptsBank(Period [,Items] [,Items] [,Company] [,Unit]) |
| ARReceiptsBankYTD | Calculates the year-to-date bank receipts for the specified period. | ARReceiptsBankYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
| ARReceiptsDiscount | Calculates the total discounts given for the specified period. | ARReceiptsDiscount(Period [,Items] [,Items] [,Company] [,Unit]) |
| ARReceiptsDiscountYTD | Calculates the year-to-date discounts given for the specified period. | ARReceiptsDiscountYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
| ARReceiptsAdjustment | Calculates the total adjustments for the specified period. | ARReceiptsAdjustmentYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
| ARReceiptsAdjustmentYTD | Calculates the year-to-date adjustments for the specified period. | ARReceiptsAdjustmentYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
AR SALESPERSONS |
||
| ARSalesperson | Returns the salesperson code of the specified salesperson. | ARSalesperson(SalespersonCode [,Company]) |
| ARSalespersonFirstNameFirst | Returns the name, in "first name first" order, of the specified salesperson. | ARSalespersonFirstNameFirst(SalespersonCode [,Company]) |
| ARSalespersonLastNameFirst | Returns the name, in "last name first" order, of the specified salesperson. | ARSalespersonLastNameFirst(SalespersonCode [,Company]) |
| ARSalespersonMemo | Returns any memo recorded on the specified salesperson. | ARSalespersonMemo(SalespersonCode [,Company]) |
| ARSalespersonCustomField | Returns the value of a custom field on the specified salesperson. | ARSalespersonCustomField(Field, SalespersonCode [,Company]) |
ARCODES |
||
| ARCode | Returns the specified ARCode. | ARCode(ARCode [,Company]) |
| ARCodeDescription | Returns the description of the specified ARCode. | ARCodeDescription(ARCode [,Company]) |
| ARCodeMemo | Returns any memo recorded on the specified ARCode. | ARCodeMemo(ARCode [,Company]) |
| ARCodeCustomField | Returns the value of a custom field on the specified ARCode. | ARCodeCustomField(Field, ARCode [,Company]) |
Examples
ARSalesYTD("Current", "Main=4050 AND Ledger=A", , "ARCode=TICKETS"). Retrieves the year-to-date AR sales amount for the 4050 main GL account number limited to the "TICKETS" ARCode.
ARQuantityYTD("Current", "Main = 4050 AND Ledger=A", , "ARCode=TICKETS"). Retrieves the year-to-date quantity for number of tickets for the 4050 main GL account number limited to the "TICKETS" ARCode.
ARSales("Current", "Main=4050 AND Ledger=A", "Event LIKE 27*"). Retrieves period total sales for events that start with "27" for the 4050 main GL account number.
PRCode items expressions can be used in conjunction with payroll functions. For more information, click here.
Totals functions
The totals functions can be classified into one or more of these overlapping categories:
- Group expression-based. All begin with "PRGroup".
- PRCode items expression-based. All begin with "PRExpense" or "PRLiability".
- Expense-based calculations. All have "Expense" in their name.
- Liability-based calculations. All have "Liability" in their name.
- Result amounts. Do not have "Source" or "Count" in their name.
- Source amounts. All have "Source" in their name.
- Count amounts. A distinct count of employees who match. All have "Count" in their name.
- Period amounts. Do not have "YTD" in their name.
- Year-to-date amounts. All have "YTD" in their name.
| Function | Description | Format |
|---|---|---|
| PRCode-Based Expense Totals Functions - All PRCode-based expense functions return amounts from the expense or debit side of transactions. Employee deductions and employee taxes do not contribute to these amounts; statistics may contribute if they post to General Ledger. | ||
| PRExpense | Calculates the payroll expense total for the specified period. | PRExpense(Period [,Items] [,Items] [,Items] [,Company] [,Unit])
Note For PRExpense financial expressions which refer to an attribute which has both GL and PR usage, you must use the prefix "GL.Attribute" or "PR.Attribute" in order to support certain attribute options. |
| PRExpenseSource | Calculates the payroll expense source total for the specified period. | PRExpenseSource(Period [,Items] [,Items] [,Items] [,Company]) |
| PRExpenseYTD | Calculates the year-to-date expense total for the specified period. | PRExpenseYTD(Period [,Items] [,Items] [,Items] [,Company] [,Unit]) |
| PRExpenseSourceYTD | Calculates the year-to-date expense source total for the specified period. | PRExpenseSourceYTD(Period [,Items] [,Items] [,Items] [,Company]) |
| PRExpenseCount | Calculates the count of employees with expenses for the specified period. | PRExpenseCount(Period [,Items] [,Items] [,Items] [,Company]) |
| PRExpenseCountYTD | Calculates the count of employees with year-to-date expenses as of the specified period. | PRExpenseCountYTD(Period [,Items] [,Items] [,Items] [,Company]) |
| PRCode-Based Liability Totals Functions - All PRCode-based liability functions return amounts from the liability or credit side of the transactions. Pay and statistic lines do not contribute to these amounts. | ||
| PRLiability | Calculates the payroll liability total for the specified period. | PRLiability(Period [,Items] [,Items] [,Items] [,Company] [,Unit]) |
| PRLiabilitySource | Calculates the payroll liability source total for the specified period. | PRLiabilitySource(Period [,Items] [,Items] [,Items] [,Company]) |
| PRLiabilityYTD | Calculates the year-to-date payroll liability total for the specified period. | PRLiabilityYTD(Period [,Items] [,Items] [,Items] [,Company] [,Unit]) |
| PRLiabilitySourceYTD | Calculates the year-to-date payroll liability source total for the specified period. | PRLiabilitySourceYTD(Period [,Items] [,Items] [,Items] [,Company]) |
| PRLiabilityCount | Calculates the count of employees with liabilities for the specified period. | PRLiabilityCount(Period [,Items] [,Items] [,Items] [,Company]) |
| PRLiabilityCountYTD | Calculates the count of employees with year-to-date liabilities as of the specified period. | PRLiabilityCountYTD(Period [,Items] [,Items] [,Items] [,Company]) |
| Group-Based Expense Totals Functions | ||
| PRGroupExpense | Calculates the payroll group total of expenses for the specified period. | PRGroupExpense(Group, Period [,Items] [,Items] [,Company] [,Unit]) |
| PRGroupExpenseYTD | Calculates the year-to-date payroll group total of expenses for the specified period. | PRGroupExpenseYTD(Group, Period [,Items] [,Items] [,Company] [,Unit]) |
| Group-Based Liability Totals Functions | ||
| PRGroupLiability | Calculates the payroll group total of liabilities for the specified period. | PRGroupLiability(Group, Period [,Items] [,Items] [,Company] [,Unit]) |
| PRGroupLiabilityYTD | Calculates the year-to-date payroll group total of liabilities for the specified period. | PRGroupLiabilityYTD(Group, Period [,Items] [,Items] [,Company] [,Unit]) |
Lookup functions
| Function | Description | Format |
|---|---|---|
| PRCodeSegment | Returns the name of the specified PRCode segment. | PRCodeSegment(Segment [,Company]) |
| PRCodeSegmentDescription | Returns the description of the specified PRCode segment. | PRCodeSegmentDescription(Segment [,Company]) |
| PRCodeSegmentPluralName | Returns the plural name of the specified PRCode segment. | PRCodeSegmentPluralName(Segment [,Company]) |
| PRCodeSegmentMemo | Returns any memo recorded on the specified PRCode segment. | PRCodeSegmentMemo(Segment [,Company]) |
| PRCodeSegmentCustomField | Returns the value of a custom field for the specified PRCode segment. | PRCodeSegmentCustomField(Field, Segment [,Company]) |
| PRCodeItem | Returns the code of the PRCode item for the specified PRCode segment and item. | PRCodeItem(Segment, Item [,Company]) |
| PRCodeItemDescription | Returns the description of the PRCode item for the specified PRCode segment and item. | PRCodeItemDescription(Segment, Item [,Company]) |
| PRCodeItemShortDescription | Returns the short description of the PRCode item for the specified PRCode segment and item. | PRCodeItemShortDescription(Segment, Item [,Company]) |
| PRCodeItemMemo | Returns any memo recorded for the PRCode item of the specified PRCode segment and item. | PRCodeItemMemo(Segment, Item [,Company]) |
| PRCodeItemCustomField | Returns the value of a custom field for the PRCode item of the specified PRCode segment and item. | PRCodeItemCustomField(Field, Segment, Item [,Company]) |
| PRGroup | Returns the name of the specified payroll group. | PRGroup(Group [,Company]) |
| PRGroupDescription | Returns the description of the specified payroll group. | PRGroupDescription(Group [,Company]) |
| PRGroupSourceName | Returns the source name of the specified payroll group. | PRGroupSourceName(Group [,Company]) |
| PRGroupResultName | Returns the result name of the specified payroll group. | PRGroupResultName(Group [,Company]) |
| PRGroupMemo | Returns any memo recorded on the specified payroll group. | PRGroupMemo(Group [,Company]) |
| PRGroupCustomField | Returns the value of a custom field for the specified payroll group. | PRGroupCustomField(Field, Group [,Company]) |
| PRTaxEntity | Returns the code of the specified tax entity. | PRTaxEntity(TaxEntity [,Company]) |
| PRTaxEntityDescription | Returns the description of the specified tax entity. | PRTaxEntityDescription(TaxEntity [,Company]) |
| PRTaxEntityMemo | Returns any memo recorded on the specified tax entity. | PRTaxEntityMemo(TaxEntity [,Company]) |
| PRTaxEntityCustomField | Returns the value of a custom field for the specified tax entity. | PRTaxEntityCustomField(Field, TaxEntity [,Company]) |
| PRAttribute | Returns the name of the specified attribute with Payroll usage. | PRAttribute(Attribute [,Company]) |
| PRAttributePluralName | Returns the plural name of the specified attribute with Payroll usage. | PRAttributePluralName(Attribute [,Company]) |
| PRAttributeMemo | Returns any memo recorded on the specified attribute with Payroll usage. | PRAttributeMemo(Attribute [,Company]) |
| PRAttributeCustomField | Returns the value of a custom field for the specified attribute with Payroll usage. | PRAttributeCustomField(Field, Attribute [,Company]) |
| PRAttributeItem | Returns the code of the specified attribute item. | PRAttributeItem(Attribute, Item [,Company]) |
| PRAttributeItemDescription | Returns the description of the specified attribute item. | PRAttributeItemDescription(Attribute, Item [,Company]) |
| PRAttributeItemMemo | Returns any memo recorded on the specified attribute item. | PRAttributeItemMemo(Attribute, Item [,Company]) |
| PRAttributeItemCustomField | Returns the value of a custom field for the specified attribute item. | PRAttributeItemCustomField(Field, Attribute, Item [,Company]) |
Purchasing functions provide drill down to Purchasing information in a financial design worksheet. Use these functions to calculate totals based on Purchasing information and to retrieve related information.
| Function | Description | Format |
|---|---|---|
PURCHASING TOTALSThese functions calculate totals using GL accounts and provide drill down to Purchasing detail. |
||
| POAmount | Calculates the total extended amount of purchases for the specified period. (Extended amounts include any allocated amounts.) | POAmount(Period [,Items] [,Items] [,Company] [,Unit]) |
| POAmountYTD | Calculates the year-to-date extended amount of purchases as of the specified period. (Extended amounts include any allocated amounts.) | POAmountYTD(Period [,Items] [,Items] [,Company] [,Unit]) |
| POQuantity | Calculates the total quantity of purchases for the specified period. | POQuantity(Period [,Items] [,Items] [,Company]) |
| POQuantityYTD | Calculates the year-to-date quantity of purchases as of the specified period. | POQuantityYTD(Period [,Items] [,Items] [,Company]) |
PURCHASING ATTRIBUTES |
||
| POAttribute | Returns the name of the specified attribute with PO usage. | POAttribute(AttributeName [,Company]) |
| POAttributeDescription | Returns the description of the specified attribute with PO usage. | POAttributeDescription(AttributeName [,Company]) |
| POAttributeMemo | Returns any memo recorded for the specified attribute with PO usage. | POAttributeMemo(AttributeName [,Company]) |
| POAttributeCustomField | Returns the value of a custom field for the specified attribute with PO usage. | POAttributeCustomField(Field, AttributeName [,Company]) |
PURCHASING ATTRIBUTE ITEMS |
||
| POAttributeItem | Returns the name of the specified attribute item. | POAttributeItem(AttributeName, Item [,Company]) |
| POAttributeItemDescription | Returns the description of the specified attribute item. | POAttributeItemDescription(AttributeName, Item [,Company]) |
| POAttributeItemMemo | Returns any memo recorded for the specified attribute item. | POAttributeItemMemo(AttributeName, Item [,Company]) |
| POAttributeItemCustomField | Returns the value of a custom field for the specified attribute item. | POAttributeItemCustomField(Field, AttributeName, Item [,Company]) |
PURCHASING DEPARTMENTS |
||
| PODepartment | Returns the name of the specified purchasing department. | PODepartment(Department [,Company]) |
| PODepartmentDescription | Returns the description of the specified purchasing department. | PODepartmentDescription(Department [,Company]) |
| PODepartmentMemo | Returns any memo recorded for the specified purchasing department. | PODepartmentMemo(Department [,Company]) |
| PODepartmentCustomField | Returns the value of a custom field for the specified purchasing department. | PODepartmentCustomField(Field, Department [,Company]) |
PURCHASING PRODUCTS |
||
| POProduct | Returns the name of the specified product. | POProduct(Product [,Company]) |
| POProductDescription | Returns the description of the specified product. | POProductDescription(Product [,Company]) |
| POProductMemo | Returns any memo recorded for the specified product. | POProductMemo(Product [,Company]) |
| POProductCustomField | Returns the value of a custom field for the specified product. | POProductCustomField(Field, Product [,Company]) |
PURCHASING UNITS OF MEASURE |
||
| POUnitOfMeasure | Returns the name of the specified unit of measure. | POUnitOfMeasure(UnitOfMeasure [,Company]) |
| POUnitOfMeasureDescription | Returns the description of the specified unit of measure. | POUnitOfMeasureDescription(UnitOfMeasure [,Company]) |
| POUnitOfMeasureMemo | Returns any memo recorded for the specified unit of measure. | POUnitOfMeasureMemo(UnitOfMeasure [,Company]) |
| POUnitOfMeasureCustomField | Returns the value of a custom field for the specified unit of measure. | POUnitOfMeasureCustomField(Field, UnitOfMeasure [,Company]) |
VENDORS |
||
| Vendor | Returns the name of the specified vendor. | Vendor(Vendor [,Company]) |
| VendorDescription | Returns the description of the specified vendor. | VendorDescription(Vendor [,Company]) |
| VendorMemo | Returns any memo recorded for the specified vendor. | VendorMemo(Vendor [,Company]) |
| VendorCustomField | Returns the value of a custom field for the specified vendor. | VendorCustomField(Field, Vendor [,Company]) |
Examples
PODepartmentDescription("Accounting"). Retrieves the description of the accounting department.
POQuantity("Current", "Main LIKE 5* AND Ledger=A", "Product=Camera AND Event=Fair"). Returns the quantity of purchases for the current period for all GL cost accounts limited to the "Camera" product and the "Fair" event.
POAmountYTD("Current", "Main LIKE 5* AND Ledger=A", "Vendor=Wholesale"). Retrieves the year-to-date amount of purchases for all GL cost accounts for the "Wholesale" vendor.
POAmount("Current", "Main=5520 AND Ledger=A", "Event=Race AND UOM=Each") or POAmount("Current", "Main=5520 AND Ledger=A", "Event=Race AND 'Unit of Measure'=Each"). Retrieves the current amount of purchases for the 5520 main GL account number limited to the "Race" event attribute and with "Each" as the unit of measure.
Note
Attribute names that contain spaces (such as "Unit of Measure" in the last example) must be enclosed in single quotes.
PRCode items expressions
PRCode items expressions are similar to segment items expressions except they determine payroll information based on PRCode segment items in their PRCode segments. The general form of a PRCode items expression is:
[PRCODESEGMENT <operator> PRCODEITEM]
The <operator> is one of the following: =, <>, <, >, <=, >=, LIKE, NOT LIKE.
Examples
- Pay=REG
- Tax=SUTA
- Deduction=HI AND HIProvider=BC
Syntax rules
- PRCode items expressions are optional.
- PRCode items expressions can be combined with ANDs and ORs as in the third example above.
- PRCode items expressions can be grouped using parentheses.
- PRCode items expressions can be negated.
- PRCode items expressions support the IN function. The IN function allows you to mix group assignment tests with other operations. The argument of the IN function is a group expression minus the amount.
Example
(Pay=REG OR Pay=SAL) AND Ded=401K
Example
NOT Ded=DENTAL
Example
IN(Compensation) AND Pay=REG
"Compensation" is the name of a check line group.
|
5225 S Loop 289, #207 Lubbock, TX 79424 806.687.8500 | 800.354.7152 |
© 2025 AccountingWare, LLC All rights reserved. |