Financial views

Financial views help you create dynamic financial statements that you can view in the HD view or generate in report form for deeper analysis of your financial data. Each financial view can be filtered by calendar, by year, and by period and you can include or exclude unmerged entries which are not on hold. Views which explicitly include account numbers can also be filtered by account number.

Planning is vital to a great financial view. AccountingWare suggests that you design the financial view on paper before undertaking the design in ActivReporter. You may be able to use existing rollups to group information for financial reports, or you may need to create new rollups.

The rows in a financial view can report over all accounts in the chart of accounts or over specified segments and/or rollups. The columns in a financial view are defined by choosing a column name, financial view function, and depending on the function selected, by specifying an items expression.

How the financial view is defined determines what information is available for financial reports based on the financial view.

Create a financial view

The process of developing a financial view involves determining the columns you want to show in the view and specifying the functions and arguments that will populate the columns with the data you want to see. It's a good idea to plan your financial views on paper before you start setting them up.

In the Navigation pane, highlight the ActivReporter > Financial Views folder.
Click . The New Financial View window opens.
Enter a unique Name for the financial view.
If you want to report totals only, skip to step 7. Otherwise, in the Segments field, click the right arrow to open the Financial View Segments popup.
Do one of the following:
- If you want to report on all accounts in the chart of accounts, select "Report on Accounts".
- If you want to iterate over specific segments/rollups:
  1. Select "Specify Segments".
  2. In the Available Segments list box, highlight the segment(s)/rollup(s) to include, then click to move your selection(s) to the Assigned Segments list box. You can use Ctrl and/or Shift selection to select multiple segments.
  3. If you selected multiple segments/rollups, use and to arrange them in the desired order.
Click OK.
In the Attributes field, select the attribute(s) to report on.

Note

If you report on a combination of segments/rollups and/or attributes, the combinations of segments/rollups and/or attributes found in the journal detail will define the rows in the financial view.

In the first row of the Columns table in the Name field, type a label for the first column of the financial view.
From the Function drop-down list, select the function that returns the type of data you want to retrieve for the column.
Click the right arrow on the Arguments column to open the Financial View Column Arguments popup. The information prompted for depends on your selection in the Function column.
Respond to the prompt(s) as appropriate. Prompts may include one or more of the following fields:
- Period Expression. Enter a period expression to describe the reporting period.
- Items Expression. Enter a conditional expression to describe the accounts to include. The conditions you can apply depend on the function you selected for the current row. In general terms, conditional expressions allow you to limit information by any available attribute that makes sense in the context of the selected function.
  When writing conditional expressions it is important to avoid ambiguity in identifying the item you want ActivReporter to evaluate. When ambiguity is possible, you should the item name.
  ×
  Fully qualified naming convention
  When specifying arguments in conditional expressions, it is important to eliminate any ambiguity so that ActivReporter can correctly evaluate the expressions. To do this, we refer to the element we are specifying a condition on by its fully qualified name. In general, the fully qualified name has the following form:
  <Two-character package ID>.<entity name>.<item name>
  Ambiguity can arise in a couple of ways:
  If you have a GL segment named "Account", it can conflict with the GL account entity. In order to eliminate the ambiguity, refer to the account entity as GL.Account and to the segment as GL.Segment.Account.
  If you are using financial functions which are not GL-specific, you may need to distinguish segment/rollup names from attribute names (or entity names) from other packages. If a segment/rollup name is the same as the attribute or entity name, an ambiguity can occur.
  Fully-qualified names are not necessary for GL-specific functions such as Activity, Balance, YearToDate, etc.; however, there's no harm in using them.
  Example
  To distinguish the GL account entity from a GL segment named "Account", use "GL.Account" for the entity and "GL.Segment.Account" for the segment.
  Note
  For conditional expressions which reference attributes, the syntax "is NULL" is allowed. Using "is NULL" allows you to find results for which no value was specified for a particular attribute.
  Click the right arrow to open the popup where you can build the expression by selecting items and specifying constraints.
  ×
  Account Conditions
  Condition Expression
  Enter a SQL condition statement here to exclude or include information from the financial view. Alternatively, you can use the table below to describe the conditions you need and ActivReporter will build and add the conditions to this text box.
  Conditions table
  Use this table to construct the conditions you need in order to accurately select information for the financial view.
  Item
  From the drop-down list, select the account entity, or the segment, rollup, department, etc., to define a range or mask on. The items in the dropdown are filtered based on the selected function and are represented by their fully qualified names.
  Mask
  Enter a mask for the selected item values when the values you need to select have common characters but are not in a particular order.
  Press F3 to look up the value.
  If a value is already selected, you can press F4 to open the record in its native editor.
  Begin
  Enter the first value in the range of item values you need to select. Use a range when the values are in a defined order.
  Press F3 to look up the value.
  If a value is already selected, you can press F4 to open the record in its native editor.
  End
  Enter the last value in the range of item values you need to select. Use a range when the values are in a defined order.
  Press F3 to look up the value.
  If a value is already selected, you can press F4 to open the record in its native editor.
  Check Conditions button
  Click this button to check for SQL errors in the condition statement.
