Financial designs

Essentially, a financial design is an Excel worksheet that incorporates labels, ActivReporter functions, and Excel formulas to produce a customized financial statement. Because the functions are generalized, you can reuse a financial design from period to period and year to year.

Note

Financial designs always create a file in a certain baseline Excel file format regardless of which version of Excel is installed on your workstation. This means users can generally open and work with financial design worksheets no matter which version of Excel is installed.

Financial design worksheets can be created and stored in ActivReporter or external to it. There are several advantages to maintaining your designs within ActivReporter:

  • Worksheets are backed up with the rest of the ActivReporter database.
  • It is easy to share your worksheets with other ActivReporter users.
  • Worksheets can be secured with standard ActivReporter security.
  • Worksheets are versioned so that historical worksheets can be viewed and restored.

Preparing to design a financial statement

Before you start designing a financial, examine your current financial statement. Note that it consists of rows and columns. Each row and each column on the statement has an identifying characteristic or rule that can be associated with the row or column.

Rows typically identify which account or group of accounts appear on that row. In most cases, the group of accounts relates to a rollup code. Rows may also contain formulas which total sections of the report.

Columns usually either contain descriptions of lines, financial data, budget data, or calculations (such as variances and/or percentages). This type of information is retrieved from ActivReporter via financial functions. Often the formula for all cells in a column section is the same so that you can enter a formula in the cell at the top of the column section and drag down to copy the formula to all rows of that section of the financial.

The purpose of the remaining information in this topic is to help you translate your paper statements into generalized designs which can be used today and for years to come.

Create a financial design record

Work with design versions

Load a design from an external location

Save a design to an external location

Delete a financial design record

x

Account Conditions

Condition Expression

Enter a SQL condition statement here to exclude or include information from the financial view. Alternatively, you can use the table below to describe the conditions you need and ActivReporter will build and add the conditions to this text box.

Conditions table
Use this table to construct the conditions you need in order to accurately select information for the financial view.
Item
From the drop-down list, select the account entity, or the segment, rollup, department, etc., to define a range or mask on. The items in the dropdown are filtered based on the selected function and are represented by their fully qualified names.
Mask

Enter a mask for the selected item values when the values you need to select have common characters but are not in a particular order.

Press F3 to look up the value.

If a value is already selected, you can press F4 to open the record in its native editor.

Begin

Enter the first value in the range of item values you need to select. Use a range when the values are in a defined order.

Press F3 to look up the value.

If a value is already selected, you can press F4 to open the record in its native editor.

End

Enter the last value in the range of item values you need to select. Use a range when the values are in a defined order.

Press F3 to look up the value.

If a value is already selected, you can press F4 to open the record in its native editor.

Check Conditions button
Click this button to check for SQL errors in the condition statement.

 

x

Financial Functions

Activity

Calculates the activity of a set of accounts as of the specified period.

Activity(Period,Items1,Items2,Company,Unit)

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.

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.

Balance

Calculates the balance of a set of accounts as of the specified period.

Balance(Period,Items1,Items2,Company,Unit)

Examples

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.

BeginBalance

Calculates the beginning balance of a set of accounts as of the specified period.

BeginBalance(Period,Items1,Items2,Company,Unit)

YearToDate

Calculates the year-to-date activity of a set of accounts as of the specified period.

YearToDate(Period,Items1,Items2,Company,Unit)

Examples

YearToDate("Current", "Inc=Supplies", "Ledger=A")

Returns the year-to-date through this month of the office supply expenses.

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.

DebitActivity

Calculates the debit activity of a set of accounts as of the specified period.

DebitActivity(Period,Items1,Items2,Company,Unit)

Example

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.

DebitBalance

Calculates the debit balance of a set of accounts as of the specified period.

DebitBalance(Period,Items1,Items2,Company,Unit)

DebitYearToDate

Calculates the year-to-date debit activity of a set of accounts as of the specified period.

DebitYearToDate(Period,Items1,Items2,Company,Unit)

CreditActivity

Calculates the credit activity of a set of accounts as of the specified period.

CreditActivity(Period,Items1,Items2,Company,Unit)

Example

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.

CreditBalance

Calculates the credit balance of a set of accounts as of the specified period.

CreditBalance(Period,Items1,Items2,Company,Unit)

CreditYearToDate

Calculates the year-to-date credit activity of a set of accounts as of the specified period.

CreditYearToDate(Period,Items1,Items2,Company,Unit)

 

x

Segments Functions

Segment

Returns the name of the specified segment.

Segment(SegmentCode,Company)

SegmentPluralName

Returns the plural name of the specified segment.

SegmentPluralName(SegmentCode,Company)

SegmentMemo

Returns the memo of the specified segment.

SegmentMemo(SegmentCode,Company)

SegmentCustomField

Returns the value of a custom field for the specified segment.

SegmentCustomField(CustomField,SegmentCode,Company)

SegmentItem

Returns the segment item of the specified segment and optional item.

SegmentItem(Segment,Item,Company)

SegmentItemDescription

Returns the description of the segment item for the specified segment and optional item.

SegmentItemDescription(Segment,Item,Company)

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.

SegmentItemShortDescription

Returns the short description of the segment item for the specified segment and optional item.

SegmentItemShortDescription(Segment,Item,Company)

SegmentItemName

Returns the name of the segment item for the specified segment and optional item.

SegmentItemName(Segment,Item,Company)

SegmentItemAddress1

Returns the first address line of the segment item for the specified segment and optional item.

SegmentItemAddress1(Segment,Item,Company)

SegmentItemAddress2

Returns the second address line of the segment item for the specified segment and optional item.

SegmentItemAddress2(Segment,Item,Company)

SegmentItemCity

Returns the city of the segment item for the specified segment and optional item.

SegmentItemCity(Segment,Item,Company)

SegmentItemState

Returns the state of the segment item for the specified segment and optional item.

SegmentItemState(Segment,Item,Company)

SegmentItemZipCode

Returns the Zip Code of the segment item for the specified segment and optional item.

SegmentItemZipCode(Segment,Item,Company)

