Financial designs

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 topics on the "Design creation" tab.

1. In the Navigation pane, highlight the ActivReporter > Financial Designs folder.

2. Click . The New Financial Design window opens.

   

3. Enter a unique Name for the financial design. You can use up to 40 characters.

4. Click Workbook. The Financial Options dialog box opens.

   

5. From the Calendar drop-down list, select the calendar to base the financial statement on.

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

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

7. From the Year drop-down list, select the year of the period to report on.
8. From the Period drop-down list, select the period to report on.
9. 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.
10. 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.
11. If you want to display actual data in your financial design, mark the Use Live Data checkbox; otherwise, the design will show zeros for amounts.
12. 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".
13. 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 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.

14. Click OK.
15. Set up the financial design in the open Excel worksheet.
16. When you finish, save the financial design worksheet in Excel.
17. Close Excel.
18. In the Financial Design window, save the financial design record.

When you open a financial design for editing, you are opening the most recent version of the design (unless you intentionally select an older version). 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 ActivReporter, a new version of the design is added to the ActivReporter database and past versions of the design are retained (unless you choose to delete them).

Open a historical version of a financial design

Restore a historical version of a financial design

Delete a historical version of a financial design

If you have a financial design saved, for whatever reason, outside of the tmp directory where ActivReporter puts Excel design files for editing, it is possible to load that design for use in ActivReporter. Here's how:

1. Create a new financial design record or open an existing design record to add a new version of it.
2. From the toolbar, select > Tools > Load Workbook from File. The Windows Open dialog opens.
3. Navigate to and select the financial design you want to load.
4. Click Open.
5. Save the financial design record.

You can save a financial design stored in ActivReporter to an external file. To do so:

1. Open the financial design record in ActivReporter.
2. From the toolbar, select > Tools > Save Workbook to File. The Windows Save As dialog opens.
3. Navigate to and select the location where you want to save the financial design.
4. In the File name field, enter a file name for the financial design.
5. Click Save.

Financial design records can be deleted with impunity.

To delete a financial design record, highlight it in the HD view and click , or open the financial design and select > Edit > Delete. In either case, ActivReporter prompts you to confirm your action. Click Delete to delete the financial design.

Large workbooks and calculation mode

The default calculation mode for ActivReporter 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 ActivReporter application, AccountingWare recommends that you store workbooks in ActivReporter. The advantages of storing in ActivReporter include:

Versioning

Iteration

General Ledger > Financial Designs > [right-click] > Select and Publish 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 ActivReporter 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, ActivReporter security will secure access to your financial information.

Compound formulas in an Excel cell

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

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

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

or

GP 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:

"GP 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:

""GP 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:

"""GP Balance Sheet"" = 032"

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

GP Balance Sheet = $A7

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

To avoid this confusion, there is a special ActivReporter ItemExpression function 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("GP 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("GP Balance Sheet", $A7)

The ItemExpression function can accept an unlimited number of item/value pairs.

ItemExpression(A1,A2,B1,B2,C1,C2,...) equates to "(A1=A2 AND B1=B2 AND C1=C2 AND ...)"

Tips for designing HUGE workbooks

ActivReporter 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 ActivReporter 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 ActivReporter's Financial Designs folder
• Account 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.

Publish 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 Publish Financial Statements 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 in Dynamics GP. 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.

Account Financial Report augmented with BOTs

The Account Financial Report is a special report located at ActivReporter > Reports > Account Financial Report or ActivReporter > Chart of Accounts > [right-click] > Select and Report > Account 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 using VBScript or JScript.

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 ActivReporter. 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 as well as drillback to Dynamics GP.

PDFs

PDFs are static and suitable for printing or saving to a library in the Windows file system or for attaching to the financial design record itself in ActivReporter.

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

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

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 ActivReporter's extended function library for Excel plus ActivReporter's expression syntax and how to apply it.

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

1. Create rollups.
2. Add appropriate rollup items to the rollups.

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:

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 ActivReporter 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. The arguments of the Activity function are Period, Ledger, Account(s), Company, and Unit.

1. Place your cursor in the cell where you want to insert a value.

2. In the ActivityHD ribbon in the Functions group, select Financial > Activity. The Function Arguments dialog box opens.

   

3. In the Period field, enter a period expression for the current period such as "Period 1 of 2016".
4. In the Items1 field, enter a segment expression for the ledger such as "Ledger=A".
5. 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=031".
6. Click OK.
7. 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 sales revenue for the current period has been pulled into the design (provided you requested live data when you set up the design).

   

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

9. Next, we'll use the power of Excel to populate the row for the remaining revenue category. Select the cell we've already calculated, then click and drag its fill handle to fill in the value.

   

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

    

11. Press Enter to accept the highlighted range of cells and insert the sum.

    

12. Select the values in the Current Revenue section and format them as currency.

    

13. Let's make the label on the Current column dynamic. To do so, highlight the cell with the "Current" label and from the ActivityHD ribbon in the Functions group, select Periods > Period Description. The Function Arguments dialog box opens.

    

14. In the Period field, reference the field where you stored the text "Current".

    

15. Click OK.

    

16. Next, let's make the category labels dynamic as well. Highlight the cell with the first category label and from the ribbon in the Functions group, select Segments > SegmentItemDescription. The Function Arguments dialog box opens.

    

17. The SegmentItemDescription function has three arguments, Segment (or rollup), Item (segment item or rollup item), and Company. In the Function Arguments dialog box 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.

    

18. Click OK.

19. Finally, drag the fill handle on the first revenue description down to the last revenue description.

    

20. Select the column formerly known as "Current", copy it, and paste the content into the YTD column.

    

21. Replace the label on the revenue year-to-date column with the literal text "YTD" or some variation thereof.

    

22. To show year-to-date amounts, we need a different function. Not surprisingly, 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".

    

23. 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".)

    

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

    

25. Replace the "YTD" label in the Budget column with "Budget".
26. 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".

    

27. 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. When you paste, use the Paste Special > Formulas option.
28. In the formula bar, change the first parameter to refer to the name of the rollup that contains expense rollup items.

    

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

    

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

    

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

    

32. Drag the fill handle on the cell that contains the current expense amount for the first expense category to the last expense category.

    

33. Use the SUM function to calculate total expenses for the current column.

    

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

    

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

    

36. Drag the fill handle down to the last expense category in the year-to-date column.

    

37. Use the SUM function to calculate total expenses for the year-to-date column.

    

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

    

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

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

   

2. From the ActivityHD ribbon in the Layout group, select Print Area > Set Print Area.
3. In the Output group, click Print. The Print dialog opens.
4. Select the print options you need, then click OK.
40. When you finish, save the Excel workbook and close it.
41. Save the financial design record.

You can suppress rows which contain zeros from your financial statements. In the sample income statement below, notice that the values in rows 13, 14, 18, 20, 24, 25, and 34 are zeros.

Hidden formula filter method (featuring the IsLiveData function)

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

1. Open the financial design worksheet, making sure you mark the Use Live Data checkbox in the Financial Options dialog box.

2. Add a hidden column in your financial design.

   

3. Determine which column to test for the presence of zeros. This will be a column which holds monetary values. (Notice we're moving rows 1-3 and columns A-B out of the view to lessen distraction.)

   

The formula

To test for zeros we will compose a formula which uses Excel's IF function (aka Boolean function) and ActivReporter'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 publish 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(F12=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(F12=0) will be equal to 1 only if both are equal to 1.

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

4. 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 "F12" with the appropriate cell position in your worksheet:

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

   

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

6. Select the hidden column, then select the Data ribbon and click Filter. A dropdown is added to the first cell in the column.

   

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

   

8. From the adjacent drop-down list, select "Hide".

   

9. Click OK. The rows that contain "Hide" in the hidden column are suppressed.

   

10. Save your changes in Excel.
11. Close the worksheet.
12. Save your changes in ActivReporter 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

In the previous example, we actually had amounts in two columns, but we suppressed zeros when the first column had zeros. This works great if you have only one column of amounts, but if you have multiple columns of data in your financial design, say amounts by month and year-to-date, or by period and prior year period as above, 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.

We'll pick up where we left off in the prior example.

1. Open the financial design worksheet, making sure you mark the Use Live Data checkbox in the Financial Options dialog box.

2. Determine which additional column(s) to test for the presence of zeros.

   

3. 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")

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

   

   In our example, the results mimic those from the one-column procedure since every row with a zero in column F also has a zero in column H.

5. Now apply the filter on the hidden column, showing everything that does not equal "Hide".

   

New Copy

Suppose you have a financial design and want to create another similar design. If your financial designs are stored in the ActivReporter database (best practice), this is a simple operation. In the Financial Designs HD view, highlight the design you want to emulate, right-click, and select New Copy from the menu. Provide a unique name for the new record, and you have the starting point for a new financial design.

You can also use the New Copy feature on the following entities:

• Calendars
• Financial views

Export/Import

The New Copy feature is not available for rollups; however, there is a method for creating a rollup similar to an existing one. Using the Export command available in the right-click menu of a Rollup Items HD view, export the rollup items to an XML file on the Windows file system. Next, use the Import command to recreate the rollup in the same company with a different name.

Another useful application of Export/Import is to move rollup structures from one company database to another!

The Export and Import commands are also available for:

• Calendars
• Financial views
• Rollups

That means that these entities can also be moved from one company database to another.

Report profiles for convenience and consistency

As you work with ActivReporter, you may find yourself entering the same answers to a dialog full of checkboxes, dates, and options repetitively. To alleviate this tedium, ActivReporter provides a report profile feature which allows you to save oft-used answers for subsequent reuse. The next time you run the dialog and need the same settings as a named report profile, simply load the report profile and ActivReporter fills in the answers for you!

Report profiles are available in the Financial Options dialog of financial designs and on all ActivReporter reports.

Reporting ledgers for intercompany eliminations

The reporting ledgers in Dynamics GP were designed to create journal entries which optionally could be included in financial reporting. These ledgers were for companies needing international reporting (IFRS) as well as reporting in the US (FASB). The ledgers can also be used for posting intercompany elimination entries or for tax reporting (depreciation, meals, etc.).

ActivReporter supports the Dynamics GP reporting ledgers.

Excel's outline feature

Excel includes a special feature called "outlining". Outlining allows you to embed supporting summary items into your Excel workbook details. Outlining can be applied to both rows and columns.

ActivReporter fully supports the Excel outlining feature. In fact, when publishing reports from Excel, you can determine the outline level to use on a "printed" (non-Excel) publication.

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

Financial	Segments	Accounts
Periods	Miscellaneous	Companies
	Validate

FinancialCalculator.Drilldown:
    <Function @Name @Period @Items1 @Items2 @Items3 @Company @Unit/>

Company.RunAction("Drilldown")
    <Function @Value/>
    <CalendarPeriod>
        <Calendar @Value/>
        <Year @Value/>
        <Period @Value/>
        <PeriodContains @Value/@Expression/>
    </CalendarPeriod>
    <IncludeUnmerged @Value(True/False)/>
    <ItemsExpression @Value/>
    <Calculate @Value(True/False)/>

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.

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.

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". When you are online, the button is labeled with the name of the company you're connected to.

Connect to a dataset

Disconnect from a dataset

Change the dataset you are connected to

Calculate a worksheet

Calculate all open worksheets immediately

Force a full calculation of all open workbooks

Some native Excel functions cannot recalculate financial designs sufficiently. For this reason, ActivReporter provides three refresh actions to fill the gap.

Refresh data and filters

Refresh data

Refresh filters

Set a print area

Clear the print area

Expand the print area

Hide non-printing areas

Show non-printing areas

Turn on auto-filtering

Apply filtering

Turn off auto-filtering

Clear all filtering

Reapply filtering

A good way to jumpstart a financial design is to start with a built-in layout. ActivReporter 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:

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

   

3. Click Yes or No. You are prompted with the opportunity to filter the data for the report.

   

4. If you want 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.

   

5. Define selection criteria for the financial design as needed. When you finish, click OK to close the Selection dialog.

6. Click OK to close the Financial Items prompt. ActivReporter advises you how many rows will be created in the generated output.

   

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

Balance Sheet Current vs Previous Year

Income Statement

Income Statement Actual vs Budget YTD

Income Statement Current vs Previous Year

Income Statement Current vs YTD

Income Statement Current vs YTD vs Previous Year

Trial Balance
<
>
8. Tweak the design as needed.
9. When you finish, save your changes.

Several sample designs are provided so that you can see a variety of techniques for generating financial reports. When ActivReporter is installed, these samples are copied to the user's computer. In most cases, the sample designs are copied to the following folder:

C:\ProgramData\AccountingWare\Activity Explorer\Financial Design Samples\ActivReporter

When you access a sample file, the selected file is copied to a new file so that the original file is not overwritten if you save it. If you choose to save the file, you are prompted for a saving location via the standard Excel Save dialog.

If there are no sample files on your computer, the Samples icon will not be visible in the toolbar.

To access a sample financial design:

1. Start Excel.
2. Select the ActivityHD ribbon.
3. In the Layout group, click Samples. A drop-down menu opens with the list of available samples.
4. Select the sample to open. A copy of the sample design loads in Excel.
5. Enjoy investigating or modifying the sample!

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

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

Prerequisites

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

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

Publish financials using the Financial Tree Reporting bot

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

   

2. In the Design field, select the financial design to publish. If the configuration file you select in the Config File field specifies a different design on its "Reports" tab, the design in the configuration file replaces any design specified here.
3. From the Calendar dropdown, select the calendar to base the financials on.

4. To define the reporting period using a relative expression, select or enter the period expression in the Expression field. If you prefer to explicitly enter the year and period, skip to step 5.

   You can select from the following common period expressions:

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

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

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

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

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

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.

14. Click Next >>. You are prompted to confirm that you want to publish the financials.

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

16. Click Finish.

#VALUE! error

If a cell containing an ActivReporter function indicates #VALUE!, click the Log button in the ActivityHD ribbon for help identifying possible solutions.

"ActiveX component can't create object" error

This error usually indicates that the bitness of Excel (32-bit vs. 64-bit) does not match the bitness of ActivReporter.

Ensure that the bitness of Excel and ActivReporter is the same. If necessary, uninstall ActivReporter and reinstall the correct bitness of ActivReporter.

ActivReporter database synchronization is not occurring

If changes in Dynamics GP are not being updated in ActivReporter (i.e., the database is not synchronized), check for the following conditions:

• ActivSync is not enabled on the database. Go to the company dashboard and check the "ActivSync Status" gadget.

  

  When you configure a company in ActivityHD Manager, you are asked if you want to enable ActivSync. Normally, you would agree to this option. If for some reason you did not, you can rerun the Company Configuration wizard in ActivityHD Manager and enable ActivSync. An already configured company can be reconfigured at any time!

  

• The Service Broker External Activator is not running.

  

Drillback to Dynamics GP is not working

To use the drillback feature, you must be currently logged into Dynamics GP and you must be logged into the company that you are drilling back to.

If you are not logged into Dynamics GP, or if the company currently in focus is not the company you are attempting to drill back on, the following error is issued:

Excel is unresponsive

If you are working on a financial design and the workbook does not respond to mouse clicks, it is often because a drill-down window is open in the background.

Drill-down windows are modal and lock Excel from any further operations. Sometimes the modal window is not visible because it is minimized or occluded on the desktop. To recover from this condition, click the Excel icon in the taskbar (or use the Alt+Tab combination to navigate to Excel) to bring Excel and all its companion windows to the foreground. When you do this, the modal drilldown should appear on top of the Excel workbook. Close the modal window and Excel should resume normal function.

Excel suddenly reports "N/A" on all cells containing ActivReporter functions

We see this periodically. Typically, this sudden pockmarking of your worksheet with "N/A" occurs when pasting previously cut cell(s). If you force Excel to re-evaluate all formulas in all cells, the problem generally evaporates. To force Excel to perform a "deep" recalculation, press Ctrl+Alt+Shift+F9.

New rollup does not work

If a new rollup is created while you are editing a financial design, it will not be recognized (and, hence, will not produce results within ActivReporter functions). The delay in availability of the new rollup is because rollup and segment information is cached within the financial design. To remedy this, disconnect and reconnect to the ActivReporter company. This action refreshes the cache and causes the rollups to be recognized and become usable.