Advanced Spreadsheets and Reporting Documentation
Index

Initial Configuration

After installing and registering Advanced Spreadsheets and Reporting, follow these steps to configure your first indicators and start using the extended functionality.

Configuration Overview

Initial setup involves:

  1. Understanding the enhanced Financial Reports structure
  2. Creating your first indicator
  3. Testing the indicator
  4. Building a financial report with indicators

Step 1: Navigate to Financial Reports

  1. Search for Financial Reports in Business Central
  2. Open an existing financial report or create a new one
  3. Click on the Row Definition to edit the account schedule

Step 2: Understanding the Enhanced Account Schedule

With Advanced Spreadsheets and Reporting installed, you'll notice new options on the Row Definition page:

New Fields on Account Schedule Lines

Field Description
Company Specify a company to pull data from a different company
Indicator Defined Shows if an indicator is configured for this line

New Totaling Type

A new totaling type is available:

  • Indicator: Marks the line as using an AFR indicator instead of standard G/L totaling

Step 3: Creating Your First Indicator

3.1 Add a New Line

  1. In the row definition, add a new line
  2. Fill in the Row No. and Description
  3. Set the Totaling Type to Indicator

3.2 Configure the Indicator

  1. With Totaling Type set to Indicator, the Totaling field shows "-"
  2. Use the assist-edit button (…) on the Indicator Defined column
  3. The Define Indicator card opens

3.3 Set Up the Indicator

On the Define Indicator card:

  1. Method: Choose the calculation method
    • Lookup: Get a single value
    • Sum: Total all matching values
    • Count: Count matching records
    • Min/Max: Get minimum or maximum value
    • Average: Calculate the average
  2. Table: Click the assist-edit button to select the source table
    • A list of all tables in Business Central appears
    • Select the table containing your data
  3. Field: Click the assist-edit button to select the numeric field
    • Only Decimal, Integer, and BigInteger fields are shown
    • Select the field to calculate

3.4 Add Filters

In the Filters section of the Define Indicator card:

  1. Click in the filter list to add a new filter
  2. Select a Field from the source table
  3. Choose the Filter Type:
    • Static: Enter a fixed filter value
    • Global Dim 1: Use the dimension 1 filter from the financial report
    • Global Dim 2: Use the dimension 2 filter from the financial report
    • Column Period: Apply date filtering based on the column layout
  4. For Static filters, enter the value in the Filter column
  5. For Column Period filters, optionally add a Date Formula for adjustments

Step 4: Testing the Indicator

Before using the indicator in reports, test it:

  1. On the Define Indicator card, find the Test Indicator section
  2. Set the Test period start and Test period end dates
  3. Select a Test Column Layout
  4. Click Calculate Indicator (for test)
  5. Review the Test Result and elapsed time

If the result is unexpected:

  • Verify your table and field selection
  • Check your filters
  • Ensure you have data in the specified date range

Step 5: Using the Indicator Library (Optional)

If a pre-built indicator exists in the library:

  1. On the Define Indicator card, click Select from library
  2. Browse available indicators
  3. Select the appropriate indicator
  4. The configuration is applied automatically

Importing Library Indicators

To import the standard indicator library:

  1. Search for Indicator Library
  2. Click Import to download indicators from the Hougaard library — the public GitHub repository hougaard/AdvancedFinancialReportingIndicators; the environment needs outbound access to api.github.com and raw.githubusercontent.com
  3. The library is populated with common indicator definitions

Step 6: Configure Column Definitions (Optional)

For multi-company scenarios, you can specify a company on column definitions (column layouts):

  1. Search for Column Definitions
  2. Select or create a column definition
  3. In the Company field, specify a company name
  4. This column will pull data from that company

Step 7: Setting a Default API Report (Optional)

If you plan to use the API features:

  1. Search for Financial Reports API List
  2. Select your primary financial report
  3. Click Set Default
  4. This report will be used when API calls don't specify a report

Example: Customer Balance Indicator

Here's a complete example of setting up a customer balance indicator:

  1. Line Setup:
    • Row No.: CustBal
    • Description: Total Customer Balance
    • Totaling Type: Indicator
  2. Indicator Configuration:
    • Method: Sum
    • Table: Customer Ledger Entry
    • Field: Amount
  3. Filters:
    • Field: Document Type
    • Filter Type: Static
- Filter: Invoice Credit Memo
  1. Date Filter (for period sensitivity):
    • Field: Posting Date
    • Filter Type: Column Period
  2. Test: Set dates and verify the calculation

Next Steps

After completing initial configuration:

Troubleshooting

Indicator Returns Zero

  • Verify the license is registered
  • Check table and field selection
  • Verify filters aren't excluding all records
  • Test with simpler filters first

"Table not found" Error

  • The table may not exist in your environment
  • Some tables require specific licenses or modules

Slow Indicator Calculation

  • Add more specific filters to reduce record count
  • Use SumIndex fields where available
  • Consider using Lookup instead of Sum for single records