SegmentItemCountry

Returns the country of the segment item for the specified segment and optional item.

SegmentItemCountry(Segment,Item,Company)

SegmentItemMemo

Returns the memo of the segment item for the specified segment and optional item.

SegmentItemMemo(Segment,Item,Company)

SegmentItemCustomField

Returns the custom field value of the segment item for the specified segment and optional item.

SegmentItemCustomField(CustomField,Segment,Item,Company)

 

x

Accounts Functions

Account

Returns the account code of the specified account.

Account(AccountCode,Company)

AccountDescription

Returns the description of the specified account.

AccountDescription(AccountCode,Company)

AccountMemo

Returns the memo for the specified account.

AccountMemo(AccountCode,Company)

AccountCustomField

Returns the value of a custom field for the specified account.

AccountCustomField(Field,AccountCode,Company)

 

x

Periods Functions

As of Period End

A hybrid Excel-ActivReporter function which produces a heading which includes the period end date. Uses optional "As of" date if specified.

= "As of " & TEXT(PeriodEnd(), "mmmm dd, yyyy")

For the N Month(s) Ending

A hybrid Excel-ActivReporter function which produces a heading with the number of months ending (Month Day, Year) for the specified period. Uses optional "As of" date if specified.

= "For the " & getdigit(PeriodNumber()) & " Month(s) Ending " & TEXT(PeriodEnd(), "mmmm dd, yyyy")

For the Period Beginning

A hybrid Excel-ActivReporter function which produces a heading which includes period begin date. Uses optional "As of" date if specified.

= "For the Period Beginning " & TEXT(PeriodBegin(), "mmmm dd, yyyy")

For the Period Ending

A hybrid Excel-ActivReporter function which produces a heading which includes the period end date. Uses optional "As of" date if specified.

= "For the Period Ending " & TEXT(PeriodEnd(), "mmmm dd, yyyy")

For the Period Beginning and Ending

A hybrid Excel-ActivReporter function which produces a heading which includes the period begin and end dates. Uses optional "As of" date if specified.

= "For the Period Beginning " & TEXT(PeriodBegin(), "mmmm dd, yyyy") & " and Ending " & TEXT(PeriodEnd(), "mmmm dd, yyyy")


PeriodNumber

Returns the period number of the specified period.

PeriodNumber(Period,Company)

PeriodDescription

Returns the description of the specified period.

PeriodDescription(Period,Company)

PeriodYear

Returns the year of the specified period.

PeriodYear(Period,Company)

Example

PeriodYear("Current")

Prints the year in a column heading.

PeriodYearDescription

Returns the description of the year for the specified period.

PeriodYearDescription(Period,Company)

PeriodCalendar

Returns the calendar of the specified period.

PeriodCalendar(Period,Company)

PeriodCalendarDescription

Returns the description of the calendar for the specified period.

PeriodCalendarDescription(Period,Company)

PeriodCalendarMemo

Returns the memo on the calendar for the specified period.

PeriodCalendarMemo(Period,Company)

PeriodCalendarCustomField

Returns the value of the custom field on the calendar for the specified period.

PeriodCalendarCustomField(CustomField,Period,Company)

PeriodWorkdays

Returns the number of workdays in the specified period.

PeriodWorkdays(Period,Company)

PeriodBegin

Returns the beginning date of the specified period.

PeriodBegin(Period,Company)

PeriodEnd

Returns the ending date of the specified period.

PeriodEnd(Period,Company)

Examples

PeriodEnd("Current")

Retrieves the ending date of the current period.

"As of" & Text(PeriodEnd("Current"), "mmmm dd, yyyy")

Combines Excel formulas and ActivReporter 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.

PeriodYearBegin

Returns the beginning date of the year for the specified period.

PeriodYearBegin(Period,Company)

PeriodYearEnd

Returns the ending date of the year for the specified period.

PeriodYearEnd(Period,Company)

AsOf

Returns the specified "as of" date if specified and 0 if it is not.

AsOf(Date)

Example

IF(AsOf()=0,"","As of "&TEXT(AsOf(),"mm/dd/yyyy"))

Displays "As of <date>" when the as of date is specified and nothing when it is not.

 

x

Miscellaneous Functions

AccountItem

Accepts a partial account code and a ledger and returns a full account code.

AccountItem(Account,Ledger)

AccountExpression

Accepts a partial account code and a ledger and returns an expression that can be used to limit a financial function.

AccountExpression(Account,Ledger)

ItemExpression

Accepts an unlimited number of item type and item value pairs and returns an expression that can be used to limit a financial function.

ItemExpression(Item1,Value1,Item2,Value2,Item3,Value3,...)

Example

ItemExpression("Project", "100", "Dept", "01") = "(Project=100 AND Dept=01)"

Iteration
IterationItem

Accepts no arguments and returns a list of the iteration items that are applied to the financial, or accepts an item type and returns the iteration item of that type.

IterationItem(ItemType,Company)

IterationDescription

Accepts no arguments and returns a list of iteration item descriptions that are applied to the financial, or accepts an item type and returns the iteration item description of that type.

IterationDescription(ItemType,Company)

IterationShortDescription

Accepts no arguments and returns a list of iteration item short descriptions that are applied to the financial, or accepts an item type and returns the iteration item short description of that type.

IterationShortDescription(ItemType,Company)

Units
Unit

Accepts a unit and returns the code of the unit, or accepts no unit and returns the code of the currency unit from the Workbook Options dialog box.

Unit(Unit,Company)

UnitDescription

Accepts a unit and returns the description of the unit, or accepts no unit and returns the description of the currency unit from the Workbook Options dialog box.

UnitDescription(Unit,Company)

UnitMemo

Accepts a unit and returns the memo from the unit, or accepts no unit and returns the memo from the currency unit from the Workbook Options dialog box.

UnitMemo(Unit,Company)

UnitCustomField

Accepts a unit and returns the custom field value for the specified custom field on the unit, or accepts no unit and returns the custom field value for the specified custom field on the currency unit from the Workbook Options dialog box.

Unit(Field,Unit,Company)

IsLiveData

