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.

  1. In the Navigation pane, highlight the ActivReporter > Financial Views folder.
  2. Click . The New Financial View window opens.

  3. Enter a unique Name for the financial view.
  4. 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.

  5. 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.
  6. Click OK.
  7. In the Attributes field, select the attribute(s) to report on.
  8. 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.

  9. In the first row of the Columns table in the Column field, type a label for the first column of the financial view.
  10. From the Function drop-down list, select the function that returns the type of data you want to retrieve for the column.
  11. 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.
  12. 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.

      x

      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.

      x

      Account Conditions

      Condition Expression

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

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

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

      Press F3 to look up the value.

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

      Begin

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

      Press F3 to look up the value.

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

      End

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

      Press F3 to look up the value.

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

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

       

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

  13. When you finish, click OK.
  14. Repeat steps 8-12 for each column you want to add to the financial view.
  15. If you want to include another financial view as a subreport to this view, select the Subreport tab; otherwise, skip to step 17.
  16. In the Subreports table in the Financial View column, select the name of the financial view to incorporate as a subreport.
  17. In the Name column, enter a title for the subreport.
  18. Repeat steps 15-16 for each subreport you need to incorporate in the financial view.
  19. When you finish, save your changes.

Activate a financial view

Before you can use a financial view, you must activate it. Once activated, a folder for the view is added as a subfolder of the Financial Views folder. You can activate a financial view from the HD view or from the Financial View window.

From the HD view...

  1. In the Navigation pane, highlight the ActivReporter > Financial Views folder.
  2. In the HD view, select the financial view to activate.
  3. Click . You are prompted to confirm your action.

  4. Click OK.

From the Financial View window...

  1. Open the financial view record to activate in the Financial View window.
  2. Click . You are prompted to confirm your action.

  3. Click OK.

Note

You cannot edit an active financial view (except for its name). If you need to make changes, first deactivate the view, and then edit it.

Deactivate a financial view

If you don't need a financial view for the time being, but think you might need it at a later time, you can deactivate it. When you deactivate a view, its subfolder is removed from the Financial Views folder. You can deactivate a financial view from the HD view or from the Financial View window.

From the HD view...

  1. In the Navigation pane, highlight the ActivReporter > Financial Views folder.
  2. In the HD view, select the active financial view you want to deactivate.
  3. Click . You are prompted to confirm your action.

  4. Click OK.

From the Financial View window...

  1. Open the financial view record to deactivate in the Financial View window.
  2. Click . You are prompted to confirm your action.

  3. Click OK.

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:

  1. In the Navigation pane, expand the ActivReporter > Financial Views folder and highlight the subfolder for the financial view you want to see.
  2. 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.)
  3. 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.
  4. 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.
  5. 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.
  6. 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 .

x

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

  1. Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.

  2. In the Hidden Columns list box, select the column(s) to show. You can use Ctrl and/or Shift selection to select multiple columns.
  3. Click to move the selected column(s) to the Visible Columns list box.
  4. Click OK.

Hiding columns

  1. Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.
  2. 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.

  3. Click to move the selected column(s) to the Hidden Columns list box.
  4. Click OK.

Rearranging columns

Method 1

  1. Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.
  2. In the Visible Columns list box, highlight a column you want to move.

  3. Use and to move the column to the desired position.
  4. Repeat steps 2-3 for other columns until you achieve the desired order.
  5. Click OK.

Method 2

  1. In the HD view, click the heading of the column you want to move and hold the mouse button down.

  2. Drag the column to the desired position. A vertical blue line indicates where the column will be moved.

  3. Release the mouse button. The column is moved to its new position.

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

  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.

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

  1. Hover over the vertical divider at the right end of the column you want to resize. The cursor changes to .

  2. Click and drag to the right to widen the column; to the left to shorten it.
  3. 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.

  1. Right-click in the HD view and select Options from the shortcut menu. The Options dialog box opens.

  2. Click Defaults. The Hidden Columns and Visible Columns list boxes are restored to their original settings as is the HD view.
  3. Click OK.

 

Built-in financial views

ActivReporter comes with three built-in financial views to kickstart your analysis efforts:

  • Activity by Month by Category. Based on the "GP Category" rollup, this view shows activity for periods 1 through 12 for the "Fiscal" calendar.
  • Trial Balance. This view shows activity by GL account for the specified calendar, year, and period.
  • Trial Balance by Category. Based on the "GP Category" rollup, this view shows activity for the specified year and period for the "Fiscal" calendar.

To use a built-in financial view, you must first activate it. Once activated, the corresponding financial view folder is available in the ActivReporter > Financial Views submenu. Select a financial view from the menu to view its results in the HD view.

You can also access these financial views from the Financial Reporting gadget on the company dashboard.