- Segment. From the drop-down list, select the segment for which to include information.
- Custom Field. Select the name of the custom field for which to include information.
When you finish, click OK.
If you want the column just defined to be included in column totals whenever the column is included in the HD view, mark the Include In Totals checkbox. Only columns which contain numeric values can be included in totals. The checkbox appears disabled for columns with non-numeric data.
Repeat steps 8-13 for each column you want to add to the financial view.
If you want to include another financial view as a subreport to this view, select the Subreport tab; otherwise, skip to step 18.
In the Subreports table in the Financial View column, select the name of the financial view to incorporate as a subreport.
In the Name column, enter a title for the subreport.
Repeat steps 16-17 for each subreport you need to incorporate in the financial view.
When you finish, save your changes.

Activate a financial view

Deactivate a financial view

View a financial view

After you create and activate a financial view, it becomes available in the ActivReporter > Financial Views folder. You can access the financial view in the HD view or you can print a report.

To filter and view financial data in the HD view:

In the Navigation pane, expand the ActivReporter > Financial Views folder and highlight the subfolder for the financial view you want to see.
If there are filters available for the view and you want to apply one or more of them, select the filter(s) from the Filters drop-down list. (To select multiple filters, press Ctrl as you select filters.)
The behavior of the Lookup field depends on the accounts, segments/rollups, and/or attributes specified when the financial view was defined.
- If you are reporting on account and no attributes are specified, the Lookup field is masked to accept account numbers only. Enter a partial account number, full account number, or account mask to show data for a particular account or group of accounts.
- If you are reporting on one or more segments/rollups and no attributes are specified, the Lookup field is masked to accept values/partial values/masks for the specified segments/rollups. Enter the appropriate values to show data for the specified segments/rollups.
- If you are reporting on account or reporting on segments/rollups AND attributes are also specified, the Lookup field is a text field. Enter appropriate values for the accounts and attributes OR for the segments/rollups and attributes to show data for.
By default, the Include Zero Amounts checkbox is marked. If you want to include rows in the view where all calculated amounts are zero, leave the checkbox marked. If you prefer to hide rows where all calculated amounts are zero, clear the checkbox.
The Period field defaults to the current period for the default calendar. To show data for a different period in the view, press F2 or click the right arrow on the Period field to open the Calendar Period popup.

Select the calendar period for which to include information in the view:
1. From the Calendar drop-down list, select the calendar to view financial data for.
2. 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 c.
  
  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 enter a period expression, ActivReporter adjusts the values in the Year and Period fields accordingly.
  
  If you enter a period expression, skip to step e.
3. From the Year drop-down list, select the fiscal year to view financial data for.
4. From the Period drop-down list, select the period to view financial data for.
5. Click OK to apply the calendar period change.
If you only want the financial view to show 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.
If you want the data in the view to reflect unmerged journal entries that are not on hold, mark the Include Unmerged checkbox.

Note

You can show more columns in the view if desired. To learn how to do so, see .

HD view

ActivReporter provides several ways to tweak the HD view to make it work for you.

Note

For more on how to show and hide columns when using the View Options feature, click here.

Showing columns

Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.
In the Hidden Columns list box, select the column(s) to show. You can use Ctrl and/or Shift selection to select multiple columns.
Click to move the selected column(s) to the Visible Columns list box.
Click OK.