Returns "true" if the data is live; otherwise, returns "false".

IsLiveData(Company)

 

x

Company Functions

CompanyName

Returns the name of the optional company.

CompanyName(Company)

CompanyLegalName

Returns the legal name of the optional company.

CompanyLegalName(Company)

CompanyAddress1

Returns the first address line for the optional company.

CompanyAddress1(Company)

CompanyAddress2

Returns the second address line for the optional company.

CompanyAddress2(Company)

CompanyCity

Returns the city for the optional company.

CompanyCity(Company)

CompanyState

Returns the state for the optional company.

CompanyState(Company)

CompanyZipCode

Returns the Zip Code for the optional company.

CompanyZipCode(Company)

CompanyCountry

Returns the country of the optional company.

CompanyCountry(Company)

CompanyMemo

Returns the memo for the optional company.

CompanyMemo(Company)

CompanyCustomField

Returns the value of the custom field for the optional company.

CompanyCustomField(CustomField,Company)

 

x

Validate Functions

ValidateAccounts

Returns "true" if all accounts are valid and no missing or duplicate accounts are found. Warnings appear in the calculation log.

ValidateAccounts(CheckMissing,CheckDuplicates,Ledger,Accounts)

ValidateItems

Returns "true" if all items are valid and no missing or duplicate items are found. Warnings appear in the calculation log.

ValidateItems(CheckMissing,CheckDuplicates,ItemType,Values)

ErrorsOrWarnings

Returns a message if there are errors or warnings in the calculation log. The default message is: "Review needed: Calculation Log contains errors or warnings".

ErrorsOrWarnings(Message)

 

x

Accounts Receivable Functions

ARSales

Calculates the total AR sales (revenue) for the specified period.

ARSales(Period,Items1,Items2,Items3,Company,Unit)

Example

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.

ARSalesYTD

Calculates the year-to-date AR sales (revenue) for the specified period.

ARSalesYTD(Period,Items1,Items2,Items3,Company,Unit)

Example

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.

ARSalesQuantity

Calculates the total AR sales quantity for the specified period.

ARSalesQuantity(Period,Items1,Items2,Items3,Company)

ARSalesQuantityYTD

Calculates the year-to-date AR sales quantity for the specified period.

ARSalesQuantityYTD(Period,Items1,Items2,Items3,Company)

ARExtraActivity

Calculates the extra GL activity as of the specified period.

ARExtraActivity(Period,Items,Company,Unit)

ARExtraDebitActivity

Calculates the extra GL debit activity as of the specified period.

ARExtraDebitActivity(Period,Items,Company,Unit)

ARExtraCreditActivity

Calculates the extra GL credit activity as of the specified period.

ARExtraCreditActivity(Period,Items,Company,Unit)

ARExtraYearToDate

Calculates the year-to-date extra GL activity as of the specified period.

ARExtraYearToDate(Period,Items,Company,Unit)

ARExtraDebitYearToDate

Calculates the year-to-date extra GL debit activity as of the specified period.

ARExtraDebitYearToDate(Period,Items,Company,Unit)

ARExtraCreditYearToDate

Calculates the year-to-date extra GL credit activity as of the specified period.

ARExtraCreditYearToDate(Period,Items,Company,Unit)

ARCodes
ARCode

Returns the code of the specified ARCode.

ARCode(ARCode,Company)

ARCodeDescription

Returns the description of the specified ARCode.

ARCodeDescription(ARCode,Company)

ARCodeMemo

Returns the memo on the specified ARCode.

ARCodeMemo(ARCode,Company)

ARCodeCustomField

Returns the value of a custom field on the specified ARCode.

ARCodeCustomField(CustomField,ARCode,Company)

Attributes
ARAttribute

Returns the name of the specified attribute.

ARAttribute(AttributeName,Company)

ARAttributePluralName

Returns the plural name of the specified attribute.

ARAttributePluralName(AttributeName,Company)

ARAttributeMemo

Returns the memo on the specified attribute.

ARAttributeMemo(AttributeName,Company)

ARAttributeCustomField

Returns the value of a custom field on the specified attribute.

ARAttributeCustomField(CustomField,AttributeName,Company)

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 the memo on the specified attribute item.

ARAttributeItemMemo(AttributeName,Item,Company)

ARAttributeItemCustomField

Returns the value of a custom field on the specified attribute item.

ARAttributeItemCustomField(CustomField,AttributeName,Item,Company)

Customers
ARCustomer

Returns the customer code of the specified AR customer.

ARCustomer(CustomerCode,Company)

ARCustomerName

Returns the name of the specified AR customer.

ARCustomerName(CustomerCode,Company)

ARCustomerMemo

Returns the memo on the specified AR customer.

ARCustomerMemo(CustomerCode,Company)

ARCustomerCustomField

Returns the value of a custom field on the specified AR customer.

ARCustomerCustomField(CustomField,CustomerCode,Company)

Customer Classes
ARClass

Returns the class code of the specified AR class.

ARClass(ARClassCode,Company)

ARClassDescription

Returns the description of the specified AR class.

ARClassDescription(ARClassCode,Company)

ARClassMemo

Returns the memo on the specified AR class.

ARClassMemo(ARClassCode,Company)

ARClassCustomField

Returns the value of a custom field on the specified AR class.

ARClassCustomField(CustomField,ARClassCode,Company)

Salespersons
ARSalesperson

Returns the salesperson code of the specified salesperson.

ARSalesperson(SalespersonCode,Company)

ARSalespersonFirstNameFirst

Returns the name of the specified salesperson with first name first.

ARSalespersonFirstNameFirst(SalespersonCode,Company)

ARSalespersonLastNameFirst

Returns the name of the specified salesperson with last name first.

ARSalespersonLastNameFirst(SalespersonCode,Company)

ARSalespersonMemo

Returns the memo on the specified salesperson.

ARSalespersonMemo(SalespersonCode,Company)

ARSalespersonCustomField

Returns the value of a custom field on the specified salesperson.

ARSalespersonCustomField(CustomField,SalespersonCode,Company)

 

x

