Keeping previous balance on sub-summary report

[jkluchnik]

Hello all. I have an issue for which I have been using the same solution for a long time but was thinking that perhaps I am missing something and one of you brilliant types may have a better way to solve this problem.

We have several tables that keep numerical data that needs a running balance, with a total at the end. However, it is also necessary sometimes to pull a report which is a sub-set of the data in the total, say for a particular date range. The problem is if you restrict the data to a sub-set of the data, the balances will only be for the sub-set of the data.

I have been solving this up to now by using a process which finds a subset of the data up to one day prior to the desired sub-set, getting the balance of that data, creating a new "opening balance" record with that data, and then running the report including this opening balance record. This works. However, there are always drawbacks on using "temporary" records, such as if the system fails in the middle of one of these reports, you now have data that does not belong there.

Here is the original data set.

Now if you do a sub-summary report sorted by account and date, you get this:

So far, so good. However, when you filter the data by the "filter dates" shown in the original dataset, you end up with this result, which is wrong:

By running a process that gets the previous balances of the accounts, getting their balances "pre-filter" date, and creating records, one can active the desired result:

However, I am hoping there is a more elegant solution?

I have attached a sample file with the data in the images.

Thanks all!

Balances.fp7.zip

[Fitch]

How about keeping the running balance as a stored field in the account table?

[jkluchnik]

Unfortunately that is not a great solution for us. Much of our data is uploaded, or is entered automatically by other processes. Data may be entered into this table "out of order" so it means that every time a record comes in, it would have to reset that field throughout the entire table...

Thanks for the suggestion Fitch!

[dwdata]

Yes - I run into this quite often. Unfortunately, I do not think there is a way around producing Balances Report without some sort of batch procedure at the time of the report. It usually requires storing opening balance in a table (or somewhere) and even creating temp records.

Please check out this little example file I whipped up. Hopefully it will get you going in the right direction.

Balances_dwdc.zip

[jkluchnik]

Hi Don,

Thanks for your example and your comments. You may have missed that I wrote I actually have solved this as well, years ago and do it with a process which creates opening balance temp records, and then deletes them. I was just hoping to hear that someone much smarter than me has an obvious simple way to do this that I have been overlooking for years (this happens to me a lot on this forum!).

Unfortunately, it seems that in this case I need to stay the course.

[dwdata]

That's cool. I agree, it would be nice if FMP handled this type of stuff a much better. I have another idea that I will experiment with. If I stumble upon something I will share it with you.