Hiding columns

Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.
In the Visible Columns list box, select the column(s) to hide. You can use Ctrl and/or Shift selection to select multiple columns.

Note

Each entity has one or more identifier columns which are denoted in the Options dialog box by an asterisk (*). Identifier columns cannot be hidden and, consequently, cannot be removed from the Visible Columns list box.
Click to move the selected column(s) to the Hidden Columns list box.
Click OK.

Rearranging columns

Method 1

Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.
In the Visible Columns list box, highlight a column you want to move.
Use and to move the column to the desired position.
Repeat steps 2-3 for other columns until you achieve the desired order.
Click OK.

Method 2

In the HD view, click the heading of the column you want to move and hold the mouse button down.
Drag the column to the desired position. A vertical blue line indicates where the column will be moved.
Release the mouse button. The column is moved to its new position.
Repeat steps 1-3 for any other columns you need to move.

Resizing columns

You can shorten or widen the display area of columns.

Method 1

Right-click in the HD view header row to open the shortcut menu. If you want to resize a particular column, right-click on that column in the header. If you want to resize all columns, right-click anywhere in the header.
From the menu, select Size Column to Fit to size the column you clicked on to the width of the longest value visible in the column, or select Size All Columns to Fit to size each column to the width of its longest visible value.

Method 2

Hover over the vertical divider at the right end of the column you want to resize. The cursor changes to .
Click and drag to the right to widen the column; to the left to shorten it.
When you achieve the desired width, release the mouse button.

Restoring default column settings

At any time you can revert to the column settings you had for a folder when ActivReporter was installed.

Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.
Click Defaults. The Hidden Columns and Visible Columns list boxes are restored to their original settings as is the HD view.
Click OK.

Built-in financial views

A financial view was mysteriously deactivated

The following image shows a sample financial view in the HD view:

Financial Views Listing

The following table shows the financial view functions along with any required or optional arguments.

Items expressions allow you to limit information by any available attribute that makes sense in the context of the selected function.

When writing conditional expressions it is important to avoid ambiguity in identifying the item you want ActivReporter to evaluate. When ambiguity is possible, you should fully qualify the item name.

Example

To distinguish the GL account entity from a GL segment named "Account", use "GL.Account" for the entity and "GL.Segment.Account" for the segment.

Note

For conditional expressions which reference attributes, the syntax "is NULL" is allowed. Using "is NULL" allows you to find results for which no value was specified for a particular attribute.

Function	Description	Required Arguments	Optional Arguments
Activity	Calculates the activity of an account or set of accounts as of the specified period.		Period Expression Items Expression^GL
Balance	Calculates the balance of a set of accounts as of the specified period.		Period Expression Items Expression^GL
Begin Balance	Calculates the beginning balance of a set of accounts as of the specified period.		Period Expression Items Expression^GL
Year To Date	Calculates the year-to-date activity of a set of accounts as of the specified period.		Period Expression Items Expression^GL
Credit Activity	Calculates the credit activity of a set of accounts as of the specified period.		Period Expression Items Expression^GL
Credit Balance	Calculates the credit balance of a set of accounts as of the specified period.		Period Expression Items Expression^GL
Credit Year to Date	Calculates the year-to-date credit activity of a set of accounts as of the specified period.		Period Expression Items Expression^GL
Debit Activity	Calculates the debit activity of a set of accounts as of the specified period.		Period Expression Items Expression^GL
Debit Balance	Calculates the debit balance of a set of accounts as of a specified period.		Period Expression Items Expression^GL
Debit Year to Date	Calculates the year-to-date debit activity of a set of accounts as of the specified period.		Period Expression Items Expression^GL

Segment Name	Returns the name of the specified segment.	Segment
Segment Plural Name	Returns the plural name of the specified segment.	Segment
Segment Memo	Returns any memo recorded on the specified segment.	Segment
Segment Custom Field	Returns a custom field value of the specified segment.	Custom Field Segment
Segment Item	Returns the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item Description	Returns the description of the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item Short Description	Returns the short description of the segment item of the specified segment and optional segment item.	Segment	Segment Item Description
Segment Item Sort Order	Returns the sort order of the segment item.	Segment	Items Expression^GL
Segment Item Name	Returns the name of the segment item.	Segment	Items Expression^GL
Segment Item Address 1	Returns the first address line of the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item Address 2	Returns the second address line of the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item City	Returns the city of the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item State	Returns the state of the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item Zip Code	Returns the Zip code of the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item Country	Returns the country of the item of the specified segment and optional segment item.	Segment	Items Expression^GL
Segment Item Memo	Returns any memos recorded on the segment item.	Segment	Items Expression^GL
Segment Item Custom Field	Returns a custom field value of the item of the specified segment and optional segment item.	Custom Field Segment	Items Expression^GL

