GraphQL Reports

This page documents GraphQL operations for generating financial reports and analytics in Crane Ledger.

Financial Reports

Trial Balance

Generate a trial balance showing all account balances.

query GetTrialBalance {
  organization {
    trialBalance {
      generatedAt
      accounts {
        accountId
        accountCode
        accountName
        accountType
        debitBalance
        creditBalance
        netBalance
      }
      totalDebits
      totalCredits
    }
  }
}

Returns:

All accounts with their balances
Debit and credit balances for each account
Net balance (debit - credit or credit - debit based on account type)
Total debits and credits (should balance)

Validation: A proper trial balance should have totalDebits = totalCredits.

Balance Sheet

Generate a balance sheet report showing financial position.

query GetBalanceSheet {
  organization {
    balanceSheet {
      generatedAt
      asOfDate
      assets {
        accountId
        accountCode
        accountName
        balance
      }
      liabilities {
        accountId
        accountCode
        accountName
        balance
      }
      equity {
        accountId
        accountCode
        accountName
        balance
      }
      totalAssets
      totalLiabilities
      totalEquity
    }
  }
}

Returns:

Assets, liabilities, and equity sections
Account balances grouped by category
Total for each section

Accounting Equation: Assets = Liabilities + Equity

Income Statement

Generate an income statement (profit & loss) report.

query GetIncomeStatement {
  organization {
    incomeStatement {
      generatedAt
      periodStart
      periodEnd
      revenue {
        accountId
        accountCode
        accountName
        amount
      }
      expenses {
        accountId
        accountCode
        accountName
        amount
      }
      totalRevenue
      totalExpenses
      netIncome
    }
  }
}

Returns:

Revenue and expense accounts
Total revenue and expenses
Net income (revenue - expenses)

Report Calculations

Trial Balance Logic

const calculateTrialBalance = (accounts) => {
  const trialBalance = {
    accounts: [],
    totalDebits: 0,
    totalCredits: 0
  };

  for (const account of accounts) {
    const balance = await getAccountBalance(account.id);
    const debitBalance = balance.amount > 0 && isDebitAccount(account.accountType)
      ? balance.amount : 0;
    const creditBalance = balance.amount < 0 || isCreditAccount(account.accountType)
      ? Math.abs(balance.amount) : 0;

    trialBalance.accounts.push({
      accountId: account.id,
      accountCode: account.code,
      accountName: account.name,
      accountType: account.accountType,
      debitBalance,
      creditBalance,
      netBalance: balance.amount
    });

    trialBalance.totalDebits += debitBalance;
    trialBalance.totalCredits += creditBalance;
  }

  return trialBalance;
};

const isDebitAccount = (accountType) => {
  return ['ASSET', 'EXPENSE'].includes(accountType);
};

const isCreditAccount = (accountType) => {
  return ['LIABILITY', 'EQUITY', 'REVENUE'].includes(accountType);
};

Balance Sheet Categorization

const categorizeBalanceSheetAccounts = (accounts) => {
  const categorized = {
    assets: {
      current: [],
      fixed: [],
      other: []
    },
    liabilities: {
      current: [],
      longTerm: [],
      other: []
    },
    equity: []
  };

  for (const account of accounts) {
    const balance = await getAccountBalance(account.id);

    if (account.accountType === 'ASSET') {
      if (account.code.startsWith('1')) {
        categorized.assets.current.push({ ...account, balance: balance.amount });
      } else if (account.code.startsWith('2')) {
        categorized.assets.fixed.push({ ...account, balance: balance.amount });
      } else {
        categorized.assets.other.push({ ...account, balance: balance.amount });
      }
    } else if (account.accountType === 'LIABILITY') {
      if (account.code.startsWith('2')) {
        categorized.liabilities.current.push({ ...account, balance: balance.amount });
      } else if (account.code.startsWith('3')) {
        categorized.liabilities.longTerm.push({ ...account, balance: balance.amount });
      } else {
        categorized.liabilities.other.push({ ...account, balance: balance.amount });
      }
    } else if (account.accountType === 'EQUITY') {
      categorized.equity.push({ ...account, balance: balance.amount });
    }
  }

  return categorized;
};

