Integration Examples

This guide provides practical examples of integrating Advanced Spreadsheets and Reporting APIs with various external tools and applications.

Power BI Integration

Setting Up the Connection

Step 1: Get API URL

Open Business Central
Search for Financial Reports API List
Find your report
Copy the CalcDate API Link or Date Filter API Link
For column definitions with more than 12 columns, change the entity set in the link to calcDateLines24 or dateFilterLines24 to receive columnValues1-24

Step 2: Create Power BI Data Source

Open Power BI Desktop
Get Data → OData Feed
Paste the API URL
Configure authentication

Step 3: Configure Authentication

For Business Central Online:

Select Organizational account
Sign in with your BC credentials
Connect

Power Query Examples

Basic Report Query

let
    Source = OData.Feed(
        "https://api.businesscentral.dynamics.com/v2.0/{tenant}/{environment}/api/hougaard/financialReport/v2.0/dateFilterLines?company=CRONUS%20International&$filter=reportFilter eq 'INCOME-STMT' and dateFilter ge '2024-01-01' and dateFilter le '2024-12-31'",
        null,
        [Implementation="2.0"]
    )
in
    Source

Parameterized Query

First, create parameters:

pStartDate (Date)
pEndDate (Date)
pReportName (Text)

Then use them:

let
    StartDateText = Date.ToText(pStartDate, "yyyy-MM-dd"),
    EndDateText = Date.ToText(pEndDate, "yyyy-MM-dd"),
    BaseUrl = "https://api.businesscentral.dynamics.com/v2.0/{tenant}/{environment}/api/hougaard/financialReport/v2.0/dateFilterLines",
    FilterString = "?company=CRONUS%20International&$filter=reportFilter eq '" & pReportName & "' and dateFilter ge '" & StartDateText & "' and dateFilter le '" & EndDateText & "'",
    Source = OData.Feed(BaseUrl & FilterString, null, [Implementation="2.0"])
in
    Source

Multiple Reports in One Dataset

let
    GetReport = (ReportName as text) => 
        let
            Source = OData.Feed(
                "...?$filter=reportFilter eq '" & ReportName & "'...",
                null,
                [Implementation="2.0"]
            ),
            AddedColumn = Table.AddColumn(Source, "ReportSource", each ReportName)
        in
            AddedColumn,
    
    IncomeStatement = GetReport("INCOME-STMT"),
    BalanceSheet = GetReport("BALANCE-SHEET"),
    Combined = Table.Combine({IncomeStatement, BalanceSheet})
in
    Combined

Building the Dashboard

Visual Recommendations

Matrix: For traditional financial statement layout
Card: For key metrics
Line Chart: For trends
Table: For detailed data

Tips

Use indentation field to create hierarchy
Use bold/underline for conditional formatting
Rows with Show = No are already excluded server-side; evaluate the conditional show values (If Any Column Not Zero, When Positive Balance, When Negative Balance) in your visuals if you want to hide those rows

Excel Integration

Using Power Query

Step 1: Create Query

Data → Get Data → From Other Sources → OData Feed
Enter the API URL
Configure authentication
Load to worksheet or data model

Step 2: Refresh Data

Manual: Data → Refresh All
Automatic: Set up refresh schedule (Power Query in Excel Online)

Example: Monthly Financial Report

let
    // Configuration
    Company = "CRONUS%20International",
    ReportName = "INCOME-STMT",
    
    // Get first and last day of previous month
    Today = DateTime.LocalNow(),
    LastMonth = Date.AddMonths(DateTime.Date(Today), -1),
    StartDate = Date.StartOfMonth(LastMonth),
    EndDate = Date.EndOfMonth(LastMonth),
    
    // Format dates
    StartDateText = Date.ToText(StartDate, "yyyy-MM-dd"),
    EndDateText = Date.ToText(EndDate, "yyyy-MM-dd"),
    
    // Build URL
    BaseUrl = "https://api.businesscentral.dynamics.com/v2.0/{tenant}/{environment}/api/hougaard/financialReport/v2.0/dateFilterLines",
    Filter = "?company=" & Company & "&$filter=reportFilter eq '" & ReportName & "' and dateFilter ge '" & StartDateText & "' and dateFilter le '" & EndDateText & "'",
    
    Source = OData.Feed(BaseUrl & Filter, null, [Implementation="2.0"])
in
    Source

Creating Financial Statements

Import data to Excel
Create a formatted template
Reference data cells
Apply formatting based on bold/underline fields

Custom Application Integration

C# / .NET Example

using System;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;

public class AFRClient
{
    private readonly HttpClient _client;
    private readonly string _baseUrl;