Payroll Functions

PRExpense

Calculates the payroll expense total for the specified period.

PRExpense(Period,Items1,Items2,Items3,Company,Unit)

PRExpenseSource

Calculates the payroll expense source total for the specified period.

PRExpenseSource(Period,Items1,Items2,Items3,Company)

PRLiability

Calculates the payroll liability total for the specified period.

PRLiability(Period,Items1,Items2,Items3,Company,Unit)

PRLiabilitySource

Calculates the payroll liability source total for the specified period.

PRLiabilitySource(Period,Items1,Items2,Items3,Company)

PRExpenseYTD

Calculates the year-to-date payroll expense total for the specified period.

PRExpenseYTD(Period,Items1,Items2,Items3,Company,Unit)

PRExpenseSourceYTD

Calculates the year-to-date payroll expense source total for the specified period.

PRExpenseSourceYTD(Period,Items1,Items2,Items3,Company)

PRLiabilityYTD

Calculates the year-to-date payroll liability total for the specified period.

PRLiabilityYTD(Period,Items1,Items2,Items3,Company,Unit)

PRLiabilitySourceYTD

Calculates the year-to-date payroll liability source total for the specified period.

PRLiabilitySourceYTD(Period,Items1,Items2,Items3,Company)

PRGroupExpense

Calculates the payroll group total of expenses for the specified period.

PRGroupExpense(Group,Period,Items1,Items2,Company,Unit)

PRGroupLiability

Calculates the payroll group total of liabilities for the specified period.

PRGroupLiability(Group,Period,Items1,Items2,Company,Unit)

PRGroupExpenseYTD

Calculates the year-to-date payroll group total of expenses for the specified period.

PRGroupExpenseYTD(Group,Period,Items1,Items2,Company,Unit)

PRGroupLiabilityYTD

Calculates the year-to-date payroll group total of liabilities for the specified period.

PRGroupLiabilityYTD(Group,Period,Items1,Items2,Company,Unit)

PRExpenseCount

Calculates the count of employees with expenses for the specified period.

PRExpenseCount(Period,Items1,Items2,Items3,Company)

PRExpenseCountYTD

Calculates the count of employees with year-to-date expenses for the specified period.

PRExpenseCount(Period,Items1,Items2,Items3,Company)

PRLiabilityCount

Calculates the count of employees with liabilities for the specified period.

PRLiabilityCount(Period,Items1,Items2,Items3,Company)

PRLiabilityCountYTD

Calculates the count of employees with year-to-date liabilities for the specified period.

PRLiabilityCountYTD(Period,Items1,Items2,Items3,Company)

Attributes
PRAttribute

Returns the name of the specified payroll attribute.

PRAttribute(AttributeName,Company)

PRAttributePluralName

Returns the plural name of the specified payroll attribute.

PRAttributePluralName(AttributeName,Company)

PRAttributeMemo

Returns the memo on the specified payroll attribute.

PRAttributeMemo(AttributeName,Company)

PRAttributeCustomField

Returns the value of a custom field on the specified payroll attribute.

PRAttributeCustomField(CustomField,AttributeName,Company)

PRAttributeItem

Returns the code of the specified payroll attribute item.

PRAttributeItem(AttributeName,Item,Company)

PRAttributeItemDescription

Returns the description of the specified payroll attribute item.

PRAttributeItemDescription(AttributeName,Item,Company)

PRAttributeItemMemo

Returns the memo on the specified payroll attribute item.

PRAttributeItemMemo(AttributeName,Item,Company)

PRAttributeItemCustomField

Returns the value of a custom field on the specified payroll attribute item.

PRAttributeItemCustomField(CustomField,AttributeName,Item,Company)

Groups
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 the memo on the specified payroll group.

PRGroupMemo(Group,Company)

PRGroupCustomField

Returns the value of a custom field on the specified payroll group.

PRGroupCustomField(CustomField,Group,Company)

PRCodes
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 the memo on the specified PRCode segment.

PRCodeSegmentMemo(Segment,Company)

PRCodeSegmentCustomField

Returns the value of a custom field on the specified PRCode segment.

PRCodeSegmentCustomField(CustomField,Segment,Company)

PRCodeItem

Returns the PRCode item of the specified PRCode segment and item.

PRCodeItem(Segment,Item,Company)

PRCodeItemDescription

Returns the description of the PRCode item of the specified PRCode segment and item.

PRCodeItemDescription(Segment,Item,Company)

PRCodeItemShortDescription

Returns the short description of the PRCode item of the specified PRCode segment and item.

PRCodeItemShortDescription(Segment,Item,Company)

PRCodeItemMemo

Returns the memo on the PRCode item of the specified PRCode segment and item.

PRCodeItemMemo(Segment,Item,Company)

PRCodeItemCustomField

Returns the value of a custom field on the PRCode item of the specified PRCode segment and item.

PRCodeItemCustomField(CustomField,Segment,Item,Company)

Tax Entities
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 the memo on the specified tax entity.

PRTaxEntityMemo(TaxEntity,Company)

PRTaxEntityCustomField

Returns the value of a custom field on the specified tax entity.

PRTaxEntityCustomField(CustomField,TaxEntity,Company)

 

x

Purchasing Functions

POAmount

Calculates the total purchase amount for the specified period.

POAmount(Period,Items1,Items2,Company,Unit)

Example

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.

POAmountYTD

Calculates the year-to-date total purchase amount for the specified period.

POAmountYTD(Period,Items1,Items2,Company,Unit)

Example

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.

POQuantity

Calculates the total purchase quantity for the specified period.

POQuantity(Period,Items1,Items2,Company)

Example

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.

POQuantityYTD

Calculates the year-to-date total purchase quantity for the specified period.

POQuantityYTD(Period,Items1,Items2,Company)

Attributes
POAttribute

Returns the name of the specified purchasing attribute.

POAttribute(AttributeName,Company)

POAttributeDescription

Returns the description of the specified purchasing attribute.

POAttributeDescription(AttributeName,Company)

POAttributeMemo

Returns the memo on the specified purchasing attribute.