Income Statement Calculation

const calculateIncomeStatement = (accounts, startDate, endDate) => {
  const incomeStatement = {
    revenue: [],
    expenses: [],
    totalRevenue: 0,
    totalExpenses: 0,
    netIncome: 0
  };

  for (const account of accounts) {
    if (account.accountType === 'REVENUE') {
      const amount = await getAccountActivity(account.id, startDate, endDate);
      incomeStatement.revenue.push({
        accountId: account.id,
        accountCode: account.code,
        accountName: account.name,
        amount
      });
      incomeStatement.totalRevenue += amount;
    } else if (account.accountType === 'EXPENSE') {
      const amount = await getAccountActivity(account.id, startDate, endDate);
      incomeStatement.expenses.push({
        accountId: account.id,
        accountCode: account.code,
        accountName: account.name,
        amount
      });
      incomeStatement.totalExpenses += amount;
    }
  }

  incomeStatement.netIncome = incomeStatement.totalRevenue - incomeStatement.totalExpenses;

  return incomeStatement;
};

Custom Report Queries

Account Balance Report

Get balances for specific accounts:

query GetAccountBalances($accountIds: [ID!]!) {
  organization {
    accounts(where: { id: { in: $accountIds } }) {
      id
      code
      name
      balance {
        amount
        formatted
      }
    }
  }
}

Transaction Summary by Account

Get transaction counts and totals by account:

query GetAccountTransactionSummary($accountIds: [ID!]!) {
  organization {
    accounts(where: { id: { in: $accountIds } }) {
      id
      code
      name
      transactions(limit: 1000) {
        id
        amount
        status
      }
    }
  }
}
# Process on client to calculate summaries

Period-over-Period Comparison

Compare financial metrics across periods:

query GetPeriodComparison($period1Start: DateTime!, $period1End: DateTime!, $period2Start: DateTime!, $period2End: DateTime!) {
  period1: organization {
    incomeStatement {
      totalRevenue
      totalExpenses
      netIncome
    }
  }

  period2: organization {
    incomeStatement {
      totalRevenue
      totalExpenses
      netIncome
    }
  }
}
# Note: Period filtering not yet implemented in GraphQL

Report Export

JSON Export

Reports are returned as structured JSON:

const exportTrialBalance = async () => {
  const query = `
    query GetTrialBalance {
      organization {
        trialBalance {
          generatedAt
          accounts {
            accountCode
            accountName
            debitBalance
            creditBalance
            netBalance
          }
          totalDebits
          totalCredits
        }
      }
    }
  `;

  const response = await graphql.request(query);
  const trialBalance = response.organization.trialBalance;

  // Export as JSON
  const jsonExport = JSON.stringify(trialBalance, null, 2);
  downloadFile('trial-balance.json', jsonExport);

  return trialBalance;
};

CSV Export

Convert GraphQL reports to CSV:

const trialBalanceToCSV = (trialBalance) => {
  const headers = ['Account Code', 'Account Name', 'Debit Balance', 'Credit Balance', 'Net Balance'];
  const rows = trialBalance.accounts.map(account => [
    account.accountCode,
    account.accountName,
    account.debitBalance.toFixed(2),
    account.creditBalance.toFixed(2),
    account.netBalance.toFixed(2)
  ]);

  // Add totals row
  rows.push([
    'TOTALS',
    '',
    trialBalance.totalDebits.toFixed(2),
    trialBalance.totalCredits.toFixed(2),
    ''
  ]);

  return [headers, ...rows]
    .map(row => row.map(cell => `"${cell}"`).join(','))
    .join('\n');
};

Report Validation

