Financial designs

Security

Essentially, a financial design is an Excel worksheet that incorporates labels, ActivityHD 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.

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 ActivityHD 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.

Click here to view a table comparing financial designs and financial views.

Create a financial design record

The procedure which follows describes how to create a design record. Concealed in step 15 of this procedure is the heart of the effort, setting up the design in Excel. This aspect of financial design creation is discussed in the "Create a financial design from a blank Excel worksheet" topic found under the "Design creation" tab on this page.

In the Navigation pane, highlight the General Ledger > Financial Designs folder.
Click . The New Financial Design window opens.
Enter a unique Name for the financial design. You can use up to 40 characters.
Enter a description of the financial design. The default length is 90 characters, but you can use General Ledger package options to set the length of the field from 1 to 255 characters.
Click Workbook. The Financial Options dialog box opens.

Note

If the Excel add-in is not registered, you will first be prompted to register it. Click Yes to register the add-in.
From the Calendar drop-down list, select the calendar to base the financial statement on. If the selected calendar contains today's date, the Year and Period fields default to the calendar period which contains today's date. Otherwise, if the calendar does not contain today's date, the fields default to the last valid period before the current date.
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 7.

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, ActivityHD adjusts the values in the Year and Period fields accordingly.

If you enter a period expression, skip to step 9.
From the Year drop-down list, select the year of the period to report on.
From the Period drop-down list, select the period to report on.
If you only want the financial report 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.
To report in a different unit than the unit(s) specified on the affected accounts, select the reporting unit in the Currency Unit field; otherwise, leave this field blank. This option is used to perform translation between units. For optimal performance, only use this option when translation is needed.
To display actual data in your financial design, mark the Use Live Data checkbox; otherwise, the design will show zeros for amounts.
If you mark the Use Live Data checkbox, the Include Unmerged Entries Not on Hold checkbox is enabled. Mark this checkbox if you want the live data to include unmerged entries which are not flagged as "on hold".
If needed, use the Iteration Limits table to restrict the data that is reported. To do so, for each limit you want to define:
1. In the Type column, select whether to limit by account number, a specific segment, a specific rollup, or a specific attribute.
2. In the Item(s) column, specify the value(s) for the account number, segment item, rollup item, or attribute item to limit by.
Note
The Find dialog on the Item(s) field supports multi-select. For example, if you choose "Department" in the Type field, you could use Ctrl and/or Shift selection in the Find dialog on the Item(s) field to select multiple departments for iteration. This feature allows you to create a consolidated financial for a set of items.
Click OK.
Set up the financial design in the open Excel worksheet.
When you finish, save the financial design worksheet in Excel.
Close Excel.
In the Financial Design window, save the financial design record.

Tip

After you design a financial statement, consider reusing it as the basis for future financial designs. To copy a design to a new file, use the New Copy feature.

Work with design versions

When you open a financial design for editing, you are opening the most recent version of the design (unless you intentionally selected an older version). In the past, Excel was embedded in ActivityHD and financial designs were saved as OLE objects in the company database. Starting in version 7.12, financial designs are extracted to a temporary file on your hard drive or network and Excel is launched as an external application in its own process for editing the file. When you save changes to a design, first in Excel and then in ActivityHD, a new version of the design is added to the ActivityHD database and past versions of the design are retained (unless you choose to delete them). This effectively ends past heartaches with crashing, corrupted worksheets, and failed design recovery attempts.

But what if I have perfectly usable financial designs that were developed before Excel's liberation? Expand this link

Open a historical version of a financial design

Restore a historical version of a financial design

Delete a historical version of a financial design

Load a design from an external location

Drill down on financial function detail

The Drilldown Explorer lets you execute a financial function and view the result as well as the detail behind the calculation.

Note

Drilldown is only available when using live data.

In the Navigation pane, highlight the General Ledger > Financial Designs folder.
Click or right-click the folder and select Drilldown Explorer from the shortcut menu.
From the Function drop-down list, select the financial function to view details for.
In the Calendar Period field, select the accounting period for which to return data.
In the As Of field, select the last date for which to view journal detail entries. The default is the last day of the period you selected. The as of date must fall on or before the end date of the specified period.
To include unmerged transactions in the results, mark the Include Unmerged checkbox.
To limit the detail based on an Items Expression, click to open the Account Conditions dialog box where you can build an expression by selecting items and specifying constraints.
Click Calculate. The journal detail records which match your criteria load in the view.

Delete a financial design record

Elements of a financial design

Although they can vary in their complexity, all financial designs have the following elements in common:

Report and column headings. You can enter literal text in any cell of an Excel worksheet to produce report and column headings. At times you may want to retrieve information from ActivityHD to use in headings; e.g., company name, company address, segment item description, period ending date. To retrieve data for headings, use text functions or date functions.
Literals. You can enter text in any cell of a worksheet. Do not use literal text, however, for row descriptions. Instead, use text functions to retrieve the descriptions of the rollup items for each row.
Formulas. Formulas retrieve numerical values or text. Formulas often use rules that affect each row or column. Insert rules in the margins of the design.
Totals. Use Excel formulas to calculate totals in your financial designs. Totals are almost always defined using Excel's SUM function.

ActivityHD's extended function library for Excel lets you incorporate information from General Ledger in your financial design. One advantage of financial designs is that you can reorganize your rollups and have the changes immediately reflected in all affected financial designs.

Before you create a financial design, the following preparation is needed:

Determine how to summarize information. Which accounts do you want to group together and report on a line? Do you have an existing rollup for these report groupings that you can use? If so, skip to step 5.
Create a rollup that represents the report groupings you need.
Add rollup items to the rollup that identify each line on the financial statement.
Activate the rollup.
Create a new financial design.

Tip

When possible, reuse rollups in multiple financial designs.

Best practices

Large workbooks and calculation mode

The default calculation mode for ActivityHD is "Automatic". However, when designing a large workbook, set the calculation mode to "Manual". Then use F9 to recalculate the entire workbook, or Shift+F9 to calculate the current worksheet only.

Workbook storage location

While workbooks can be stored in the Windows file system or internal to the ActivityHD application, AccountingWare recommends that you store workbooks in ActivityHD. The advantages of storing in ActivityHD include:

Versioning

Iteration

General Ledger > Financial Designs > [right-click] > Select and Print Financials

Memo

Add free text to describe the workbook design and its use. See who created the design, the last date/time the design was modified, and other information about the workbook.

Obsolete workbooks

You can flag a workbook as obsolete while still retaining it for future use. When flagged "Obsolete", workbooks are hidden in the HD view unless you mark the Include Obsolete checkbox.

Attachments

If FileStream is enabled on the ActivityHD database (see your database administrator to get it enabled), you can add attachments to your financial designs. That could mean storing an entire database of published financials based on a design. If you do use attachments to store your published financial library, ActivityHD security will secure access to your financial information.

Note 1: Saving a workbook stored in ActivityHD

When a workbook is stored in ActivityHD, you must save the workbook in two places:

In Excel. Close the workbook and save when prompted, select the File tab and then select Save, or click Save in the ActivityHD ribbon.
In ActivityHD. Save the financial design record.

Note 2: Multi-company financial statements