POAttributeMemo(AttributeName,Company)

POAttributeCustomField

Returns the value of a custom field on the specified purchasing attribute.

POAttributeCustomField(CustomField,AttributeName,Company)

POAttributeItem

Returns the name of the specified purchasing attribute item.

POAttributeItem(AttributeName,Item,Company)

POAttributeItemDescription

Returns the description of the specified purchasing attribute item.

POAttributeItemDescription(AttributeName,Item,Company)

POAttributeItemMemo

Returns the memo on the specified purchasing attribute item.

POAttributeItemMemo(AttributeName,Item,Company)

POAttributeItemCustomField

Returns the value of a custom field on the specified purchasing attribute item.

POAttributeItemCustomField(CustomField,AttributeName,Item,Company)

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 the memo on the specified purchasing department.

PODepartmentMemo(Department,Company)

PODepartmentCustomField

Returns the value of a custom field on the specified purchasing department.

PODepartmentCustomField(CustomField,Department,Company)

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 the memo on the specified product.

POProductMemo(Product,Company)

POProductCustomField

Returns the value of a custom field on the specified product.

POProductCustomField(CustomField,Product,Company)

Units of Measure
POUnitOfMeasure

Returns the name of the unit of measure.

POUnitOfMeasure(Unit,Company)

POUnitOfMeasureDescription

Returns the description of the unit of measure.

POUnitOfMeasureDescription(Unit,Company)

POUnitOfMeasureMemo

Returns the memo on the unit of measure.

POUnitOfMeasureMemo(Unit,Company)

POUnitOfMeasureCustomField

Returns the value of a custom field on the unit of measure.

POUnitOfMeasureCustomField(CustomField,Unit,Company)

Vendors
Vendor

Returns the name of the specified vendor.

Vendor(VendorCode,Company)

VendorDescription

Returns the description of the specified vendor.

VendorDescription(Vendor,Company)

VendorMemo

Returns the memo on the specified vendor.

VendorMemo(Vendor,Company)

VendorCustomField

Returns the value of a custom field on the specified vendor.

VendorCustomField(CustomField,Vendor,Company)

x

Financial Designs Listing

Purpose

The Financial Designs Listing provides a list of financial designs defined in your system.

Content

The basic report is simply a listing of the financial design names.

In addition, you can include one or more of the following:

  • timestamps
  • memos
  • custom fields.

The following total appears on the report:

  • record count.

Print the report

  1. In the Navigation pane, highlight the ActivReporter > Financial Designs folder.
  2. Start the report set-up wizard.
    • To report on all or a filtered subset of financial designs:
      1. Right-click the Financial Designs folder and select Select and Report > Financial Designs Listing from the shortcut menu.
      2. On the Selection tab, define any filters you want to apply to the data.

    • To report on specifically selected financial designs:
      1. In the HD view, select the financial designs to include on the report. You can use Ctrl and/or Shift selection to select multiple records.
      2. Click .
    • To report on a particular financial design from the Financial Design window:
      1. In the HD view, locate and double-click the financial design you want to report on. The Financial Design window opens with the financial design loaded.
      2. Click .
  3. Select the Options tab.

  4. Mark the checkbox(es) for the additional information to include:
    • Timestamps
    • Memos
    • Custom Fields (only visible if custom fields are set up)
  5. Select the Output tab.
  6. In the Design field, look up and select the report design to use.
  7. In the toolbar, click the icon for the type of output you want:
    • - Provides access to two preview options.
      • Preview - Click the icon or click the drop-down arrow and select Preview from the drop-down menu to view the report in the Crystal Reports viewer.
      • Preview to PDF - Click the drop-down arrow next to the icon and select Preview to PDF to view the report in the PDF reader.
    • - Opens the Print dialog so that you can select and configure a printer and then print a paper copy of the report.
    • - Opens the dialog so that you can address and compose an e-mail that the report will be attached to.
    • - Opens the Export Report dialog so that you can save the report to a file. File types include Crystal Reports (.rpt), PDF (.pdf), Microsoft Excel (.xls), Microsoft Word (.doc), rich text (.rtf), and XML (.xml).
  8. x

    Report Email dialog

    From
    The email account from which to send the email. Valid options are:
    • Windows user default account. Sends email using the user's Windows default email account. For most users, this is the account configured in Outlook or another email client application.
    • Server personal. Sends email using the email configuration for the system or company server and the email address on the current user's authorized user record. The authorized user record must have a confirmed email address.
    • Server generic. Sends email using the email configuration and "from" address for the system or company server. This option requires "Send generic" access to the Server Email resource.
    To
    The email address(es) to which to send the email. Separate email addresses with semi-colons.
    CC
    The email addresses to copy on the email. Separate email addresses with semi-colons.
    BCC
    The email addresses to blind copy on the email. Separate email addresses with semi-colons.
    Subject
    The subject line.
    Text box
    The body of the email.

     

Data extensions

The following data extension is available for the report:

  • Financial designs

 

x

Publish a financial statement

