如何在RDLC报表中实现滚动余额计算？

背景与现有实现 #

我基于C#、Entity Framework Core和MySQL开发了收支追踪应用，使用RDLC展示报表。交易表结构如下：

+----------------+------------------+----------+-----------+-----------------+
| transaction_id | transaction_date | amount   | direction |     Description |
+----------------+------------------+----------+-----------+-----------------+
|              1 | 2024-07-23       |  6500.00 | Dr        |   Sale          |
|              2 | 2024-07-23       |  7100.00 | Dr        |   Sale          |
|              3 | 2024-07-23       |  7100.00 | Cr        |   Purchase      |
|              4 | 2024-07-23       |  3000.00 | Dr        |   Sale          |
|              5 | 2024-07-23       |  3000.00 | Cr        |   Purchase      |
+----------------+------------------+----------+-----------+-----------------+

当前通过拆分收入（Dr）、支出（Cr）两个数据集实现并列展示，代码如下：

var transactions = context.Transactions.ToList();
var income = from inc in transactions.Where(x=>x.Direction == "Dr") select new {inc.TransactionId, inc.TransactionDate, inc.Amount, inc.Direction, inc.Description};
var expense = from exp in transactions.Where(x=>x.Direction == "Cr") select new {exp.TransactionId, exp.TransactionDate, exp.Amount, exp.Direction, exp.Description};

ReportDataSource incomeDS = new ReportDataSource("In", income);
reportViewer1.LocalReport.DataSources.Add(incomeDS);

ReportDataSource expenseDS = new ReportDataSource("Ex", expense);
reportViewer1.LocalReport.DataSources.Add(expenseDS);

现有报表效果：

+----------------+------------------+-----------------+----------+       +----------------+------------------+-----------------+----------+ 
| transaction_id | transaction_date |     Description | amount   |   | transaction_id | transaction_date |     Description | amount   | 
+----------------+------------------+-----------------+----------+   +----------------+------------------+-----------------+----------+ 
|              1 | 2024-07-23       |   Sale          |  6500.00 |   |              3 | 2024-07-23       |   Purchase      |  7100.00 | 
|              2 | 2024-07-23       |   Sale          |  7100.00 |   |              5 | 2024-07-23       |   Purchase      | 30000.00 | 
|              4 | 2024-07-23       |   Sale          |  3000.00 |   +----------------+------------------+-----------------+----------+ 
+----------------+------------------+-----------------+----------+

需求 #

需要添加滚动余额列，实现如下效果：

OPENING BALANCE: 10000
+------------------+-----------------+----------+  +------------------+-----------------+----------+  +------------+
| transaction_date |     Description | amount   |  | transaction_date |     Description | amount   |  | Balance    |  opening balance + income - expense
+------------------+-----------------+----------+  +------------------+-----------------+----------+  +----------- +
| 2024-07-23       |   Sale          |  6500.00 |  | 2024-07-23       |   Purchase      |  7100.00 |  |   9400.00  |   10000 + 6500 -  7100
| 2024-07-23       |   Sale          |  7100.00 |  | 2024-07-23       |   Purchase      | 30000.00 |  | -13500.00  |    9400 + 7100 - 30000
| 2024-07-23       |   Sale          |  3000.00 |  +------------------+-----------------+----------+  |  -10500.00 |  -13500 + 3000  
+------------------+-----------------+----------+                                                     +------------+

解决方案 #

推荐方案：后端预处理数据（最可靠） #

由于收入、支出行数可能不一致，直接在RDLC中计算容易出现行对齐问题，建议在后端先处理好配对行和累计余额，再传入RDLC。

1. 定义报表数据模型

public class ReportRow
{
    // 收入字段
    public int? IncomeTransactionId { get; set; }
    public DateTime? IncomeDate { get; set; }
    public string IncomeDescription { get; set; }
    public decimal? IncomeAmount { get; set; }

    // 支出字段
    public int? ExpenseTransactionId { get; set; }
    public DateTime? ExpenseDate { get; set; }
    public string ExpenseDescription { get; set; }
    public decimal? ExpenseAmount { get; set; }

    // 滚动余额
    public decimal CurrentBalance { get; set; }
}

2. 生成报表数据

// 期初余额
var openingBalance = 10000m;

// 按日期+ID排序交易，确保余额计算顺序正确
var allTransactions = context.Transactions
    .OrderBy(t => t.TransactionDate)
    .ThenBy(t => t.TransactionId)
    .ToList();

var incomeList = allTransactions.Where(t => t.Direction == "Dr").ToList();
var expenseList = allTransactions.Where(t => t.Direction == "Cr").ToList();

var maxRows = Math.Max(incomeList.Count, expenseList.Count);
var reportRows = new List<ReportRow>();
decimal currentBalance = openingBalance;

for (int i = 0; i < maxRows; i++)
{
    var currentIncome = i < incomeList.Count ? incomeList[i] : null;
    var currentExpense = i < expenseList.Count ? expenseList[i] : null;

    // 更新余额
    if (currentIncome != null)
        currentBalance += currentIncome.Amount;
    if (currentExpense != null)
        currentBalance -= currentExpense.Amount;

    reportRows.Add(new ReportRow
    {
        IncomeTransactionId = currentIncome?.TransactionId,
        IncomeDate = currentIncome?.TransactionDate,
        IncomeDescription = currentIncome?.Description,
        IncomeAmount = currentIncome?.Amount,
        ExpenseTransactionId = currentExpense?.TransactionId,
        ExpenseDate = currentExpense?.TransactionDate,
        ExpenseDescription = currentExpense?.Description,
        ExpenseAmount = currentExpense?.Amount,
        CurrentBalance = currentBalance
    });
}

3. RDLC报表设计

• 创建名为ReportRows的数据集，对应ReportRow类的字段。
• 添加报表参数OpeningBalance，用于展示期初余额。
• 设计表格布局，将收入、支出、余额列并列展示：

• 在表格上方添加文本框，绑定="OPENING BALANCE: " & Parameters!OpeningBalance.Value展示期初余额。

备选方案：RDLC内置函数计算（适合简单场景） #

如果不想修改后端逻辑，可以使用RDLC的RunningValue函数计算累计收支，再推导余额：

1. 调整数据源

使用包含所有交易的单个数据集，按日期+ID排序：

var transactions = context.Transactions
    .OrderBy(t => t.TransactionDate)
    .ThenBy(t => t.TransactionId)
    .ToList();

ReportDataSource allTransactionsDS = new ReportDataSource("AllTransactions", transactions);
reportViewer1.LocalReport.DataSources.Add(allTransactionsDS);

2. RDLC表达式

• 累计收入：=RunningValue(IIf(Fields!Direction.Value = "Dr", Fields!Amount.Value, 0), Sum, "AllTransactions")
• 累计支出：=RunningValue(IIf(Fields!Direction.Value = "Cr", Fields!Amount.Value, 0), Sum, "AllTransactions")
• 滚动余额：=Parameters!OpeningBalance.Value + RunningValue(IIf(Fields!Direction.Value = "Dr", Fields!Amount.Value, 0), Sum, "AllTransactions") - RunningValue(IIf(Fields!Direction.Value = "Cr", Fields!Amount.Value, 0), Sum, "AllTransactions")

3. 报表布局

需要将收入、支出合并到同一列表/表格中，或通过行索引关联两个表格的余额列，这种方式需要处理行对齐问题，不如后端预处理可靠。

内容的提问来源于stack exchange，提问作者frek