When you need to create financial statements which combine data from two or more separate companies (i.e., databases), you can choose which ActivityHD company "hosts" the financial design; it does not matter which company you choose. The internal security for each company dictates whether you can access the data stored in the various company databases. Of course, if companies are delineated by using a segment of the chart of accounts, the issue of where to store the financial design is avoided.

Compound formulas in an Excel cell

Compound formulas in Excel workbooks occur when there are two functions in the same cell combined by an addition (+) operator. For example:

=Balance("", "GL.Account=000-1100-00", , "Casablanca") + Balance("", "GL.Account=000-1100-00", , "Casablanca")

Avoid this construction because the drilldown feature can only drill down to the first function. To work around this limitation, place the two components in two distinct cells (columns). If you don't want to see these cells in the final output, you can hide them.

Use the ItemExpression function to simplify setting two strings equal

Parameters in a function often require you to set two strings equal to one another. For instance:

GL.Account = 000-1100-00-A

AA Balance Sheet = 032

The first example is not problematic. However, the left side of the second example is a string that includes spaces, so we must enclose the string in quotes so that the parameter parser can work properly; in other words:

"AA Balance Sheet" = 032

But this is still a problem because the parameters of a function are strings themselves, so they must likewise be enclosed in quotes. The natural solution seems like:

""AA Balance Sheet" = 032"

However, this still isn't a proper construction since the parser requires you to "escape" quotation marks. Therefore, the proper formulation for the parameter is:

"""AA Balance Sheet"" = 032"

The complexity increases when you use indirect references in cells such as:

AA Balance Sheet = $A7

To be parsed correctly, this must be written as """AA Balance Sheet"" = "&$A7

To avoid this confusion, there is a special ActivityHD function ItemExpression which simplifies equating two strings even if the strings contain spaces.

The function parameters in the examples above can be written as follows using the ItemExpression function:

ItemExpression("AA Balance Sheet", "032") [Note: "032" must be enclosed in quotes so that it is not interpreted as "32".]

ItemExpression("GL.Account", "000-1100-00-A")

ItemExpression("AA Balance Sheet", $A7)

Note

Always quote both string parameters (i.e., "032" and "000-1100-00-A") unless the parameter is a reference to a cell (e.g., $A7) that is formatted as a string in Excel.

Tips for designing HUGE workbooks

ActivityHD can switch between "live data" mode and "non-live data". Live data updates your workbook with the current contents of the database whenever you press F9 in the financial design. You can control which mode ActivityHD is using by marking or clearing the Use Live Data checkbox in the Financial Options dialog. You can access the dialog by clicking Options in the ActivityHD ribbon.

If you are designing a huge workbook of many pages, your user experience may be improved by working with live data turned off. Alternatively, you can use Shift+F9 to update only the current worksheet instead of updating the entire workbook.

Publishing financial designs (workbooks)

There are several ways to publish/print your financial designs including:

Excel's print function
Right-click option on ActivityHD's Financial Designs folder
Publish Financial Report augmented with bots

Printing in Excel

When a workbook is open in Excel, Excel's print and save features are available. These are the primary ways to "publish" during the design process. Once the design is finalized, however, it is preferable to use another method to publish it since any time you open a workbook you run the risk of making inadvertent changes to the design.

Print Financials option on the right-click menu of the Financial Designs folder

This method is AccountingWare's recommended method for publishing financial designs. The advantages include:

Availability of iteration
Ability to limit results by accounts, account segments, or rollups
Ability to switch budgets at runtime
Elimination of accidental changes to the finalized design

Iteration

Using iteration allows you to publish multiple financial statements with a single instantiation of a report. For instance, with one financial design you can use iteration to produce reports for each division/company/department/etc.

Limiting results by account(s), account segment(s), or rollup(s)

You can use the Print Financials report wizard to filter a financial statement to a specific set of accounts, account segments, or rollups.

Switching budgets at runtime

Many companies have multiple budgets active at any given time. If the financial statement is designed using the generic budget ledger (B), when it is published the user can select which budget to use in budget comparisons. You can do this by selecting the budget from the Attributes tab in the Publish Financial Statements process.

Publish Financial Report augmented with bots

The Publish Financial Report is a special report located at General Ledger > Reports > Publish Financial Report. This report is used primarily in conjunction with bots in order to automate the process of generating financial reports. Bot creation requires someone skilled in writing VB scripts.

Bots can provide the capability to:

Produce a set of financial reports
Email specific financial reports to specific people
Store specific financial reports in a specific location or library
Attach specific financial reports to specific financial designs using a naming convention

Bots are not a standard component of ActivityHD. Bots are programmed to suit each individual customer's specifications and can be a tremendous time saver when properly designed and deployed.

Financial design presentation

While there are several ways to present your financial designs, the two most common are:

Excel workbooks with numeric data only (no formulas)
PDFs

Excel workbooks with numeric data only

If your goal is for the financial statement consumer to investigate the numbers in a financial statement, then disseminating statements as Excel workbooks is a logical choice. Distributing statements in this way retains the drilldown to detail function on financial amounts.

PDFs

PDFs are static and suitable for printing or saving to a library in the Windows file system.

Iteration hint

If you produce 15 financial reports using iteration and find errors in one or two of them, what should you do? Browse to the folder that contains the financial statement output and delete the statement(s) with errors. When you run the iteration again, you can opt to skip the regeneration of any statements which already exist in the output folder.

Reserving the first three columns and rows of a worksheet for indirect references

ActivityHD does not require any special row or column reservations. Every Excel financial function can be self-contained and free of indirect references.

On the other hand, ActivityHD financial designs can often benefit from the use of indirect references. If you choose to do so, we recommend you use a few of the left-most columns and top-most rows for this purpose. This recommendation is not mandatory; in fact, you can use any rows and columns for indirect references and then hide them in your final presentations. You can even use other worksheets in your workbook to manage indirect references.

Indirect references can be used to refer to ledgers, periods, accounts, rollups, and other items which support the financial design process. Refer to the sample reports to observe how indirect references can be used in financial designs.

Create a financial design from a blank Excel worksheet

The discussion that follows assumes that you have an intermediate knowledge of Microsoft Excel, including Excel functions (like SUM, COUNT, DATE), relative cell referencing, and formatting principles.

In addition, you should have a general knowledge of ActivityHD's extended function library for Excel plus ActivityHD's expression syntax and how to apply it.

Tip

The usual Excel commands are enabled when you are working on financial designs. Notably, the Split and Freeze Panes commands are available on the View ribbon.

We'll illustrate the basic principles of financial design by creating a simple income statement.

Step 1: Design the financial statement on paper

Ask yourself a few questions:

What should the general layout look like?
What information do I want to present?
How should I organize that information on the statement?
How will I label the columns and rows on the statement?
Do I have the rollups I need to group the accounts for reporting?
Which ActivityHD formulas do I need to pull in the data I want?
Which Excel functions do I need to summarize the data?

After you've answered the questions and gathered the facts you need, sketch the statement out on paper as a guideline while you implement your design.

Step 2: Create new rollups and rollup items if needed

For an income statement, it would be good to have a rollup representing revenues with rollup items that represent the categories of revenue we want the statement to show. Likewise, we should include a rollup for expenses with rollup items that represent the categories of expenses we want to show.

Create rollups.
Create rollup items.

For our example, we are using a rollup called "REVS" for revenues. REVS has the following rollup items:

"EXP" is our rollup for expenses. It has the following rollup items:

Note

We could have created one rollup with both the revenue and expense items we need. Indeed, that would simplify our process a bit because it would allow us to make greater use of the fill feature and of copy and paste. However, for this illustration, we'll distinguish between the revenue rollup and the expense rollup.

Step 3: Add report headings and labels

Literal text can be entered in any cell of an Excel worksheet.

Enter the headings and labels you need. Some of these, like the report title and column headings may remain static; some we may automate later by replacing the labels with formulas. For now, we'll enter headings and labels to serve as placeholders. You can apply text formatting now or later.

Step 4: Create a "cheat column"

We're going to need to reference the rollup items which correspond to each of the rows in our income statement. In a column to the left of the rows where we need to bring in values from rollup items, type the rollup item codes that correspond to each row.

Step 5: Use ActivityHD functions to pull data into the report

We'll start in the "Current" column of the REVENUE section.

In the case of the our income statement, we want to see the current activity in each of our revenue categories. The function we need to do that is the Activity function (not to be confused with the ActivityHD software). The arguments of the ActivityHD function are Period, Ledger, Account(s), Company, and Unit.

Place your cursor in the cell where you want to insert a value.
From the ActivityHD ribbon in the Functions group, select Financial > Activity. The Function Arguments dialog box opens.
In the Period field, enter a period expression for the current period such as "Period 1 of 2010".
In the Items1 field, enter a segment expression for the ledger such as "Ledger=A".
In the Items2 field, enter a segment expression for the accounts. In this case, we'll use a rollup and designate the rollup item we need; for example, "REVS=SERVICEREV".

Click OK.
We need the negative of the calculated activity amount, so in the formula bar, type a minus sign after the equal sign and before the Activity function name.
Notice that the amount for the service revenue for the current period has been pulled into the design (provided you requested live data when you set up the design).
We want to generalize the report so that it will work for any period, ledger, or account(s). To do so, we'll use relative cell referencing.
- Instead of specifying a particular "current" period, we want the design to report on whichever "current" period the user specifies when the report is run. To do this, we'll replace the specific period expression with the period expression "Current".
  Since we'll be referring to "Current" for every revenue and expense in the "Current" column, let's go a step further and replace it with a relative cell reference. First, type "Current" in a cell somewhere in the rows above the title. Next, select the calculated cell again and go to the formula bar and replace "Current" with the relative cell reference.
  Note
  Notice that this cell reference uses a relative column and absolute row.
- We'll also be referring to Ledger A for every revenue and expense in the "Current" column, so let's use a relative cell reference for it as well. First, type "Ledger=A" in a cell somewhere above the title. Next, select the calculated cell, go to the formula bar, and replace "Ledger=A" with the relative cell reference.
- Finally, let's generalize the segment expression that refers to the rollup we are using to derive the accounts we need. This is where our "cheat column" comes in. Instead of referencing the service rollup item by name, we can refer to the cell where we stored its name. Select the calculated cell and delete the rollup item name only (leave the closing quotation mark). We're going to concatenate the cell reference to the literal part of the expression, so after the closing quotation mark, type an ampersand (&) followed by the cell reference.
  Note
  Notice that this cell reference uses an absolute column and relative row.
Next, we'll use the power of Excel to populate the rows for the remaining revenue categories. Select the cell we've already calculated, then click and drag its fill handle to fill in the values.
Let's use Excel's SUM function to get a total for the revenue items. Highlight the cell where the Total Revenue row and the Current column meet. On Excel's Home tab in the Editing group, click .
Press Enter to accept the highlighted range of cells and insert the sum.
Select the values in the Current Revenue section and format them as currency.
Let's make the label on the Current column dynamic. To do so, select the cell with the "Current" label, and from the ActivityHD ribbon in the Functions group, select Periods > PeriodDescription. The Function Arguments dialog box opens.
The only arguments of the PeriodDescription function are Period and Company.
In the Period field, reference the field where you stored the text "Current".
Click OK.
Next, let's make the category labels dynamic as well. Select the cell with the first category label, and from the ActivityHD ribbon in the Functions group, select Segments > SegmentItemDescription. The Function Arguments dialog box opens.
The SegmentItemDescription function has three arguments, Segment (or rollup), Segment Item (or rollup item), and Company.
In the Segment field, enter the name of the revenue rollup.
In the Item field, enter a relative cell reference to the cell that contains the first rollup item name.
Click OK.
Finally, drag the fill handle on the first revenue description down to the last revenue description.
Select the column formerly known as "Current", copy it, and paste the content into the YTD column.
Replace the label on the revenue year-to-date column with the literal text "YTD" or some variation thereof.
To show year-to-date amounts, we need a different function. Not suprisingly, that function is called YearToDate. Select the cell for the first category in the YTD column. In the formula bar, replace the function name "Activity" with "YearToDate".
Drag the fill handle on the cell that contains the amount for the first year-to-date category and drag down through the row for the last category. The formulas for these cells are updated to use the YearToDate function. (The values in our example for the current and year-to-date columns are equal because the current period we selected in the design set-up window is "1".)
To fill in the budget numbers for the REVENUE section, start by copying the column that contains the year-to-date information to the column reserved for budget information.
Replace the "YTD" label in the Budget column with "Budget".
To arrive at budget numbers, we need to show numbers from the budget ledger, "B". Since we used relative cell referencing for the ledger, we simply need to change the literal text we referenced for the ledger from "Ledger=A" to "Ledger=B".
Now let's turn our attention to the EXPENSES portion of the report. First, let's make the category names in the EXPENSES section dynamic. Copy the cell in the REVENUE section that contains the first category label and paste it in the cell in the EXPENSES section that contains the first category label.
In the formula bar, change the second parameter to refer to the name of the rollup that contains expense rollup items.
Drag the fill handle on the cell that contains the first category name in the EXPENSES section to the last category name in the EXPENSES section. The labels are updated accordingly.
Copy the cell that contains the current revenue for the first revenue category and paste it in the cell that contains the current expenses for the first expense category.
The difference in the values we needed for REVENUE and the values we need for EXPENSES lies in the rollup. In the formula bar, change to the rollup name that contains your expense rollup items and remove the negative sign in front of the function name.
Drag the fill handle on the cell that contains the current expense amount for the first expense category to the last expense category.
Use the SUM function to calculate total expenses for the current column.
In the REVENUE section, copy the year-to-date entry for the first revenue category and paste it in the cell that corresponds to the year-to-date entry for the first expense category.
In the formula bar, change the rollup name to the name of the rollup that contains the expense rollup items and remove the negative sign.
Drag the fill handle down to the last expense category in the year-to-date column.
Use the SUM function to calculate total expenses for the year-to-date column.
Copy the year-to-date values in the EXPENSE section (from the first category through the total expenses) and paste them in the corresponding cells of the Budget column in the EXPENSE section.
Apply any formatting you want to improve the presentation of the report.

Tip: Printing a Financial Design

To print the financial design as it is laid out in Excel:

With Excel open to the design worksheet you want to print, click in the upper leftmost cell you want to print and drag to the bottom rightmost cell you want to print.
From the ActivityHD ribbon in the Layout group, select Print Area > Set Print Area.
In the Output group, click Print. The Print dialog opens.
Select the print options you need, then click OK.