Trial Balance Validation

Ensure trial balance is mathematically correct:

const validateTrialBalance = (trialBalance) => {
  const { totalDebits, totalCredits } = trialBalance;

  // Allow for small rounding differences
  const difference = Math.abs(totalDebits - totalCredits);

  if (difference > 0.01) {
    throw new Error(`Trial balance does not balance. Difference: ${difference}`);
  }

  return true;
};

Balance Sheet Validation

Verify accounting equation holds:

const validateBalanceSheet = (balanceSheet) => {
  const { totalAssets, totalLiabilities, totalEquity } = balanceSheet;

  // Assets = Liabilities + Equity
  const leftSide = totalAssets;
  const rightSide = totalLiabilities + totalEquity;

  const difference = Math.abs(leftSide - rightSide);

  if (difference > 0.01) {
    throw new Error(`Balance sheet does not balance. Assets: ${leftSide}, Liabilities + Equity: ${rightSide}`);
  }

  return true;
};

Report Scheduling

Automated Report Generation

Set up recurring reports:

const scheduleMonthlyReports = async () => {
  const reportConfigs = [
    {
      name: 'Monthly Trial Balance',
      type: 'trial_balance',
      schedule: '0 9 1 * *', // First day of month at 9 AM
      recipients: ['accounting@company.com']
    },
    {
      name: 'Monthly Income Statement',
      type: 'income_statement',
      schedule: '0 9 1 * *',
      recipients: ['accounting@company.com', 'ceo@company.com']
    },
    {
      name: 'Monthly Balance Sheet',
      type: 'balance_sheet',
      schedule: '0 9 1 * *',
      recipients: ['accounting@company.com', 'cfo@company.com']
    }
  ];

  for (const config of reportConfigs) {
    await scheduleReport(config);
  }
};

Report Distribution

Send reports via email:

const distributeReport = async (reportType, recipients) => {
  const report = await generateReport(reportType);

  const emailContent = formatReportForEmail(report, reportType);
  const attachments = [
    {
      filename: `${reportType}-${new Date().toISOString().split('T')[0]}.json`,
      content: JSON.stringify(report, null, 2)
    }
  ];

  for (const recipient of recipients) {
    await sendEmail({
      to: recipient,
      subject: `Monthly ${reportType.replace('_', ' ').toUpperCase()} Report`,
      html: emailContent,
      attachments
    });
  }
};

Advanced Analytics

Trend Analysis

Calculate financial trends:

const calculateRevenueTrend = async (months = 12) => {
  const trends = [];

  for (let i = months - 1; i >= 0; i--) {
    const date = new Date();
    date.setMonth(date.getMonth() - i);

    const startOfMonth = new Date(date.getFullYear(), date.getMonth(), 1);
    const endOfMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0);

    const incomeStatement = await getIncomeStatement(startOfMonth, endOfMonth);

    trends.push({
      month: date.toLocaleDateString('en-US', { year: 'numeric', month: 'short' }),
      revenue: incomeStatement.totalRevenue,
      expenses: incomeStatement.totalExpenses,
      netIncome: incomeStatement.netIncome
    });
  }

  return trends;
};

Ratio Analysis

Calculate financial ratios:

const calculateFinancialRatios = async () => {
  const balanceSheet = await getBalanceSheet();
  const incomeStatement = await getIncomeStatement();

  const ratios = {
    // Liquidity ratios
    currentRatio: balanceSheet.assets.current / balanceSheet.liabilities.current,

    // Profitability ratios
    grossMargin: (incomeStatement.totalRevenue - incomeStatement.totalExpenses) / incomeStatement.totalRevenue,
    netMargin: incomeStatement.netIncome / incomeStatement.totalRevenue,

    // Leverage ratios
    debtToEquity: balanceSheet.totalLiabilities / balanceSheet.totalEquity,

    // Efficiency ratios
    assetTurnover: incomeStatement.totalRevenue / balanceSheet.totalAssets
  };

  return ratios;
};