The Publish Financial Statements wizard lets you limit a financial statement to a particular account or set of accounts or to produce iterative financials on a financial design template. After you design a template, you can publish multiple financial statements from the design without having to alter the design.

  1. In the Navigation pane, highlight the ActivReporter > Financial Designs folder.
  2. Start the Publish Financial Statements process:
    • To publish financial statements for all or a filtered subset of financial designs:
      1. Right-click and select Select and Publish Financials from the shortcut menu.

      2. On the Selection tab, define any filters to apply to the data.

        Tip

        If you frequently run a process with the same settings, or infrequently run the process but want to easily recall the settings you used the last time you ran it, create an action profile. Action profiles "remember" settings you've used to run a process in the past.

    • To publish financial statements for specifically selected financial designs:
      1. In the HD view, select the financial designs to publish financial statements for. You can use Ctrl and/or Shift selection to select multiple designs.
      2. Click . The Publish Financial Statements wizard starts.

  3. On the Options tab from the Calendar drop-down list, select the calendar to publish financial statements for.
  4. To define the reporting period using a relative expression, select or enter the period expression in the Expression field. If you prefer to explicitly enter the year and period, skip to step 5.

    You can select from the following common period expressions:

    • Current
    • Current - 1 period
    • Current - 1 year

    Alternatively, you can type a valid period expression in the field. For help on period expression syntax, click the help button to the right of the field.

    If you select or enter a period expression, ActivReporter adjusts the values in the Year and Period fields accordingly.

    If you enter a period expression, skip to step 7.

  5. From the Year drop-down list, select the year of the period to publish financial statements for.
  6. From the Period drop-down list, select the period to publish financial statements for.
  7. If you only want the financial statement to reflect postings through a particular date, enter the date in the As Of field. All postings after the as of date are ignored. The as of date must fall between the current period begin and end dates.
  8. To iterate over segment(s), rollup(s), and/or attribute(s), click the right arrow on the Iteration field to open the Select Iterations popup.

    If you do not need to perform iteration, skip to step 13.

  9. In the Select Iterations popup, highlight the segment(s), rollup(s), and/or attribute(s) to iterate over in the Available Segments list box, then click to move your selection(s) to the Assigned Segments list box.
  10. If you selected multiple items to iterate over, use and to arrange the items in the order you want them applied.
  11. Examples

    Example 1. Suppose your company has five departments and you need to produce an income statement for each department. If you select the segment that represents your departments to iterate over, ActivReporter will print out an income statement for each department.

    Example 2. Suppose you have a GL attribute called "Project" and a financial design that uses financial functions such as "Balance" and "Activity" which limits by other segments, rollups, and attributes. When the financial design is calculated and printed, amounts for ALL projects are summarized in the calculated amount. If instead you iterate over the "Project" attribute, a separate financial is generated for each project and each financial shows only amounts for its project.

  12. When you finish, click OK.

    Note

    If you specified segments or rollups in the Iteration field, be sure to use the Accounts tab to specify which accounts, segments, and/or rollups to iterate over. If you specified attributes in the Iteration field, be sure to use the Attributes tab to specify which attributes to iterate over.

  13. If you selected one or more items to iterate over and select "File" in the Output To field, the Combine iterations into multisheet workbook checkbox is enabled. Mark the checkbox if you want the separate iteration worksheets to be combined in a single workbook.
  14. Note

    Certain file types are not valid for combining iterations into a multisheet workbook: .txt, .csv, .prn, .dif, .slk, .xlam, .xla.

  15. If you selected one or more items to iterate over, the Include consolidated statement checkbox is enabled and marked. If you do not want your output to include a consolidated financial statement, clear the checkbox.

    To learn more about consolidated statements, expand this link.

    Example

    Suppose you have a "Store" account segment with eight items (stores "00" through "07"). You want to generate financial statements for stores 00, 01, 02, and 03 and also generate a consolidated financial statement for those four stores.

    1. Open the financial design you want to generate and run the Publish Financial Statements process. We'll use "Income Stmt" for this example.
    2. On the Options tab, respond as indicated to the highlighted selections.

    3. On the Accounts tab, limit the selection to stores 00, 01, 02, and 03.

    4. Complete the process.

    The process generates a single Excel workbook (Income Stmt.xls) in the file location you designated. The workbook contains five worksheets: one for the consolidated statement and a statement for each of the four stores.

    You can use the @IterationDescription() function in your financial design to produce the worksheet title. When you iterate, the function returns "Consolidated" for the consolidated worksheet title and returns the iteration item description for the titles of the individual worksheets. For example:

    • Income Stmt Consolidated - "Consolidated"
    • Income Stmt 00 - "Corporate Office"
    • Income Stmt 01 - "Lubbock"
    • Income Stmt 02 - "Dallas"
    • Income Stmt 03 - "Houston"

    Note

    • When you export a consolidated iteration, "Consolidated" is appended to the output filename: <Financial design name> Consolidated.xlsx.

    Special note about consolidated statements when iterating over multiple iteration items

    To include a consolidated statement when iterating over multiple iteration items (segments, rollups, and/or attributes), DO NOT exclude combinations of those items when creating selection conditions on the Accounts and Attributes tabs of the wizard. The list of iterations to be performed is not simply based on the iteration items matching the selection conditions but the selection conditions are also applied to determine the combinations of iteration items from the specified iteration items. The consolidated statement limits its financial functions to ALL combinations of iteration items from those specified.

    Example

    Suppose you have two segments, "Store" and "Department", and a rollup, "Performance". Performance is a rollup of Store + Department.

    Store Department Performance
    01 Apparel High
    01 Furniture Low
    01 Garden Low
    01 Jewelry Medium
    02 Apparel Medium
    02 Furniture Medium
    02 Garden High
    02 Jewelry Low
    03 Apparel High
    03 Furniture Low
    03 Garden Medium
    03 Jewelry High

    To generate a financial statement with consolidated financial statement for each of your low-performing departments:

    • On the Performance rollup, specify this condition: Performance = "Low"
    • Select the following segments for iteration: Store, Department

    The result will be a financial statement for the following combination of stores and departments since their Performance rollup value is "Low":

    Store Department
    01 Furniture
    01 Garden
    02 Jewelry
    03 Furniture

    However, the consolidated statement will be limited to all combinations of the selected items from Store and Department: Store IN (01,02,03) AND Department IN (Furniture, Garden, Jewelry).

    Store Department
    01 Furniture
    01 Garden
    01 Jewelry
    02 Furniture
    02 Garden
    02 Jewelry
    03 Furniture
    03 Garden
    03 Jewelry
  16. In the Currency Unit field, select the unit in which to report amounts. The unit selected must be flagged as a currency unit.
  17. If you want the statements to reflect unmerged entries that are not on hold, mark the Include Unmerged Entries Not on Hold checkbox.
  18. If you need to adjust the print area of the financial statements to start somewhere other than the upper left cell of the worksheet, select the row and column to start at in the Row Level and Column Level fields.

    Note

    This option is useful if you "outline" your financial statement in the leftmost columns and uppermost rows and want to hide those notations in your final output.

  19. In the Output To field, select the destination for the financial statements. Your options are:
    • Printer
    • File. If you select this option, the File Format and Directory fields are enabled.
      1. From the File Format drop-down list, select the type of file to save the financial statement as.

      2. In the Directory field, enter the file path or click and browse to the location where you want to save the file.
  20. If you specified segments and/or rollups in the Iteration field, select the Accounts tab. Otherwise, skip to step 20.

  21. To restrict the financial statements to particular accounts, define any filters to apply to the data.
  22. If you specified attributes in the Iteration field, select the Attributes tab. Otherwise, skip to step 22.

  23. To restrict the financial statements to information for particular attributes, define any filters to apply to the data.

    Example

    Suppose a rollup attribute called "Project Type" rolls up the "Project" attribute. You can iterate on "Project" and specify a condition on the Attributes tab for Project Type = "Stage". The result would be a separate financial for each project in the "Stage" project type rollup.

  24. Click Next >>. What happens next depends on the type of output you selected.
    • If you selected "Printer", the Print dialog opens.

      Set the print options as desired, then click OK. The financial statement(s) are sent to the selected printer.
    • If you selected "File", ActivReporter may prompt you to confirm that you want to generate the financials. If it does, click Yes. Otherwise, generation of financial statements begins immediately.
  25. After you produce the financial statement(s), click Finish.

 