When you finish, save the Excel workbook and close it.
Save the financial design record.

Insert a function in a financial design

Suppress rows with zero

You can suppress rows which contain zeros from your financial statements. In the sample income statement below, notice that the values in rows 19-21, and 30-32 are zeros.

Note

The procedures below describe how to perform actions in Microsoft Excel for Microsoft 365. Consult Microsoft's online help to learn how to accomplish the same results in your version of Excel.

Hidden formula filter method (featuring the IsLiveData function)

This method suppresses rows with zero for printing, but lets those rows show in design mode.

Open the financial design worksheet, making sure you mark the Use Live Data checkbox in the Financial Options dialog box.
Create a hidden column in your financial design.
Determine which column to test for the presence of zeros. This will be the column which holds monetary values.

The formula

To test for zeros we will compose a formula which uses Excel's IF function (aka Boolean function) and ActivityHD's IsLiveData function. The IF function evaluates a condition and performs one action if the condition is true and another action if the condition is false. The IsLiveData function refers to the Use Live Data checkbox in the Financial Options dialog box. When you print a financial statement, live data is used; when the checkbox is marked, you use live data as well.

We want to hide rows with zero when we're using live data and show those rows when we're in design mode (i.e., not using live data). So, in literal terms, if data is live and the monetary cell(s) on the row are equal to zero, we want to return "Hide"; otherwise, we want to return "Show". Written as a formula, this is similar to:

IF(IsLiveData()*AND(F14=0),"Hide","Show")

IsLiveData() evaluates to 1 if TRUE; 0 if FALSE. Similarly, the AND() function evaluates to 1 if TRUE; 0 if FALSE. Therefore, the product of IsLiveData() and AND(F14=0) will be equal to 1 only if both are equal to 1.

Note: In actual practice, replace "F14" with the position of the cell you are testing for zero.

In your hidden column, in the row which corresponds to the first monetary value in the worksheet, enter the following formula, replacing the arbitrary cell position "F14" with the appropriate cell position in your worksheet:

=IF(IsLiveData()*AND(F14=0),"Hide","Show")
Continue by copying the formula to the cells in the hidden column which correspond to the rows with monetary amounts in the column you are testing, excluding rows with literal text or totals. If necessary, edit the cell references in the formulas to ensure the references in the hidden column and in the column with values correspond.

Next, we're going to apply a filter on our hidden column.

Starting with the first cell in the hidden column select the range of cells to filter, then on the ActivityHD ribbon in the Layout group, click Filter. A drop-down control is added to the first cell in the column.
Open the dropdown and select Text Filters > Does Not Equal from the shortcut menu. The Custom AutoFilter dialog box opens with "does not equal" selected in the first Show rows where field.
From the adjacent drop-down list, select "Hide".
Click OK. The rows that contain "Hide" in the hidden column are suppressed.
Save your changes in Excel.
Close the worksheet.
Save your changes in ActivityHD and close the financial design record.

Notice that when you reopen the financial design in design mode (i.e., with Use Live Data unmarked) and open the financial design worksheet, all rows in which you added the formula now indicate "Show". This is because Excel detects that live data is not in use. So using this technique, you can see all rows in design mode and only non-zero rows when you print the financial design.

Extending the hidden formula filter method to multiple columns

If you have multiple columns of data in your financial design, say amounts by month and year-to-date, you will probably only want to suppress rows if every monetary value in the row is zero. To do so, you simply extend the formula discussed in the previous procedure by appending AND expressions to the condition statement. Using AND ensures that all conditions must be true before we hide a row.

Open the financial design worksheet, making sure you mark the Use Live Data checkbox in the Financial Options dialog box.
Create a hidden column in your financial design.
Determine which columns to test for the presence of zeros.
In the hidden column, in the row which corresponds to the first monetary values in the worksheet, enter the following formula, replacing the arbitrary cell positions with the appropriate cell positions in your worksheet.

=IF(IsLiveData()*AND(F12=0)*AND(H12=0),"Hide","Show")
Copy the formula to the cells in the hidden column which correspond to the rows with monetary amounts in the columns you are testing, excluding rows with literal text only or totals. Edit the cell references in the formulas to ensure the references in the hidden column and in the columns with values correspond.
From this point, follow steps 6-12 of the previous procedure to apply a filter on the hidden column.

Functions

Heads up!

Most financial design functions now include an optional "Company" parameter. If you omit a value for the company, information for the company you are currently connected to loads in the financial design. If you provide a valid company as the parameter value, the data for that company is reported in the financial design. This means a single financial design can simultaneously report on multiple companies.

Example

The Activity function without a company parameter specified returns results from the company the financial design is connected to:

Activity("current","ISSum=REVENUES","ledger=a")

The Activity function with a value specified for the company returns results for CompanyB:

Activity("current","ISSum=REVENUES","ledger=a","CompanyB")

Click a category to learn about the functions in the category.

Alternate presentation of functions with examples

Drill down on functions in a financial design

Period expressions

A period expression describes an accounting period relative to the current period of a calendar.

The most common period expression is "CURRENT" or its equivalent of an empty string "".

Period expressions consist of keywords, numeric constants, string constants, and/or codes strung together using appropriate syntax.

Note

To help you interpret the syntax models that follow, keep these things in mind:

Brackets "[ ]" indicate that the item enclosed is optional.
A vertical pipe "|" indicates "or".
NUMBER represents a numerical constant; it is interpreted as an Excel date number.
STRING represents a string constant; it is interpreted as a date in SQL Server constant date format.

There are two basic types of period expressions:

Current. This type of period expression has the following general syntax:
[CURRENT] [PERIOD] [[+/-] NUMBER] [PERIOD | PERIODS | YEAR | YEARS]
Examples
- CURRENT
- CURRENT PERIOD
- CURRENT - 1
- PERIOD 1
- CURRENT - 1 YEAR
Literal. This type of period expression starts with the PERIOD keyword in conjunction with either the THROUGH keyword or the CONTAINING keyword and has the following general syntax:
- PERIOD THROUGH
- PERIOD CONTAINING
These can be condensed to the following general syntax statement:
[PERIOD THROUGH | CONTAINING] [[NUMBER] | [STRING] | [[YEAR | PERIOD] [BEGIN | END] [CURRENT] [PERIOD] [[+/-] NUMBER] [PERIOD | PERIODS | YEAR | YEARS]]] [OF CALENDAR]

Sub-expressions

Sub-expressions can be components of larger expressions. For period expressions, there are two sub-expressions we'll consider: PERIOD NUMBER and OF CALENDAR.

PERIOD NUMBER. A PERIOD NUMBER sub-expression has the following general syntax:

PERIOD NUMBER [OF] [STRING] [OF CALENDAR] [[+/-] NUMBER] [PERIOD | PERIODS | YEAR | YEARS]

Note

The STRING in this syntax statement is a string constant or code that is interpreted as a year code.

Examples

PERIOD 1 OF 2006
PERIOD 12 OF "2008"
PERIOD 4 OF 2008 OF Quarterly

OF CALENDAR. An OF CALENDAR sub-expression has the following general syntax:

OF STRING

Note

The STRING in this syntax statement is a string constant or code that is interpreted as a calendar name.

Examples

PERIOD THROUGH CURRENT OF "Quarterly"
PERIOD CONTAINING "2014-10-15" OF Daily