Account Description	Returns the description of the account.		Items Expression^GL
Account Alias	Returns the alias for the account.		Items Expression^GL
Account Begin Date	Returns the begin date of the account.		Items Expression^GL
Account End Date	Returns the end date of the account.		Items Expression^GL
Account Date Control	Returns the date control of the account.		Items Expression^GL
Account Created (Date)	Returns the date the account was created.		Items Expression^GL
Account Created (DateTime)	Returns the date and time the account was created.		Items Expression^GL
Account Created By	Returns the user name that created the account.		Items Expression^GL
Account Modified (Date)	Returns the date the account was last modified.		Items Expression^GL
Account Modified (DateTime)	Returns the date and time the account was last modified.		Items Expression^GL
Account Modified By	Returns the user name that last modified the account.		Items Expression^GL
Account Memo	Returns any memos about the account.		Items Expression^GL
Account Custom Field	Returns a custom field value from the account.	Custom Field	Items Expression^GL

Period Number	Returns the number of the specified period.		Period Expression
Period Description	Returns the description of the specified period.		Period Expression
Period Year	Returns the year of the specified period.		Period Expression
Period Year Description	Returns the description of the year of the specified period.		Period Expression
Period Work Days	Returns the number of work days of the specified period.		Period Expression
Period Begin	Returns the begin date of the specified period.		Period Expression
Period End	Returns the end date of the specified period.		Period Expression
Period Year Begin	Returns the begin date of the year of the specified period.		Period Expression
Period Year End	Returns the end date of the year of the specified period.		Period Expression
Period Calendar	Returns the calendar of the specified period.		Period Expression
Period Calendar Description	Returns the calendar description of the specified period.		Period Expression
Period Calendar Memo	Returns any memos recorded on the calendar of the specified period.		Period Expression
Period Calendar Custom Field	Returns a custom field value from the calendar of the specified period.	Custom Field	Period Expression

Company Name	Returns the name of the optional company.
Company Legal Name	Returns the legal name of the optional company.
Company Address 1	Returns the first address line of the optional company.
Company Address 2	Returns the second address line of the optional company.
Company City	Returns the city of the optional company.
Company State	Returns the state of the optional company.
Company Zip Code	Returns the Zip code of the optional company.
Company Country	Returns the country of the optional company.
Company Memo	Returns any memos recorded on the optional company.

Financial View Record ID

Name

A unique name for the financial view.

Note

In the next two fields, if you designate a combination of segments/rollups and/or attributes for the view, the combinations of segments/rollups and/or attributes found in the journal detail will define the rows in the financial view.

Segments

The names of the segments/rollups to include in the financial view. If you enter the segments/rollups manually, separate them by commas. Otherwise, click at the end of the field to open the Financial View Segments dialog box where you can select the segments/rollups to include.

Do one of the following:

If you want to report on all accounts in the chart of accounts, select "Report on Accounts".
If you want to iterate over specific segments/rollups:
1. Select "Specify Segments".
2. In the Available Segments list box, highlight the segment(s)/rollup(s) to include, then click to move your selection(s) to the Assigned Segments list box. You can use Ctrl and/or Shift selection to select multiple segments/rollups.
3. If you selected multiple segments/rollups, use and to arrange them in the desired order.

Attributes

The attributes for which to include information in the financial view. If you enter the attributes manually, separate them by commas. Otherwise, click

at the end of the field to open the Financial View Attributes dialog box where you can select the attributes to include information for.

Financial View tab

Columns table

Use this table to define the columns in the financial view.

Name

A label for the column.

Function

The function that retrieves the data shown in the corresponding column.

Arguments

The which determine the scope of the data returned by the selected function.

Financial View Column Arguments