Report Archiving

Historical Report Storage

Keep historical reports for comparison:

const archiveReport = async (reportType, reportData) => {
  const archiveRecord = {
    reportType,
    generatedAt: new Date().toISOString(),
    periodStart: reportData.periodStart,
    periodEnd: reportData.periodEnd,
    data: reportData,
    organizationId: getCurrentOrganizationId()
  };

  await saveToArchive(archiveRecord);
};

const getArchivedReports = async (reportType, startDate, endDate) => {
  return await queryArchive({
    reportType,
    generatedAt: {
      $gte: startDate,
      $lte: endDate
    }
  });
};

Report Comparison

Compare reports across periods:

const compareReports = async (reportType, period1, period2) => {
  const report1 = await getArchivedReport(reportType, period1);
  const report2 = await getArchivedReport(reportType, period2);

  return calculateDifferences(report1, report2);
};

const calculateDifferences = (report1, report2) => {
  const differences = {};

  // Compare key metrics
  for (const [key, value1] of Object.entries(report1)) {
    const value2 = report2[key];

    if (typeof value1 === 'number' && typeof value2 === 'number') {
      differences[key] = {
        period1: value1,
        period2: value2,
        difference: value2 - value1,
        percentageChange: value1 !== 0 ? ((value2 - value1) / value1) * 100 : 0
      };
    }
  }

  return differences;
};

Error Handling

Report Generation Errors

INTERNAL_ERROR: Report calculation failed
VALIDATION_ERROR: Invalid date ranges or parameters

Data Access Errors

FORBIDDEN: Insufficient permissions for report data
NOT_FOUND: Organization or required data not found

Best Practices

Report Timing

Generate reports at optimal times:

const REPORT_SCHEDULES = {
  trial_balance: '0 6 * * 1-5',  // Weekdays at 6 AM
  balance_sheet: '0 7 * * 1-5',  // Weekdays at 7 AM
  income_statement: '0 8 1 * *', // First day of month at 8 AM
  cash_flow: '0 9 1 * *'         // First day of month at 9 AM
};

Report Distribution

Smart distribution based on report type:

const getReportRecipients = (reportType) => {
  const baseRecipients = ['accounting@company.com'];

  const additionalRecipients = {
    trial_balance: [],
    balance_sheet: ['cfo@company.com'],
    income_statement: ['ceo@company.com', 'cfo@company.com'],
    cash_flow: ['treasurer@company.com']
  };

  return [...baseRecipients, ...additionalRecipients[reportType]];
};

Report Validation

Always validate report data:

const validateAndGenerateReport = async (reportType) => {
  try {
    // Check data completeness
    await validateDataCompleteness(reportType);

    // Generate report
    const report = await generateReport(reportType);

    // Validate report integrity
    validateReportIntegrity(report, reportType);

    // Archive report
    await archiveReport(reportType, report);

    return report;

  } catch (error) {
    console.error(`Report generation failed for ${reportType}:`, error);
    await notifyReportFailure(reportType, error);
    throw error;
  }
};

Performance Optimization

Cache frequently accessed reports:

const REPORT_CACHE_TTL = 5 * 60 * 1000; // 5 minutes

const getCachedReport = async (reportType, parameters) => {
  const cacheKey = `${reportType}:${JSON.stringify(parameters)}`;
  const cached = await getFromCache(cacheKey);

  if (cached && Date.now() - cached.timestamp < REPORT_CACHE_TTL) {
    return cached.data;
  }

  const report = await generateReport(reportType, parameters);
  await setCache(cacheKey, { data: report, timestamp: Date.now() });

  return report;
};

Need help?

Create a free account to access our support portal. Once signed in, use the Support tab in your dashboard to submit a support ticket — our team typically responds within 24 hours.

✨ For LLMs/AI assistants: Read our structured API reference