API Overview

Advanced Spreadsheets and Reporting provides API endpoints that allow external applications to access financial report data. This guide introduces the API capabilities and how to use them.

What Are the APIs?

Advanced Spreadsheets and Reporting exposes four OData-compatible API endpoints in two families:

1. CalcDate API (calcDateLines, calcDateLines24): Uses date formulas for period selection
2. DateFilter API (dateFilterLines, dateFilterLines24): Uses specific date filters

The ...24 variants return 24 column values instead of 12, for column definitions with more than 12 columns. An older endpoint, financialReportLines in API group obsolete, is still published but marked obsolete; use the endpoints above.

These APIs allow external tools like Power BI, Excel, or custom applications to retrieve calculated financial report data.

API Endpoints

CalcDate API

Endpoint: api/hougaard/financialReport/v2.0/calcDateLines (or calcDateLines24)

Uses date formulas (like -CY for start of current year) to define the reporting period.

DateFilter API

Endpoint: api/hougaard/financialReport/v2.0/dateFilterLines (or dateFilterLines24)

Uses actual date values to filter the reporting period.

Base URL Structure

Business Central Online (SaaS)

https://api.businesscentral.dynamics.com/v2.0/{tenant-id}/{environment}/api/hougaard/financialReport/v2.0/

Business Central On-Premises

https://{server}:{port}/{instance}/api/hougaard/financialReport/v2.0/

Authentication

Online (SaaS)

Use Azure AD authentication:

• OAuth 2.0
• Application (client credentials) flow
• Or delegated user flow

On-Premises

Use Basic Authentication or Windows Authentication depending on server configuration.

API Response Format

Both APIs return similar data structures with financial report line information:

Fields Returned

Field	Description
rowNo	Row number identifier
description	Row description
columnValues1-12	Calculated values for up to 12 columns (columnValues1-24 on the ...24 endpoints); empty columns return 0, never null
amountType	Net Amount, Debit, or Credit
bold	Bold formatting flag
italic	Italic formatting flag
newPage	Page break indicator
rowType	Net Change, Beginning Balance, Balance at Date
show	Visibility setting
showOppositeSign	Sign reversal flag
totalingType	Type of row (Posting, Formula, Indicator, etc.)
underline	Underline formatting
doubleUnderline	Double underline formatting
indentation	Row indentation level
totaling	Totaling expression
costCenterTotaling	Cost center filter
costObjectTotaling	Cost object filter
reportFilter	The financial report name used
periodTypeFilter	The period type used (Day, Week, Month, Quarter, Year, Accounting Period)
dim1Filter - dim4Filter	The dimension filters applied
startDateFormula, endCalcDateFormula	CalcDate API only: the date formulas applied
dateFilter	DateFilter API only: a Date FlowFilter field used for filtering; it is not echoed back as a range string

Rows with Show = No are never returned. Rows with a conditional Show value (If Any Column Not Zero, When Positive Balance, When Negative Balance) are returned with the value unevaluated, so apply that logic client-side. Header rows (blank Totaling) return 0 in every column.

Example Response

{
  "value": [
    {
      "rowNo": "10",
      "description": "Revenue",
      "columnValues1": 150000.00,
      "columnValues2": 145000.00,
      "columnValues3": 5000.00,
      "bold": true,
      "rowType": "Net_x0020_Change",
      "totalingType": "Posting_x0020_Accounts"
    },
    {
      "rowNo": "20",
      "description": "Cost of Sales",
      "columnValues1": 90000.00,
      "columnValues2": 87000.00,
      "columnValues3": 3000.00,
      "rowType": "Net_x0020_Change",
      "totalingType": "Posting_x0020_Accounts"
    }
  ]
}

Enum values containing spaces are serialized with x0020 in place of the space (for example Net_x0020_Change, Posting_x0020_Accounts, Accounting_x0020_Period). The show field uses the values Yes, No, If Any Column Not Zero, When Positive Balance and When Negative Balance (encoded the same way).

Filtering Data

Both APIs support OData filtering:

By Report Name

$filter=reportFilter eq 'INCOME-STMT'

By Date Range (DateFilter API)

$filter=reportFilter eq 'INCOME-STMT' and dateFilter ge '2024-01-01' and dateFilter le '2024-01-31'

An explicit dateFilter is honoured exactly. When it is omitted, the period is derived from periodTypeFilter (default Year) around the work date.

By Date Formula (CalcDate API)

$filter=reportFilter eq 'INCOME-STMT' and startDateFormula eq '-CY' and endCalcDateFormula eq 'CY'

By Dimensions

$filter=reportFilter eq 'INCOME-STMT' and dim1Filter eq 'SALES'

Default API Report

Why Set a Default

reportFilter is optional. If no report filter is provided in the API call, the financial report flagged Default API Report is used. If no report is flagged, the call fails with the error "No default API report defined". Setting a default is essential for Power BI connections that may not pass report names.

Setting the Default

1. Search for Financial Reports API List (also reachable as Expose Financial Reports as API on the Advanced Spreadsheets and Reporting menu page)
2. Select your primary report
3. Click Set Default
4. That report is now the default for unfiltered calls (the Default API Report field is also visible on the standard Financial Reports page)

Viewing API Links

The Financial Reports API List page shows:

• CalcDate API Link for each report
• Date Filter API Link for each report

Use these links as templates for your API calls. When the app is unlicensed the link columns are blank.

Use Cases

Power BI Integration

Connect Power BI to the APIs for:

• Real-time financial dashboards
• Automated report refresh
• Combined analysis with other data sources

Excel Integration

Use Power Query in Excel to:

• Pull financial report data
• Create custom analyses
• Build automated workbooks

Custom Applications

Build applications that:

• Display financial data
• Integrate with other systems
• Create custom reports

Getting Started

Quick Start

1. Set a default financial report
2. Note the API endpoint URL
3. Configure authentication
4. Make your first API call
5. Process the returned data

Next Steps

For detailed information, see:

• CalcDate API
• DateFilter API
• Integration Examples

Best Practices

Performance

1. Filter Appropriately: Use filters to limit data
2. Select Needed Fields: Use $select if available
3. Cache When Possible: Don't call API unnecessarily

Security

1. Secure Credentials: Protect API credentials
2. Use HTTPS: Always use encrypted connections
3. Limit Access: Use appropriate authentication scopes

Error Handling

1. Handle Failures: Implement retry logic
2. Log Errors: Track API issues
3. Validate Data: Check returned data integrity

Requirements

License

A valid license is required for API access. Unlicensed calls fail with "No active license for the Advanced Spreadsheets and Reporting".

Permissions

API users need:

• The AFR_Hgd permission set
• Web service access
• Financial report access

Network

Ensure:

• API endpoints are accessible
• Firewall allows connections
• HTTPS is configured

Related Topics

• CalcDate API
• DateFilter API
• Integration Examples