Period offsets

Period offsets are additions or subtractions from the base period (typically, from CURRENT). The portion of the syntax examples cited above in red text represent the period offset. Period offsets can be used more than once (see the third example below).

A period offset has the following general syntax:

[+/-] NUMBER [PERIOD | PERIODS | YEAR | YEARS]

Examples

CURRENT - 1
CURRENT - 1 YEAR
CURRENT - 1 PERIOD - 1 YEAR

Note

If no period or year is specified in a period offset statement, PERIOD or PERIODS is assumed.

Segment items expressions

Segment items expressions are sub-expressions which determine a set of accounts based on common segment items in their segment codes or rollups. The general form of a segment items expression is:

[SEGMENT/ROLLUP <operator> ITEM CODE/MASK]

The <operator> is one of the following: =, <>, <, >, <=, >=, LIKE, NOT LIKE.

Examples

Main=1000
Department=100
Ledger=B AND Store=Lubbock

Syntax rules

Segment items expressions are optional.
Segment items expressions can be combined with ANDs and ORs as in the third example above.
Segment items expressions can be grouped using parentheses.

Example

(MAIN=1000 AND DEPARTMENT=100) OR MAIN=1100

Segment items expressions can be negated.

Example

NOT Main=1000

More examples...explained

Example	Explanation
Main=8733	Includes all "Main" segments with a segment item equal to "8733".
Division<>Store32	Includes all "Division" segments that do not have a segment item of "Store32".
Division LIKE Store	Includes all "Division" segments that have a segment item of "Store".
NOT Main=8000	Includes all "Main" segments with any segment item except "8000".
Division=Store AND Main LIKE 8?33	Includes all "Division" segments with a segment item of "Store" and "Main" segments with a segment item that satisfies the wildcard mask "8?33" (i.e., segment items with "8" in the first position, "3" in both the third and fourth positions, and any character in the second position).
"Balance Sheet"=CAP	Includes the "Balance Sheet" rollup with a rollup item equal to "CAP". In the expression, "Balance Sheet" is enclosed in quotes because of the space embedded in the rollup name.

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.

Example

(Pay=REG OR Pay=SAL) AND Ded=401K

PRCode items expressions can be negated.

Example

NOT Ded=DENTAL

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

IN(Compensation) AND Pay=REG

"Compensation" is the name of a check line group.

Payroll attributes expressions

A payroll attributes expression limits payroll expenses or liabilities to employee-type attribute items that were assigned to employees as of the GL posting date. The syntax for payroll attributes expressions is similar to segment items expressions and PRCode items expressions.

The general form of a payroll attributes expression is:

[ATTRIBUTE <operator> ATTRIBUTEITEM]

The <operator> is one of the following: =, <>, <, >, <=, >=, LIKE, NOT LIKE.

Examples

Location=X
Location=X AND Department=2

Syntax rules

Payroll attributes expressions are optional.
Payroll attributes expressions can be combined with ANDs and ORs as in the second example above.
Payroll attributes expressions can be grouped using parentheses.

Example

(Location=X AND Department=2) OR (Location=Y and Department=3)

Payroll attributes expressions can be negated.

Example

NOT Location=X

Connect to data

When editing a financial design you can work offline, meaning you are not connected to a dataset, or online, meaning that you are. It's easy to tell your connection status. When you are offline, the connection button is labeled "Connect" and the logo is not embellished. When you are online, the button is labeled with the name of the company you're connected to and the logo is embellished with blue signal strength bands (non-functional).

Connect to a dataset

Disconnect from a dataset

Change the dataset you are connected to

Expand the print area

Hide non-printing areas

Show non-printing areas

Filter data

Turn on auto-filtering

Apply filtering

Turn off auto-filtering

Clear all filtering

Reapply filtering

Use a built-in layout

A good way to jumpstart a financial design is to start with a built-in layout. ActivityHD includes layouts for balance sheets, income statements, and a trial balance. Layout designs also include features such as the currency unit description (shows if specified in options) and embedded validation functions (ValidateAccounts, ValidateItems, ErrorsOrWarnings) for ensuring completeness and accuracy.

To load a built-in design:

Click .
From the drop-down menu, select the financial design layout to load.
You are prompted whether to overwrite the current worksheet, insert a new worksheet, or cancel out of the process.
Click Yes or No. You are prompted with the opportunity to filter the data for the report.
To apply selection criteria to the report data, select an account, segment item, rollup item, or attribute item from the Items dropdown, then click OK. The Selection dialog opens.

Try it with automation!

Selection limits can also be applied using the GLFinancialCalculator.ItemList automation method. Here's an example:

Dim lCalculator As GLFinancialCalculator

Set lCalculator = mCompany.GeneralLedger.FinancialCalculator

Dim lItems(0 To 1) As String

lItems(0) = "Ledger"

lItems(1) = "Department"

Worksheet.Cells(row, 1).CopyFromRecordset lCalculator.ItemsList(Items, _

"<p>" + _

"<Items>" + _

"<Selection>" + _

"<Item Table='Segment Item' Field='Balance Option' Version='2' Type='0'> + _

"<ListItem Value='N' Checked='True'/>" + _

"</Item>" + _

"</Selection>" + _

"</Items>" + _

"<Item>" + _

"<Selection>" + _

"<Item Table='Segment Item' Field='Description' Version='3' Operator='1' Value1='*a*'/>" + _

"</Selection>" + _

"</Item>" + _

"</p>")

Define selection criteria for the financial design as needed. When you finish, click OK to close the Selection dialog.
Click OK to close the Financial Items prompt. ActivityHD advises you how many rows will be created in the generated layout.
Click OK. The selected design loads according to your instructions.

Cycle through the carousel below to see what the built-in layouts look like.

Balance Sheet

Tweak the design as needed.
When you finish, save your changes.

Use a sample financial design

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

In the Navigation pane, highlight the General Ledger > Financial Designs folder.
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 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 to report on. The Financial Design window opens with the financial design loaded.
  2. Click .
Select the Options tab.
Mark the checkbox(es) for the additional information to include:
- Timestamps
- Memos
- Custom Fields (only visible if custom fields are set up)
Select the Output tab.
In the Design field, look up and select the report design to use.

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 Report Email dialog so that you can address and compose an email that the report will be attached to. For best results, ensure your email client is running before you attempt to send a report via email.
- 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).

Data extensions

The following data extension is available for the report:

Financial designs

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.

In the Navigation pane, highlight the General Ledger > Financial Designs folder.
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.
- 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.
On the Options tab from the Calendar drop-down list, select the calendar to publish financial statements for. If the selected calendar contains today's date, the Year and Period fields default to the calendar period which contains today's date. Otherwise, if the calendar does not contain today's date, the fields default to the last valid period before the current date.
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, ActivityHD adjusts the values in the Year and Period fields accordingly.

If you enter a period expression, skip to step 7.
From the Year drop-down list, select the year to publish financial statements for.
From the Period drop-down list, select the period to publish financial statements for.
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.
To iterate over segment(s), rollup(s), and/or attribute(s), click on the Iteration field to open the Select Iteration Types dialog.

If you do not need to perform iteration, skip to step 15.
In the Select Iteration Types dialog, highlight the segment(s), rollup(s), and/or attribute(s) to iterate over in the Available Types list box, then click to move your selection(s) to the Assigned Types list box.
If you selected multiple types to iterate over, use and to arrange the types in the order you want them applied.

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, ActivityHD will publish an income statement for each department.

