It seems that the report also includes future-dated transactions (regardless of reconciliation status) outside of the report date range.

====
[2020.05.24 19:44:27] <CDB-Man_> actually, another thing i noticed is that the rport includes future dated transactions as well
[2020.05.24 19:44:52] <CDB-Man_> a transaction dated in 2021 is being picked up even though my date range is from 2015 until today

Aha you're referring to the subtotal table.

The reason for the bug is obvious to me (because I wrote it).

Premise:
every transaction report, or offshoot of transaction report, will define a split->line to define the data extraction strategy. This will have: monetary object, heading, enable-subtotal?, enable-dual-columns? (Dr/Cr), enable-sign-reverses? etc. See definition of default-calculated-cells at https://github.com/Gnucash/gnucash/blob/40b504ec89b7f0fa56e136559308a7f779322859/gnucash/report/report-system/trep-engine.scm#L1265 onwards.

Therefore a report may define multiple extracted amounts: eg. One for Amount, Balance, Cleared Balance, Reconciled Balance etc, and Two for Amount Dr and Cr.

The subtotal-table accumulator will need to know which column contains most pertinent data, and in practice there is no method to find the best split->best_amount. My approach was to take the first amount received in the calculated-cells. In most other reports this is fine because subtotal-table is disabled when first cell isn't an appropriate amount, but I forgot to disable it for the reconcile-report options gneerator: https://github.com/Gnucash/gnucash/blob/40b504ec89b7f0fa56e136559308a7f779322859/gnucash/report/standard-reports/reconcile-report.scm#L33

So I think the best (and easiest) approach is to disable subtotal-table in the reconcile-report.

If we were to try enable it, we'd need to modify all other reports to allow the calculated-cells to output a suitable subtotal-table amount.

See current strategy to take the first column amount only. Works fine for most reports so far.

https://github.com/Gnucash/gnucash/blob/40b504ec89b7f0fa56e136559308a7f779322859/gnucash/report/report-system/trep-engine.scm#L1532

Forgive my ignorance for a moment, because I don't claim to fully understand (if any at all) the details of Scheme.  But can the subtotal table accumulate the amount column which ought to have both DR and CR amounts?  Or, if you're saying that the amount column doesn't actually do this, can we accumulate both the DR and CR columns then add the two together?  The subtotal table is very useful, and losing it would be unfortunate.  In my case, the table allows me to track prepaid amounts subtotaled by year crossed by description.