x

Use the Financial Tree Reporting bot

The Financial Tree Reporting bot helps you automatically publish financial designs, iterate over the branches of your financial tree, and transmit the published financials to the recipients who need them. For example, you can use the bot to publish an income statement financial design, iterate over each department, and email the published financials to your department heads.

The Financial Tree Reporting bot is flexible enough to publish one or several financial designs and to iterate over departments, regions, and locations in one session.

Prerequisites

Before you run the Financial Tree Reporting bot, a few items must be present:

  • ActivReporter version 8.5-2 or newer.
  • Permissions to the Financial Tree Reporting bot.
  • The financial design you want to publish.
  • A saved configuration file in Excel.
  • To email reports from a generic company email address rather than a personal one, the company email configuration must be set up.
  • To report on a financial tree, appropriate segments and rollups are necessary.

Publish financials using the Financial Tree Reporting bot

  1. In the Navigation pane, right-click the [Company name] > Financial Designs folder and select Bots > Financial Tree Reporting from the shortcut menu. The Financial Tree Reporting bot starts.

  2. In the Design field, select the financial design to publish. If the configuration file you select specifies a different design on its "Reports" tab, the design in the configuration file replaces any design specified here.
  3. From the Calendar dropdown, select the calendar to base the financials on.
  4. To define the reporting period using a relative expression, select or enter the period expression in the Expression field. If you prefer to explicitly enter the year and period, skip to step 5.

    You can select from the following common period expressions:

    • Current
    • Current - 1 period
    • Current - 1 year

    Alternatively, you can type a period expression in the field. For help on period expression syntax, click the help button to the right of the field.

    If you select or enter a period expression, ActivReporter adjusts the values in the Year and Period fields accordingly.

    If you enter a period expression, skip to step 7.

  5. From the Year drop-down list, select the year of the period to report on.
  6. From the Period drop-down list, select the period to report on.
  7. If you only want the financials to reflect postings through a particular date, enter the date in the As of field. All postings after the as of date are ignored. The as of date must fall between the current period begin and end date.
  8. To include detail from unmerged entries that are not on hold, mark the Include Unmerged checkbox.
  9. In the Currency field, select the unit in which to report amounts. The unit selected must be flagged as a currency unit. If the configuration file you select specifies a different currency unit on its "Reports" tab, the currency unit in the configuration file replaces any currency unit specified here.
  10. In the Config File field, enter the path and file name or click and browse to the location of the Excel configuration file for the financial design. If you do not specify a configuration file, ActivReporter looks for a configuration file attached to the bot record and uses it.
  11. The default email subject when you publish financials using the bot is "Financial Report". To customize the subject line, enter the subject line in the Email Subject field. Subject lines which are longer than your email application allows are truncated. The maximum length of a subject line in Outlook is 255 characters.
  12. In the Email Body text box, enter the content of the body of the email to send with the published financials. If you enter nothing here and the configuration file does not specify body content, the email message will be blank. If the configuration file you select specifies email body content on its "Recipients" tab, the email body in the configuration file replaces any email body content specified here.
  13. From the Email Type drop-down list, select the type of email account from which to send the email. Your options are:
    • generic. Sends the email from the account configured on the Email tab of the company record.
    • personal. Sends the email from your personal email account.
  14. Tip

    Now is a good time to save your answers to this dialog so that some or all of steps 2-13 do not have to be repeated the next time you publish this financial tree.
  15. Click Next >>. You are prompted to confirm that you want to publish the financials.

    INSERT IMAGE HERE

  16. Click OK. The Financial Tree Reporting bot reports its results.

    INSERT IMAGE HERE

  17. Click Finish.

 

x

Create a configuration workbook for the Financial Tree Reporting bot

The Financial Tree Reporting bot reads a configuration file to gather the information it needs to produce and distribute financials. The configuration file is an Excel workbook which defines the financial tree, names the reports to be created, and identifies the recipients of the published reports and/or the locations where the reports are to be saved.

A blank configuration workbook named "Financial Tree Reporting Configuration.xlsx" is provided in the ".../Extras/ActivReporter" folder of the distribution. The workbook has the column and tab names preassigned.