Example2. 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 published, 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.

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.
If you specified one or more iteration types in the Iteration field, the Limits field is enabled. If needed, you can use the field to define account conditions for the iteration. To do so, click to open the Account Conditions dialog box where you can build an expression by selecting items and specifying constraints.
If you specified one or more iteration type in the Iteration field, the Options field is enabled. Click to open the Consolidation Options dialog.
1. In the Option field, select a consolidation option. Valid options are:
  - Include Consolidated
  - Exclude Consolidated
  - Consolidated Only
2. If you selected "Include Consolidated" or "Consolidated Only" in the Option field, the Code field is enabled. Enter the consolidation code to use in the file name and in the Excel worksheet name. The default value is "Consolidated".
3. If you selected "Include Consolidated" or "Consolidated Only" in the Option field, the Description field is enabled. Enter the iteration description to use in the report. The default value is "Consolidated".
4. Click OK to close the dialog box.

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.

Note

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

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.

Open the financial design you want to generate and run the Publish Financial Statements process. We'll use "Income Stmt" for this example.
On the Options tab, respond as indicated to the highlighted selections.
On the Accounts tab, limit the selection to stores 00, 01, 02, and 03.
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

In the Currency Unit field, select the unit in which to report amounts. The unit selected must be flagged as a currency unit.
If you want the statements to reflect unmerged entries that are not on hold, mark the Include Unmerged Entries Not on Hold checkbox.
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.
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.
If you specified segments and/or rollups in the Iteration field, select the Accounts tab. Otherwise, skip to step 21.
To restrict the financial statements to particular accounts, define any filters to apply to the data.
If you specified attributes in the Iteration field, select the Attributes tab. Otherwise, skip to step 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.
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", ActivityHD may prompt you to confirm that you want to publish the financials.
  If it does, click Yes. Otherwise, publishing of financial statements begins immediately.
After you produce the financial statement(s), click Finish.

Condition Expression

If the account mask you specified in the field that accompanies the Condition field is too broad or too narrow to capture the accounts you need, enter a SQL condition statement here to exclude or include accounts. Accounts are included which satisfy both the condition and the account mask.

If you entered a condition in the underlying field, it is carried over into this text box.

Alternatively, you can use the table below to describe the conditions you need and ActivityHD 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 accounts.

Item

From the drop-down list, select the account, segment, rollup, or attribute to define a range or mask on.

Mask

Enter a mask for the account number, segment item, rollup item, or attribute item when the items you need to select have common characters but are not in a particular order.

Begin

Enter the first account number, segment item, rollup item, or attribute item in the range of items you need to select. Use a range when the items are in a defined order.

End

Enter the last account number, segment item, rollup item, or attribute item in the range of items you need to select. Use a range when the items are in a defined order.

Check Conditions button

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

Publish Financial Report (formerly "Account Financial Report")

Purpose

The Publish Financial Report provides a means to select a financial design and to print a financial statement using that design. This report blends the functionality of Publish Financials, including iteration, with the emailing and scheduling capabilities of ActivityHD automation.

"Run Report" access to the Publish Financial Report resource is required to run this report.

Content

The report content and layout vary depending on the financial design you select.

Print the report

In the Navigation pane, highlight the General Ledger > Reports folder.
In the General Ledger Reports HD view, double-click Publish Financial Report to start the report set-up wizard.
On the Options tab in the Design Location section, select the location where the design for the report is stored. Your options are:
- Internal. The report uses a financial design from ActivityHD. If you select this option, the Design field is enabled. Select the financial design to use to print the report.
- External. The report uses a financial design from the file system. If you select this option, the Design File field is enabled. Browse to the location in your file system where the design is stored, select it, and click Open to load the file name in the field.
From the Calendar drop-down list, select the calendar to base the report on. If the selected calendar contains today's date, the Year and Period fields default to the calendar period which contains today's date. Otherwise, if the calendar does not contain today's date, the fields default to the last valid period before the current date.
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 6.

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, ActivityHD adjusts the values in the Year and Period fields accordingly.

If you enter a period expression, skip to step 8.
From the Year drop-down list, select the year of the period to report on.
From the Period drop-down list, select the period to report on.
If you only want the report 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.
To iterate over segment(s), rollup(s), and/or attribute(s), click on the Iteration field to open the Select Iteration Types dialog.

If you do not need to perform iteration, skip to step 16.
In the Select Iteration Types dialog, highlight the segment(s), rollup(s), and/or attribute(s) to iterate over in the Available Types list box, then click to move your selection(s) to the Assigned Types list box.
If you selected multiple items to iterate over, use and to arrange the items in the order you want them applied.
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.
If you specified one or more iteration types in the Iteration field, the Limits field is enabled. If needed, you can use the field to define account conditions for the iteration. To do so, click to open the Account Conditions dialog box where you can build an expression by selecting items and specifying constraints.
If you specified one or more iteration type in the Iteration field, the Options field is enabled. Click to open the Consolidation Options dialog.
1. In the Option field, select a consolidation option. Valid options are:
  - Include Consolidated
  - Exclude Consolidated
  - Consolidated Only
2. If you selected "Include Consolidated" or "Consolidated Only" in the Option field, the Code field is enabled. Enter the consolidation code to use in the file name and in the Excel worksheet name. The default value is "Consolidated".
3. If you selected "Include Consolidated" or "Consolidated Only" in the Option field, the Description field is enabled. Enter the iteration description to use in the report. The default value is "Consolidated".
4. Click OK to close the dialog box.
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.

Note

Certain file types are not valid for combining iterations into a multisheet workbook: .txt, .csv, .prn, .dif, .slk, .xlam, .xla.
In the Currency Unit field, select the unit in which to report amounts. The unit selected must be flagged as a currency unit.
To include detail from unmerged entries that are not on hold, mark the Include Unmerged Entries Not on Hold checkbox.
In the Row Level field, enter the highest row level to include on the report if the selected financial design includes an outline. The report will print the specified row level and all row levels below it. If the financial design does not include an outline, accept blank.
In the Column Level field, enter the highest column level to include on the report if the selected financial design includes an outline. The report will print the specified column level and all column levels below it. If the financial design does not include an outline, accept blank.
Select the Accounts tab.
Define any filters you want to apply on accounts.
Select the Attributes tab.
Define any filters you want to apply on attributes.

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 Report Email dialog so that you can address and compose an email that the report will be attached to. For best results, ensure your email client is running before you attempt to send a report via email.
- 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).

Condition Expression

If you entered a condition in the underlying field, it is carried over into this text box.

Alternatively, you can use the table below to describe the conditions you need and ActivityHD 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 accounts.

Item

From the drop-down list, select the account, segment, rollup, or attribute to define a range or mask on.

Mask

Enter a mask for the account number, segment item, rollup item, or attribute item when the items you need to select have common characters but are not in a particular order.

Begin

Enter the first account number, segment item, rollup item, or attribute item in the range of items you need to select. Use a range when the items are in a defined order.

End

Enter the last account number, segment item, rollup item, or attribute item in the range of items you need to select. Use a range when the items are in a defined order.

Check Conditions button

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

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:

ActivityHD version 8.25-0 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