(In reply to CDB-Man from comment #4)
> Forgive my ignorance for a moment, because I don't claim to fully understand
> (if any at all) the details of Scheme.  But can the subtotal table
> accumulate the amount column which ought to have both DR and CR amounts? 
> Or, if you're saying that the amount column doesn't actually do this, can we
> accumulate both the DR and CR columns then add the two together?  The
> subtotal table is very useful, and losing it would be unfortunate.  In my
> case, the table allows me to track prepaid amounts subtotaled by year
> crossed by description.

Again it IS possible but requires widespread changes across many reports, and will cause breakage in the 3.x or 4.x series.


(In reply to CDB-Man from comment #1)
> It seems that the report also includes future-dated transactions (regardless
> of reconciliation status) outside of the report date range.
> 
> ====
> [2020.05.24 19:44:27] <CDB-Man_> actually, another thing i noticed is that
> the rport includes future dated transactions as well
> [2020.05.24 19:44:52] <CDB-Man_> a transaction dated in 2021 is being picked
> up even though my date range is from 2015 until today

The Reconcile-Report will filter Reconciled Dates rather than Posting Dates. It will also include unreconciled splits.

Not sure whether this is legit, but I figured a reconcile report aims to be useful during reconciliation and should include all unreconciled splits.

> Again it IS possible but requires widespread changes across many reports, and will cause breakage in the 3.x or 4.x series.

Well, that is quite unfortunate!!

At this point I am thinking out loud, so if i don't make any sense... and/or I am beating a dead horse that you have already said is not easily feasible... please disregard me...

When you say " a report may define multiple extracted amounts", is it possible to extract both the DR and Cr amounts only in this report's Scheme, without modifying anything else in the underlying?

The fact that the Reconciliation Report can already pull both the "Funds In" and "Funds Out" amounts and display them in the transaction table... means that it has access to them.  All it needs to do is net them together.  Using my attached screenshot as an example, for the "Ken" item, net DR 5,491.18 against CR $3,314.01 to report a net amount of $2,177.17 in the subtotal table.  (Again, I don't at all claim that this is easy or not easy to do in Scheme; it just "appears" easy to a human reading the report.)

=====
=====
> The Reconcile-Report will filter Reconciled Dates rather than Posting Dates. It will also include unreconciled splits.

Ah, this is definitely not clear in the report options.  The date selector follows the same layout as all other reports, and all other reports use the date selector for posting date.  If that's the case, it should clearly be labelled reconciliation date is what is being filtered.  Though that being said...

> Not sure whether this is legit, but I figured a reconcile report aims to be useful during reconciliation and should include all unreconciled splits.

1. Generally, the primary consideration when reconciling is not "when the reconciliation was done", but rather "to what date is the balance being reconciled against".
2. For example, let's say my credit card statement date is the 20th of each month, for example April 20th.  It's not mailed to me until May 5th, and therefore it is on May 5th that I perform the reconciliation.  As such, the reconciliation date on all the transactions is May 5th, but this is not a particularly useful piece of information.
3. Instead, what is much more useful is that I have reconciled the credit card balance in GNUCash, against the balance from an external source, the credit card statement, to the statement date of April 20th.  Therefore, I now know that my GNUCash balance is accurate up to April 20th (I am not missing any credit card transactions / double booked any transactions).

For these reasons, I had expected the date filters to operate on posting date.  This is also compounded by the fact that all other reports also operate on posting date, and there was nothing to indicate otherwise.

TL;DR: the suggested action is to use the date range to define posting date.
As an added bonus, depending on feasibility, a 2nd date range input could be added for reconciliation date, but as illustrated, this is probably an order of magnitude less useful.  I'm sure someone will find a use for it, but definitely not in any typical sense that I can think of as a CPA.

(In reply to CDB-Man from comment #6)
> > Again it IS possible but requires widespread changes across many reports, and will cause breakage in the 3.x or 4.x series.
> 
> Well, that is quite unfortunate!!
> 
> At this point I am thinking out loud, so if i don't make any sense... and/or
> I am beating a dead horse that you have already said is not easily
> feasible... please disregard me...
> 
> When you say " a report may define multiple extracted amounts", is it
> possible to extract both the DR and Cr amounts only in this report's Scheme,
> without modifying anything else in the underlying?

I'll need to think how it can work. I know there are other offshoots in use and it wouldn't be nice to break them. So let's consider this bug "pending" and hopefully will be workable.

> > The Reconcile-Report will filter Reconciled Dates rather than Posting Dates. It will also include unreconciled splits.
> 
> Ah, this is definitely not clear in the report options.  The date selector
> follows the same layout as all other reports, and all other reports use the
> date selector for posting date.  If that's the case, it should clearly be
> labelled reconciliation date is what is being filtered.  Though that being
> said...

FWIW I agree with you - the filtering by reconciled rather than posting date was bug 796614 and was commited as https://github.com/Gnucash/gnucash/commit/3af9acec and documented in the report blurb. I wonder whether 796614 should be reverted.

> 1. Generally, the primary consideration when reconciling is not "when the
> reconciliation was done", but rather "to what date is the balance being
> reconciled against".
> 2. For example, let's say my credit card statement date is the 20th of each
> month, for example April 20th.  It's not mailed to me until May 5th, and
> therefore it is on May 5th that I perform the reconciliation.  As such, the
> reconciliation date on all the transactions is May 5th, but this is not a
> particularly useful piece of information.

From my understanding I'd expect that your reconciled date *should* be 20th April? What do you think of bug 797640 and the long thread starting at https://lists.gnucash.org/pipermail/gnucash-devel/2020-April/044802.html

> 3. Instead, what is much more useful is that I have reconciled the credit
> card balance in GNUCash, against the balance from an external source, the
> credit card statement, to the statement date of April 20th.  Therefore, I
> now know that my GNUCash balance is accurate up to April 20th (I am not
> missing any credit card transactions / double booked any transactions).
> 
> For these reasons, I had expected the date filters to operate on posting
> date.  This is also compounded by the fact that all other reports also
> operate on posting date, and there was nothing to indicate otherwise.
> 
> TL;DR: the suggested action is to use the date range to define posting date.
> As an added bonus, depending on feasibility, a 2nd date range input could be
> added for reconciliation date, but as illustrated, this is probably an order
> of magnitude less useful.  I'm sure someone will find a use for it, but
> definitely not in any typical sense that I can think of as a CPA.

                                                               ^ this

Hence your views are very valuable.

Can you attach a sample report illustrating an ideal reconcile report please?

A textbook photograph, or even a spreadsheet printout will be fine.

> From my understanding I'd expect that your
> reconciled date *should* be 20th April?

Hmm, maybe this is a difference on my part between what I had in mind as a "reconciliation date" vs what GNUCash is using as a reconciliation date.  My original thought was "reconciliation date" meant "the date on which I perform the reconciliation".  In other words, if I receive my statement on May 5th and perform my reconciliation on that date, that is the "reconciliation date".

From how you describe it, it sounds like, as implemented, "reconciliation date" refers to "the date for which you are validating the balance against", e.g. the statement date.  If that is the case, then this useful information, to know what transaction ties to which statement.

Knowing this now, they should probably both be filterable.
1. When performing the reconciliation, the posting date is valuable, as this is what you will use to identify transactions that should be included in a particular statement.
2. After a reconciliation is completed, the reconciliation date becomes useful, as this will let you know what statement each transaction ties against.
3. That being said, posting date is universally valuable, both before and after reconciliation.  Even after reconciliation, I would, for example, want to filter for all posted transactions in a month, and see in the month if a particular transaction reconciled to the current statement or the prior one.

> What do you think of bug 797640 https://bugs.gnucash.org/show_bug.cgi?id=797640

1. Hmm, indeed, the starting balance ignoring splits after the statement date would make it easier to re-reconcile old statements.
2. That being said, typically after I reconcile something, I don't usually go back to the old statement; I just re-reconcile to today's date and flag off the offending transaction as reconciled.
3. This is mainly because the reconciliation flag is what matters to me, rather than the reconciliation date per se.  If you are wondering why...
4. The fact that the reconciliation date is not displayed in the register as a column is the main reason I don't particularly care what is the reconciled date for a given transaction.  If the reconciliation date was visible, then perhaps I would attempt to reconcile to a prior statement date instead.
5. As a fun fact, I reconcile my GNUCash to my bank account's online record on a weekly basis, so to me, the reconciliation date is meaningless, since it is almost never a statement date.
6. Instead, what I would probably do is run a transaction report with a posting date range equal to my statement date range, and see that the total activity by posting date agreed to the bank statement I am holding in front of me.  I wouldn't rely on reconciliation date... mainly because I cannot directly modify it in the register, so the reconciliation date more or less adds no value for me since I cannot easily directly modify errors; I can only fix it either directly in the SQL, or indirectly by re-running the reconciliation tool.

In terms of the example brought up in the bug by Geert:
> * reconcile should happen at say 2020-01-31
> * one transaction that is part of the bank account statement is dated 2020-02-01
1. This is quite common for cheque writing accounts that are also sweep accounts.  For example:
2. On 2020-01-01, my account balance is $0.00
3. During the month, I write cheques totaling $3,000, so my balance is now $(3,000).
4. On 2020-01-31, my balance is $(3,000).
5. To prevent overdraft fees, the bank's auto-sweep kicks in.  On 2020-02-01 at 00:00 (so right after midnight), the bank posts a payment from my operating account against the cheque account, in the amount of $3,000, but backdated to 2020-01-31 23:59:999, so that my account balance is $0.00 on 2020-01-31 at end of day.  This "sweep" payment has been backdated to ensure a $0 account balance, hence the name "backdated sweep transaction".
6. From a customer perspective, the backdated sweep is NOT visible on my 2020-01-31 bank statement; this is because the "physical" movement of cash did not occur during 2020-01.  As such, the ending balance of my 2020-01-31 statement is $(3,000).
7. Instead, the backdated sweep transaction is printed on my 2020-02-29 statement, but the transaction date is backdated and printed as 2020-01-31.

In terms of what is more useful to the user, reconciling to a $(3,000) balance on 2020-01-31 vs reconciling to a $0.00 balance on 2020-01-31, your bank account operation agreement, for a cheque-writing sweep account, dictates that "the account balance will be $0.00 at the close of business at the end of the month, so that no fees, penalties, or interest is incurred".  In other words, I would more likely than not, want to reconcile to the $0.00.  This necessitates that a future dated transaction may need to be included in my reconciliation.

> and the long thread starting at
> https://lists.gnucash.org/pipermail/gnucash-devel/2020-April/044802.html

1. Rather than defaulting it to Unix time 0, I would rather zero out the reconciliation date and force the user to re-reconcile.  That's my take on it anyways.  Reconciling to a future date makes sense in limited circumstances; even a contractual future balance "set in stone" can change due to unforeseen events such as force majeure.
2. In terms of a 1 month leeway to reconcile-ahead, consider 3 months, since quarterly statements are something to consider.  I would also do it not as a strict +90 days, but rather +90 + whatever remainder is needed to reach the end of that month.  For example, if today is Feb 16th, allow reconcile-ahead up to May 31st rather than May 16th.  If you're going to keep it at 1 month, then make it March 31st not March 16th.
3. See also the commentary above on Geert's example, as it relates to backdated transactions and/or transactions that have retroactive effect.

> Can you attach a sample report illustrating an ideal reconcile report please?
> A textbook photograph, or even a spreadsheet printout will be fine.

Hmm, well, this message came in as I was more than 1/2 done writing my large reply, so I didn't modify my original message to take this into account.

For sure, I can whip up something in Excel as an example of what I would use, if I were to prepare a reconciliation by hand.  Though, the comments from my long-winded answer might already give you some ideas.

I'll look into putting together a clean example some time this week.

Hmm, I should also mention that if the reconciliation date were more accessible (by this, I mean editable directly in the register GUI), I would probably use it more, and actually reconcile to a statement date.  As it stands right now, I use it simply as a binary "yes / no" reconcilation flag.

Agree on all counts. I routinely also reconcile at random times, therefore reconciled_date = $today at reconciliation. But also wanted to reconcile past statements especially when hunting an errant split. Hence wanted to harness the split->reconciled_date to reconcile old statements (and also do balance assertions) but created a whole lot of problems described in thread and bug 797640. So, this field isn't really that useful. The reconciled_status==YREC is currently enough for most purposes.

IMV to allow/enable balance assertions in the future, would need a completely new split metadata which would get filled during future reconciliations.

This is irrelevant to this bug which is a trep-engine.scm limitation and I'll need to see how to safely fix.

The other issue about reconciled_date filtering will need to be discussed in bug 796614.

Created attachment 373704 [details]
upgrade reports to merge cells for subtotal-table

upgrade trep/reconcile report to merge dr/cr for subtotal-table.

Regarding the double date sorting, rather than necro-ing an old ticket in bug 796614, I have started a new ticket bug 797772 instead to keep track of that separate issue.

> IMV to allow/enable balance assertions in the future, would need a 
> completely new split metadata which would get filled during future 
> reconciliations.

Well, my comments on quarters and future periods was in response to this remark of yours on the mailing list https://lists.gnucash.org/pipermail/gnucash-devel/2020-April/044802.html

> 1. any reconciliation must have a statement date of TODAY + 1MONTH. This
> allows some leeway for users who wish to reconcile in advance, yet
> disallows reconciliation too far ahead.

All I am saying is, should you implement this "feature", instead of a strict 1 month, it should be as I had described regarding quarters:
> 2. In terms of a 1 month leeway to reconcile-ahead, consider 3 months, since 
> quarterly statements are something to consider.  I would also do it not as a 
> strict +90 days, but rather +90 + whatever remainder is needed to reach the 
> end of that month.  For example, if today is Feb 16th, allow reconcile-ahead 
> up to May 31st rather than May 16th.  If you're going to keep it at 1 month, 
> then make it March 31st not March 16th.

For the patch that you posted, I'm not sure how I would go about implementing it... other than manually edit Scheme line by line by hand.  Alternatively, if it's now in the nightly, I'll go download that from Master.

Go ahead edit scheme manually. Alternatively flatpak from https://code.gnucash.org/builds/flatpak/christopherlam/beta/ after today will have a beta. This fix will be for 4.x because I won't risk potentially breaking 3.11.

try master's nightly tomorrow or later.

https://code.gnucash.org/builds/win32/master/

fixed in master.

Confirmed the credit balance issue is fixed for reconciliation report on this nightly that I checked:
> gnucash-3.903-2020-06-06-git-3.903-17-ge4e36e684+.setup.exe