Ideas to replace Stored Procedure in Cash Flow report

Question

We have a Cash flow report which is basically in this structure:

Date |Credit|Debit|balance|
09/29| 20   | 10  | 10    |
09/30| 0    | 10  | 0     |

The main problem is the balance, and as we are using a DataSet for the Data, it's kinda hard to calculate the balance on the DataSet, because we always need the balance from the previous day.

Also this data comes from several tables and it's been hard to maintain this procedure, because the database metadata is changing frequently.

Anyone could give me some possible different solutions? for the problem?

This report is being displayed on a DataGrid.

Answer 1

On the code side of things, you've got two relatively easy options, but they both involve iterating through the dataset.

option 1: For loop prior to databinding. For each row in the datatable, add/subtract the credit/debits to the previous row's balance and assign it to the appropriate cell of your datatable.

Option 2: calculate during databinding. First you'd have a global variable that you could hold your value in. Set it to zero right before the databind. For each item or altitem, add/subtract the credit/debits to the global variable and assign it to the appropriate cell of your datagrid.

Answer 2

This may be too big a change or off the mark for you, but a cash flow report indicates to me that you are probably maintaining, either formally or informally, a general ledger arrangement of some sort. If you are, then maybe I am naive about this but I think you should maintain your general ledger detail as a single table that has a bare minimum number of columns like ID, date, account, source and amount.

All of the data that comes from different tables suggests that there several different kinds of events that are affecting your cash. To me, representing these different kinds of events in their own tables (like accounts receivable or accounts payable or inventory or whatever) makes sense, but the trick is to not have any monetary columns in those other tables. Instead, have them refer to the row in the general ledger detail where that data is recorded. If you enforce this, then the cash flow would always work the same regardless of changes to the other tables.

The balance forward issue still has to be addressed and you have to take into account the number of transactions involved and the responsiveness required of the system but at least you could make a decision about how to handle it one time and not have to make changes as the other parts of your system evolve.

Answer 3

Be aware that this normally means great raise of network traffic, which can reduce the performance of the application as a whole (since you'd have to fetch all this data to process on the client).

An alternative approach is creating a ( in what implementation you like) middle layer application which you can send a request and do this processing on the database server or in a separate network segment.

If there is a SP to do this calculation, normally is because it touch a lot of data and the objective is to avoid this circulating in the network.