In the Navigation pane, right-click the General Ledger > Financial Designs folder and select Bots > Financial Tree Reporting from the shortcut menu. The Financial Tree Reporting bot starts.
In the Design field, select the financial design to publish. If the configuration file you select in the Configuration Workbook field specifies a different design on its "Reports" tab, the design in the configuration file replaces any design specified here.
From the Calendar dropdown, select the calendar to base the financials on.
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, ActivityHD adjusts the values in the Year and Period fields accordingly.

If you enter a period expression, skip to step 7.
From the Year drop-down list, select the year of the period to report on.
From the Period drop-down list, select the period to report on.
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 dates.
In the Currency Unit 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.
To include detail from unmerged entries that are not on hold, mark the Include Unmerged Entries not on Hold checkbox.
In the Configuration Workbook 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, ActivityHD looks for a configuration file attached to the bot record and uses it.
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.
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.
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.

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.

Click Next >>. You are prompted to confirm that you want to publish the financials.
Click OK. The Financial Tree Reporting bot reports its results.
Click Finish.

Prepare 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/General Ledger" folder of the distribution. The workbook has the column and tab names preassigned. Furthermore, when you install the Financial Tree Reporting bot, ActivityHD tests to see if attachments are enabled. If attachments are enabled, the configuration workbook is attached to the bot record.

To populate the configuration workbook:

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
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.

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:

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.
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=<attribute value>. Specify the attribute to iterate over; e.g., Attributes=Budget..
Examples
- Segments=Division
- Segments=Division,Segments=Department
- Segments=Division,Rollups=Region,Attributes=Budget
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 "Segments=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.

Examples

Iteration	Conditions	Results
Segments=Division	Division=100 AND Division=200	Empty financial since an account cannot be associated with multiple segment items.
Segments=Division	Division=100 OR Division=200	Financial contains data for both divisions 100 and 200.
Segments=Division	Division LIKE 2* AND Division <> 210	Financial contains data for all divisions with a segment item that starts with "2" except for division 210.

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.
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, ActivityHD 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.
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.
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.
The Consolidated column is optional. Valid values are:
- <blank> or exclude. No consolidated report is generated. If you leave the field blank or enter "exclude", skip to step 4.
- yes. Generates a consolidated report and uses the values specified in the "Consolidated Code" and "Consolidated Description" columns.
- only. Generates only the consolidated report and uses the values specified in the "Consolidated Code" and "Consolidated Description" columns.
In the Consolidated Code column, enter the consolidation code to use in the file name and in the Excel worksheet name. The default code is "Consolidated".
In the Consolidated Description column, enter the iteration description to use for the consolidated report. The default description is "Consolidated".

Expand this link to see an example of a completed "Reports" tab.

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.
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.
Save the workbook.

Financial Design Record ID

Name

A unique, descriptive name for the financial design.

Description

A description of the financial design. The default length is 90 characters, but you can use General Ledger package options to set the length of the field from 1 to 255 characters.

Financial Design tab

Workbook

Click this button to open the Financial Options dialog box where you can configure options for the financial design and then access an Excel worksheet to create or maintain the financial design.

Versions tab

Versions table

This table lists the historical versions of the financial design in reverse chronological order. The top row in the table represents the active financial design.

Version

The auto-incremented version number.

Timestamp

The date and time the version of the workbook was saved.

User

The user who created the workbook version.

Size

The size of the workbook. This can be used to spot significant changes and possible file corruption.

Type

The file format of the workbook. ".xlsx" indicates a file in standard Excel format. ".xlsm" indicates a macro-enabled Excel file. If a value in this column is blank, the file is stored as an OLE object and has not yet been converted to a standalone Excel file.

Memo

A comment about the nature of the version change. Memos for standard actions are loaded by default. Add comments which will be helpful if a user decides later to restore a prior version of the design.

Custom tab

This tab is visible if custom fields exist for the entity. At a minimum, if there are custom fields, a Fields subtab will be present. One or more additional categories of subtabs may also be visible.

Fields subtab

This tab prompts for values for any custom fields set up for entity records of this entity type. Respond to the prompts as appropriate.

References subtab

This tab is visible if other records reference the current record.

Reference

From the drop-down list, select the custom field to view references for. Reference custom fields have the following format: PackageName.EntityName.CustomFieldName.

Example

Suppose a custom field exists on PRCodes that references an ARCode. On the ARCode record, on the Custom > References subtab, you can view all the PRCodes which reference that ARCode.

Exchange Folder subtab

This tab is visible only if you set up a custom field with a data type of "Exchange Folder". The label on this tab is the name assigned to the custom field.

This tab shows the contents of the specified Exchange folder.

File subtab

This tab is visible only if you set up a custom field with a data type of "File". The label on the tab is the name assigned to the custom field.

This tab renders the contents of the specified file according to its file type.

Internet Address subtab

This tab is visible only if you set up a custom field with a data type of "Internet Address". The label on this tab is the name assigned to the custom field.

This tab shows the contents of the specified web page.

Network Folder subtab

This tab is visible only if you set up a custom field with a data type of "Network Folder". The label on this tab is the name assigned to the custom field.

This tab shows the contents of the specified network folder.

Attachments tab

The Attachments tab is visible if any record for a given entity has an attachment. If the Attachments tab is not visible, this implies that no record of the entity type has an attachment on it; however, once an attachment is added to any record of the entity type, the Attachments tab will become available.

Attachments

The list box shows the files which are attached to the current record. Highlight a file name to preview the file in the adjacent pane. Double-click a file name to open the file in its native editor.

Preview pane

If a file name is highlighted in the Attachments list box, this pane shows a preview of the file contents. Some file types cannot be previewed but can be double-clicked to open in their native editor.

Other tab

Memo tab

To include additional information about this record not collected elsewhere, enter it in this text box. This area supports common keyboard shortcuts such as Ctrl+C to copy and Ctrl+V to paste.

Timestamps tab

This tab shows the user who created this record and the date and time of creation as well as the user, date, and time of the latest modification to the record.

Extras\General Ledger\Financial Tree Reporting Configuration.xlsx

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)

Periods Functions

As of Period End: A hybrid Excel-ActivityHD 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-ActivityHD function which produces a heading which includes 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-ActivityHD 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-ActivityHD 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-ActivityHD 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 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.
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 as of date, if specified; otherwise, returns 0.

AsOf()

Example

To use the function and deal with the possibility of the as of date not being specified, try this:

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

Miscellaneous Functions

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

AccountItem(Account,Ledger,Company)
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,Company)
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,...)
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 Financial 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 Financial 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 Financial 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 Financial Options dialog box.

Unit(Field,Unit,Company)
IsLiveData: Returns "true" if the data is live; otherwise, returns "false".

IsLiveData(Company)

Company Functions

Note

Company financial functions, like all functions with the optional "Company" argument, return information only from companies the user can connect to.

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 name of the optional company.

CompanyName(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)

Purchasing Functions

POAmount: Calculates the total purchase amount for the specified period.

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

Available Items

GL.Account | GL.Segment.<name> | GL.Rollup.<name> | PO.Department | PO.Product | PO.UnitOfMeasure | PO.Vendor | GL.Attribute.<name> | PO.Attribute.<name>

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)

Available Items