    public AFRClient(string tenantId, string environment, string accessToken)
    {
        _client = new HttpClient();
        _client.DefaultRequestHeaders.Authorization = 
            new AuthenticationHeaderValue("Bearer", accessToken);
        _baseUrl = $"https://api.businesscentral.dynamics.com/v2.0/{tenantId}/{environment}/api/hougaard/financialReport/v2.0/";
    }

    public async Task<string> GetFinancialReport(string reportName, DateTime startDate, DateTime endDate, string company)
    {
        var filter = $"?company={Uri.EscapeDataString(company)}" +
                     $"&$filter=reportFilter eq '{reportName}'" +
                     $" and dateFilter ge '{startDate:yyyy-MM-dd}'" +
                     $" and dateFilter le '{endDate:yyyy-MM-dd}'";
        
        var response = await _client.GetAsync(_baseUrl + "dateFilterLines" + filter);
        response.EnsureSuccessStatusCode();
        
        return await response.Content.ReadAsStringAsync();
    }
}

Python Example

import requests
from datetime import date

class AFRClient:
    def __init__(self, tenant_id, environment, access_token):
        self.base_url = f"https://api.businesscentral.dynamics.com/v2.0/{tenant_id}/{environment}/api/hougaard/financialReport/v2.0/"
        self.headers = {"Authorization": f"Bearer {access_token}"}
    
    def get_financial_report(self, report_name, start_date, end_date, company):
        url = f"{self.base_url}dateFilterLines"
        params = {
            "company": company,
            "$filter": f"reportFilter eq '{report_name}' and dateFilter ge '{start_date}' and dateFilter le '{end_date}'"
        }
        response = requests.get(url, headers=self.headers, params=params)
        response.raise_for_status()
        return response.json()

# Usage
client = AFRClient("tenant-id", "production", "access-token")
data = client.get_financial_report(
    "INCOME-STMT",
    "2024-01-01",
    "2024-12-31",
    "CRONUS International"
)

JavaScript/Node.js Example

const axios = require('axios');

class AFRClient {
    constructor(tenantId, environment, accessToken) {
        this.baseUrl = `https://api.businesscentral.dynamics.com/v2.0/${tenantId}/${environment}/api/hougaard/financialReport/v2.0/`;
        this.headers = { 'Authorization': `Bearer ${accessToken}` };
    }

    async getFinancialReport(reportName, startDate, endDate, company) {
        const filter = `reportFilter eq '${reportName}' and dateFilter ge '${startDate}' and dateFilter le '${endDate}'`;
        const url = `${this.baseUrl}dateFilterLines`;
        
        const response = await axios.get(url, {
            headers: this.headers,
            params: {
                'company': company,
                '$filter': filter
            }
        });
        
        return response.data;
    }
}

// Usage
const client = new AFRClient('tenant-id', 'production', 'access-token');
const data = await client.getFinancialReport(
    'INCOME-STMT',
    '2024-01-01',
    '2024-12-31',
    'CRONUS International'
);

Authentication

Getting Access Tokens (OAuth 2.0)

App Registration

Go to Azure Portal → Azure Active Directory
App registrations → New registration
Configure redirect URIs
Add API permissions for Business Central

Required Permissions

Dynamics 365 Business Central
- API.ReadWrite.All (or more restrictive)

Token Request (Client Credentials)

POST https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token
Content-Type: application/x-www-form-urlencoded

client_id={client_id}
&scope=https://api.businesscentral.dynamics.com/.default
&client_secret={client_secret}
&grant_type=client_credentials

Best Practices

Error Handling

try:
    data = client.get_financial_report(...)
except requests.exceptions.HTTPError as e:
    if e.response.status_code == 401:
        # Refresh token and retry
        pass
    elif e.response.status_code == 404:
        # Report not found
        pass
    else:
        raise

Caching

import functools
from datetime import timedelta

@functools.lru_cache(maxsize=100)
def get_cached_report(report_name, start_date, end_date, company):
    return client.get_financial_report(report_name, start_date, end_date, company)

Rate Limiting

Implement retry logic with exponential backoff:

import time

def api_call_with_retry(func, max_retries=3):
    for attempt in range(max_retries):
        try:
            return func()
        except Exception as e:
            if attempt == max_retries - 1:
                raise
            time.sleep(2 ** attempt)

Troubleshooting

Common Issues

Issue	Cause	Solution
401 Unauthorized	Token expired	Refresh token
403 Forbidden	Insufficient permissions	Check API permissions
Empty response	No data for filters	Verify report/date range
Timeout	Large data set	Add more filters

Debugging Tips

Test URL in Browser: Try API URL in browser first
Check Filter Syntax: Verify OData filter format
Review Permissions: Ensure the user has the AFR_Hgd permission set
Check License: Verify the license is active (unlicensed calls return "No active license for the Advanced Spreadsheets and Reporting")