I have an application that uses C#, Entity framework core & MySql database for tracking income & expense and i’m using RDLC for reporting.

I have a transactions table for tracking income and expense like

+----------------+------------------+----------+-----------+-----------------+
| 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      |
+----------------+------------------+----------+-----------+-----------------+

I use 2 tables in the report design filtered by direction of the transaction and I’m able to display them side by side like this.

The code I use for setting the datasource for the report

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);

Using these 2 datasets I get the data splitted to have 2 tables in the report and be able to populate the report.

+----------------+------------------+-----------------+----------+       +----------------+------------------+-----------------+----------+ 
| 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 |   +----------------+------------------+-----------------+----------+ 
+----------------+------------------+-----------------+----------+

But Now, I want the RDLC report to have a running balance column/ like below. I’m not sure if this can be achieved by adding new column or it needs new table with only balance column. In either case I don’t know the which RDLC functions to use to implement this.

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 -  9400
| 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  
+------------------+-----------------+----------+                                                     +------------+