GL.Account | GL.Segment.<name> | GL.Rollup.<name> | PO.Department | PO.Product | PO.UnitOfMeasure | PO.Vendor | GL.Attribute.<name> | PO.Attribute.<name>

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)

Available Items

GL.Account | GL.Segment.<name> | GL.Rollup.<name> | PO.Department | PO.Product | PO.UnitOfMeasure | PO.Vendor | GL.Attribute.<name> | PO.Attribute.<name>

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)

Available Items

GL.Account | GL.Segment.<name> | GL.Rollup.<name> | PO.Department | PO.Product | PO.UnitOfMeasure | PO.Vendor | GL.Attribute.<name> | PO.Attribute.<name>
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)

Example

PODepartmentDescription("Accounting")

Retrieves the description of the accounting department.
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(Vendor,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)

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)

Common accesses available on financial designs

Access	A user with this access can...
Change	Use the mass change action on financial designs.
Custom Fields	Create and edit custom fields for financial designs.
Data	Have read-only access to financial designs from anywhere in the software (e.g., field validations, filters, date expressions).
Delete	Delete financial designs.
Edit	Edit financial design records.
Export	Export financial design records from ActivityHD.
Import	Import financial design records into ActivityHD.
New	Create new financial design records.
Read	Have read-only access to financial design records.
Report	Run reports with financial design information.
Report Designs	Create and edit report designs with financial design information. This access enables the Report Designs button on the Output tab of report dialogs.
Shared Answers	Create and edit action profiles and report profiles related to financial designs.
Shared Filters	Create and edit shared filters on financial designs.
Visible	View the Financial Designs folder in the Navigation pane.

Special accesses available on financial designs

Access	A user with this access can...
Publish	Publish financial statements.

	Financial Designs	Financial Views
Structure	Financial statement. Loads data into an Excel spreadsheet with cell locations the user predefines to hold totals such as assets, liability, equity, revenue, and expenses, as well as other values.	Financial report. Loads data into the HD view of a special ActivityHD folder. The columns in the HD view are based on financial functions; the rows correspond to the segments and rollups selected for reporting. Useful for interactive investigation. Provides data for dynamic reporting of sections of the chart of accounts. The data in this "view" can also be fashioned into a financial report.
Flexibility	Freeform	Tabular
Setup Tool	MS Excel	Financial View edit window
Viewing Tool	MS Excel	ActivityHD folder
Reporting Tool	MS Excel	Crystal Reports
Financial Functions	Similar (Balance, Activity, etc.)	Similar (Balance, Activity, etc.)
Period Expressions	Similar (Current - 1, etc.)	Similar (Current - 1, etc.)
Other Functions	Excel functions	Crystal Reports functions

Condition Expression

If you entered a condition in the underlying field, it is carried over into this text box.

Alternatively, you can use the table below to describe the conditions you need and ActivityHD 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 accounts.

Item

From the drop-down list, select the account, segment, rollup, or attribute to define a range or mask on.

Mask

Enter a mask for the account number, segment item, rollup item, or attribute item when the items you need to select have common characters but are not in a particular order.

Begin

Enter the first account number, segment item, rollup item, or attribute item in the range of items you need to select. Use a range when the items are in a defined order.

End

Enter the last account number, segment item, rollup item, or attribute item in the range of items you need to select. Use a range when the items are in a defined order.

Check Conditions button

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

Options tab

Current Period

The current period to use for calculations in the financial design.

Calendar

The GL calendar to use for the financial design. If the selected calendar contains today's date, the Year and Period fields default to the calendar period which contains today's date. Otherwise, if the calendar does not contain today's date, the fields default to the last valid period before the current date.

Expression

A period expression which defines a relative reporting period for the financial statement. You can select a period expression from the drop-down list or enter a period expression directly in the field. To explicitly specify a reporting period for the selected calendar, use the Year and Period fields instead.

The dropdown contains the following common period expressions:

Current
Current - 1 period
Current - 1 year

For help with proper period expression syntax, click the help button to the right of the field.

Year

The year from the GL calendar to use for the financial design. If a period expression is entered in the Expression field, ActivityHD automatically updates this field for you.

Period

The period from the selected year to use for the financial design. If a period expression is entered in the Expression field, ActivityHD automatically updates this field for you.

As of

The date through which to include postings on the financial report. All postings after the as of date are ignored. The as of date must fall between the current period begin and end dates. Leave the field blank to include all postings for the specified period.

Currency Unit

The currency unit to report amounts in if different than the unit(s) specified on the affected accounts. If blank, the units specified on affected accounts are used. This option is used to perform translation between units. For optimal performance, only use this option when translation is needed.

Note

If you are generating a report from a layout template and want the currency unit to display below the main heading, specify the reporting unit to use and ensure that the unit record includes a description.

Use Live Data

Mark this checkbox if you want to show actual data in your financial design as you work. Otherwise, the design will show zeros for amounts.

Include Unmerged Entries not on Hold

This checkbox is enabled if the Use Live Data checkbox is marked. Mark this checkbox to include unmerged entries which are not on hold in your live data.

Iteration Limits table

Use the table to restrict the data which is reported by the financial design.

Type

The account, segment, rollup, or attribute to limit by.

Item(s)

The mask or value(s) for the account number, segment item, rollup item, or attribute item to limit by.

Note

The Find dialog on the Item(s) field supports multi-select. For example, if you choose "Department" in the Type field, you could use Ctrl and/or Shift selection in the Find dialog on the Item(s) field to select multiple departments for iteration. This feature allows you to create a consolidated financial for a set of items.

AccountingWare, LLC

5225 S Loop 289, #207

Lubbock, TX 79424

806.687.8500 | 800.354.7152

Financial designs

Preparing to design a financial statement

Large workbooks and calculation mode

Workbook storage location

Versioning

Iteration

Memo

Obsolete workbooks

Attachments

Compound formulas in an Excel cell

Use the ItemExpression function to simplify setting two strings equal

Tips for designing HUGE workbooks

Publishing financial designs (workbooks)

Printing in Excel

Print Financials option on the right-click menu of the Financial Designs folder

Iteration

Limiting results by account(s), account segment(s), or rollup(s)

Switching budgets at runtime

Publish Financial Report augmented with bots

Financial design presentation

Excel workbooks with numeric data only

PDFs

Iteration hint

Reserving the first three columns and rows of a worksheet for indirect references

Step 1: Design the financial statement on paper

Step 2: Create new rollups and rollup items if needed

Step 3: Add report headings and labels

Step 4: Create a "cheat column"

Step 5: Use ActivityHD functions to pull data into the report

Hidden formula filter method (featuring the IsLiveData function)

The formula

Extending the hidden formula filter method to multiple columns

Sub-expressions

Period offsets

Syntax rules

More examples...explained

Syntax rules

Syntax rules

Try it with automation!

Purpose

Content

Print the report

Data extensions

Special note about consolidated statements when iterating over multiple iteration items

Purpose

Content

Print the report

Prerequisites

Publish financials using the Financial Tree Reporting bot

XML syntax samples

Drilldown

Financial Design Record ID

Financial Design tab

Versions tab

Custom tab

Fields subtab

References subtab

Exchange Folder subtab

File subtab

Internet Address subtab

Network Folder subtab

Attachments tab

Other tab

Common accesses available on financial designs

Special accesses available on financial designs

Options tab