Depending on the function selected, none, one, or a combination of the following argument types may be prompted for.

Period Expression

Enter a period expression to describe the reporting period. Period expressions are optional arguments.

Items Expression

Enter a conditional expression to describe the information to include. The conditions you can apply depend on the function selected. For General Ledger functions, you can define conditions based on account numbers, segment items, or rollup items. In general terms, conditional expressions allow you to limit information by any available attribute that makes sense in the context of the selected function.

When writing conditional expressions, it is important to avoid ambiguity in identifying the item you want ActivReporter to evaluate. When ambiguity is possible, you should fully qualify the item name.

Examples

To distinguish the GL account entity from a GL segment named "Account", use "GL.Account" for the entity and "GL.Segment.Account" for the segment.

Items expressions are optional arguments.

Press F2 to open the Account Conditions dialog box where you can build and verify a conditional statement.

Custom Field

Select the name of the custom field for which to include information. Custom fields are required arguments.

Press F3 to look up the value.

Segment

From the drop-down list, select the GL segment for which to include information. Segments are required arguments.

Include In Totals

If marked, indicates the column will be included in column totals whenever the column is included in the HD view. Only columns which contain numeric values can be included in totals. The checkbox appears disabled for columns with non-numeric data.

Subreports tab

Subreports table

Use this table to add any subreports you want to incorporate in the financial view.

Financial View

A financial view included as a subreport.

Press F3 to look up the value.

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

Name

A title for the subreport.

[Financial View Name] tab

The [Financial View Name] pane shows the [Financial View Name] Financial View Items HD view filtered to show all financial view items for the selected financial view.

Double-click a row in the pane to drill down to its record in the [Financial View Name] Financial View Item window.

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.

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.

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.

Financial Views Listing

Purpose

The Financial Views Listing provides a list of financial views defined in your system.

Content

For each financial view included on the report, the listing shows:

whether the view is active
which segments/rollups are included in the view or if the view is a report on accounts.

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

column detail (column number, column name, segment, period expression, items expression)
timestamps
memos
custom fields.

The following total appears on the report:

record count.

Print the report

In the Navigation pane, highlight the ActivReporter > Financial Views folder.
Start the report set-up wizard.
- To report on all or a filtered subset of financial views:
  1. Right-click the Financial Views folder and select Select and Report > Financial Views Listing from the shortcut menu.
  2. On the Selection tab, define any filters you want to apply to the data.
- To report on specifically selected financial views:
  1. In the HD view, select the financial views 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 view from the Financial View window:
  1. In the HD view, locate and double-click the financial view you want to report on. The Financial View window opens with the financial view loaded.
  2. Click .
Select the Options tab.
Mark the checkbox(es) for the additional information to include:
- Column Detail
- 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 dialog so that you can address and compose an e-mail that the report will be attached to.
- - Opens the Export Report dialog so that you can save the report to a file. File types include Crystal Reports (.rpt), PDF (.pdf), Microsoft Excel (.xls), Microsoft Word (.doc), rich text (.rtf), and XML (.xml).

Report Email dialog

From

The email account from which to send the email. Valid options are:

Windows user default account. Sends email using the user's Windows default email account. For most users, this is the account configured in Outlook or another email client application.
Server personal. Sends email using the email configuration for the system or company server and the email address on the current user's authorized user record. The authorized user record must have a confirmed email address.
Server generic. Sends email using the email configuration and "from" address for the system or company server. This option requires "Send generic" access to the Server Email resource.

The email address(es) to which to send the email. Separate email addresses with semi-colons.

The email addresses to copy on the email. Separate email addresses with semi-colons.

BCC

The email addresses to blind copy on the email. Separate email addresses with semi-colons.

Subject

The subject line.

Text box

The body of the email.

Data extensions

The following data extensions are available for the report:

Financial views
Financial view columns

Financial views

Create a financial view

Activate a financial view

From the HD view...

From the Financial View window...

Deactivate a financial view

From the HD view...

From the Financial View window...

View a financial view

Built-in financial views

A financial view was mysteriously deactivated

Financial View Record ID

Financial View tab

Subreports tab

[Financial View Name] tab

Custom tab

Fields subtab

References subtab

Exchange Folder subtab

File subtab

Internet Address subtab

Network Folder subtab

Other tab