To populate the configuration workbook:

  1. Open the configuration workbook in Excel and save it with a new name in the desired file location. The name you assign to the workbook is up to you but AccountingWare suggests that it be meaningful so that it is easy to identify in the future.

    Examples

    As a best practice, assign a name that indicates the purpose of the workbook. For example:

    • Comprehensive financials distributed to division managers.xslx
    • Period financials for managers.xslx
    • Financial Tree Reporting configuration.xslx
  2. Select the "Reports" worksheet.

    Note

    A red triangle in the upper-right corner of a column heading indicates a note has been added to provide additional information about the column. Hover your mouse cursor over the red triangle to view the note.

  3. Starting in the second row of the worksheet, enter the data that defines the information the published financials should contain. For each row of data, provide the following information:
    1. In the Report Name column, enter a label to identify the name of the file created for a report. Report names can contain spaces but must not exceed 200 characters. As a best practice, make the name meaningful but concise. These names are used in the corresponding "Reports" column of the "Recipients" worksheet.

      Each row in the worksheet with a unique report name generates a unique report. If the report name in the next row of the worksheet is blank or the same as the report name on the previous row, those rows are combined into a single Excel workbook where the individual rows represent a worksheet in the workbook.

      Note

      Do NOT allow entirely blank rows within the definition of the workbook structure.

      Expand this link to see a visual depiction of the workbook structure.

    2. In the Iteration column, enter the expression(s) which define the iteration(s) to perform. If you include multiple iteration expressions, separate them with commas. This entry is optional.

      Iteration expressions can take one of four forms:

      • <blank>. No entry in the column indicates that no iteration is to be performed. The result would be a single workbook not limited by segment, rollup, or attribute. For instance, an income statement financial design published with no iteration would result in an income statement for the entire organization rather than for a single department, division, etc.
      • Segments=<segment value>. Specify the segment to iterate over; e.g., Segments=Division.
      • Rollups=<rollup value>. Specify the rollup to iterate over; e.g, Rollups=GP Categories.
      • Attributes=Budget. "Budget" is the only available attribute.

      Examples

      • Segment=Division
      • Segment=Division,Segment=Department
      • Segment=Division,Rollup=Region,Attribute=Budget
    3. In the Conditions column, enter condition expressions to limit the scope of the iteration, if needed. If the value in the Iteration column is blank, this column must remain blank. Conditions are relative to the expression(s) entered in the Iteration column. For example, if the iteration includes "Segment=Division", you could limit by any of the segment items of the "Division" segment.

      You can use logical operators such as "AND", "OR", and "LIKE" between condition expressions to more precisely limit the scope of the iteration. Be careful to check the logic of these more complex conditions to ensure they will return the results you expect.

    4. Examples

      IterationConditionsResults
      Segments=DivisionDivision=100 AND Division=200Empty financial since an account cannot be associated with multiple segment items.
      Segments=DivisionDivision=100 OR Division=200Financial contains data for both divisions 100 and 200.
      Segments=DivisionDivision LIKE 2* AND Division <> 210Financial contains data for all divisions with a segment item that starts with "2" except for division 210.
    5. The Design column is optional. To use the financial design you entered in the Financial Tree Reporting dialog, leave this column blank. To override the design specified in the dialog, enter the name of the financial design to use to produce financials. Only one design can be entered in a cell and the design name must represent a valid design in the Financial Designs folder.
    6. The Tab (Label and Color) column is optional.

      To designate the name of the worksheet tab generated by the iteration, enter the name in the column. The maximum length of the tab name is 27 characters. If you enter a name longer than 27 characters, ActivReporter will truncate the name to 27 characters. Worksheet names must be unique in Excel; therefore, if you enter the same name for multiple tabs in a workbook, a number is appended to the worksheet name to make it unique. If you do not designate a tab name, the traditional tab names (Sheet1, Sheet2, etc.) are used.

      To designate a color for the worksheet tab, select the cell in this column and apply a fill color.

    7. The Currency column is optional. To use the currency unit you entered in the Financial Tree Reporting dialog, leave this column blank. To override the specified currency, enter the currency unit to use to produce the financials. Only one unit can be entered in a cell and the unit must represent a valid currency unit in the Units folder.
    8. The Format column is optional. Specify the report format. The format can be one of the following:
      • <blank>. If left blank, the report format defaults to Excel workbook (.xlsx) and only the Excel workbook is attached to the report email.
      • pdf. If you specify this option, a pdf version of the report is generated and attached with the Excel workbook to the report email.
      • pdf only. If you specify this option, only a pdf version of the report is attached to the report email.
    9. The Consolidated column is optional. If you leave the cell in this column blank, a consolidated worksheet is not generated. If you enter "yes" in this column and an iteration is specified, a consolidated version of the report is included in the workbook. This column is optional.
    10. Expand this link to see an example of a completed "Reports" tab.

  4. Select the "Recipients" tab.

    Note

    A red triangle in the upper-right corner of a column heading indicates a note has been added to provide additional information about the column. Hover your mouse cursor over the red triangle to view the note.

  5. Starting in the second row of the worksheet, enter the following data for each recipient of the financials.
    1. In the Name column, enter the recipient's name. The name shows only in the last step of the Financial Tree Reporting wizard where the reports that were sent (or failed to send) are listed.
    2. In the Email column, enter the email address of the recipient. This column is optional; however, if neither an email address, a file location, or a SharePoint location is specified for a recipient, no reports are produced for the recipient.
    3. In the File Location column, enter the file path where the reports are to be saved. This column is optional; however, if neither an email address, a file location, or a SharePoint location is specified for the recipient, no reports are produced for the recipient.
    4. In the SharePoint Location column, enter the SharePoint folder(s) to which to copy the corresponding report(s). If you enter multiple SharePoint folders, separate them with commas. This column is optional; however, if neither an email address, a file location, or a SharePoint location is specified for the recipient, no reports are produced for the recipient.
    5. In the Reports column, enter the report(s) to produce for the recipient. If you enter multiple report names, separate them with commas. The names in this column must exactly match the report names assigned on the "Reports" tab.
    6. If you want to override the content of the email message that you entered in the Financial Tree Reporting dialog, enter the recipient-specific content in the Email Body column. If you leave the entry in this column blank, the message you entered in the dialog is used.
    7. If you want to override the email subject line that you entered in the Financial Tree Reporting dialog, enter the recipient-specific subject line in the Email Subject column. If you leave the entry in this column blank, the subject line you entered in the dialog is used.
    8. In the Attachments column, enter the name(s) of the file(s) to attach to the report email for this recipient. If you enter multiple attachment files, separate them with commas.
    9. Expand this link to see an example of a completed "Recipients" tab.

  6. Save the workbook.