version 4 GL & balance reports

[Richard PALO]

As indicated https://bugs.tryton.org/issue5168 I'm trying to see how to approach (as suggested) under 4.0
a function field 'searcher' that operates on 'sum' fields as the patch which worked on 3.8 no longer does.

The issue is that since 4.0 the balance and GL reports are huge and, as such, somewhat unuseable as they
include *all* accounts, including those with a start balance of 0 *and* no movements (D/C).

Under the French plan (PCG), I have for example in a company a balance generating 40 pages of output
for only 28 accounts usually printing on two pages.

NB: there are over 1200 accounts defined in the French plan

The General Ledger prints 948 pages (!) mostly throw away.

It should be possible to generate [periodic] balance or GL reports including only the
movements in the period (thus without start balance) as well.

This, unfortunately, has turned into a blocker to migrate production to 4.0 for the moment.

How would a 'searcher' be able to do this in the first place?
Is there a good example already implemented in the existing modules to take a look at?

To me, it still seems more logical to have a global 'default' option 'with start balance'
and only, always, generate accounts with movements.

(That is, generating 'all' accounts is perhaps something that could better be done from
configure->accounts or perhaps a even a new report under account plan)

I'd like to solicit other experiences/observations/suggestions from the community.

cheers

--

Richard PALO

--
Richard PALO

[Sergi Almacellas Abellana]

El 27/05/16 a les 09:42, Richard PALO ha escrit:

> As indicated https://bugs.tryton.org/issue5168 I'm trying to see how to approach (as suggested) under 4.0
> a function field 'searcher' that operates on 'sum' fields as the patch which worked on 3.8 no longer does.
>
> The issue is that since 4.0 the balance and GL reports are huge and, as such, somewhat unuseable as they
> include *all* accounts, including those with a start balance of 0 *and* no movements (D/C).
>
> Under the French plan (PCG), I have for example in a company a balance generating 40 pages of output
> for only 28 accounts usually printing on two pages.
>
> NB: there are over 1200 accounts defined in the French plan

Same amount of accounts in spain, so same experience here.

>
> The General Ledger prints 948 pages (!) mostly throw away.
>
> It should be possible to generate [periodic] balance or GL reports including only the
> movements in the period (thus without start balance) as well.

I think this is a common requirement.

>
> This, unfortunately, has turned into a blocker to migrate production to 4.0 for the moment.
>
> How would a 'searcher' be able to do this in the first place?

As Far as you implement a searcher to search the amount you will be able
to filter does records with value != 0.

> Is there a good example already implemented in the existing modules to take a look at?

I think search_receivable_payable will be a good example:

http://hg.tryton.org/modules/account/file/tip/party.py#l126

>
> To me, it still seems more logical to have a global 'default' option 'with start balance'
> and only, always, generate accounts with movements.

For me it should be an option disabled by default so the user can check.

>
> (That is, generating 'all' accounts is perhaps something that could better be done from
> configure->accounts or perhaps a even a new report under account plan)
>
> I'd like to solicit other experiences/observations/suggestions from the community.
>
> cheers
>


--

Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[Richard PALO]

Given that 4.2+ will be the basis for any future platform certification
(in particular for purposes of legal requirements in France),
I'd like to renew the call to see if anybody has worked on adding the
afore mentioned options to general ledger and balance reports.

Having not had more than cursory time myself, perhaps a crowd funding approach is more
appropriate. We would certainly participate!

Anybody get further into prototype mode in order to propose a blueprint supporting,
in addition to 'all' as is currently:
1. with 'start balance <> 0'
and/or
2. with 'end balance <> 0'
and/or
3. with movements (in the period)

seems there should probably also be an option simply to include or not the 'start balance' as well
while suppressing the non-intuitive behaviour currently of 4.x selecting by default only the first account.

There seems to miss as well a means to be able to select only non-reconciled entries (relative to the
accounting year), which appears to be a crucial need expressed by accountants and legal auditors.

[Cédric Krier]

On 2016-12-03 13:37, Richard PALO wrote:
> Anybody get further into prototype mode in order to propose a blueprint supporting,
> in addition to 'all' as is currently:
> 1. with 'start balance <> 0'
> and/or
> 2. with 'end balance <> 0'
> and/or

I think written is quite complex because it is a SQL SUM but with
cumulate of previous fiscal year.
So I think probably the easiest will be to write a searcher that will
filter the records using python evaluation for each record. This is not
very performant but it should be OK as the number of account should not
be too large.

> 3. with movements (in the period)

This could probably be done by creating a Function field that returns
the number of moves.

> seems there should probably also be an option simply to include or not the 'start balance' as well

Do you mean that you want to have a column with debit - credit ?

> while suppressing the non-intuitive behaviour currently of 4.x selecting by default only the first account.

That's default behaviour to select the first record, I do not think it
will change.

> There seems to miss as well a means to be able to select only non-reconciled entries (relative to the
> accounting year), which appears to be a crucial need expressed by accountants and legal auditors.

I do not understand exactly what you mean. For me, there is no sense to
care about non-reconciled per fiscal year. Also I do not see the point
to care about them individually, we have the Aged Balance for that.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Richard PALO]

Le 04/12/2016 à 04:06, Cédric Krier a écrit :
> On 2016-12-03 13:37, Richard PALO wrote:
>> Anybody get further into prototype mode in order to propose a blueprint supporting,
>> in addition to 'all' as is currently:
>> 1. with 'start balance <> 0'
>> and/or
>> 2. with 'end balance <> 0'
>> and/or
>
> I think written is quite complex because it is a SQL SUM but with
> cumulate of previous fiscal year.
> So I think probably the easiest will be to write a searcher that will
> filter the records using python evaluation for each record. This is not
> very performant but it should be OK as the number of account should not
> be too large.
>

This is what more or less what I concluded.

We have easily 20-30K general ledger entries per year (ignoring analytic)
which I consider small to medium for an SME.

As a side note, seems a bit heavy if tryton needs to read all previous
years to get starting balance (I most certinaly hope not).
This is one of the benefits of having an annual opening balance as is
traditionnally done (not to mention it expressly provides for independent
accounting years, a fundamental principle in accounting).
In any event, one must not be myopic as to the number of fiscal years.

>> 3. with movements (in the period)
>
> This could probably be done by creating a Function field that returns
> the number of moves.
>
>> seems there should probably also be an option simply to include or not the 'start balance' as well
>
> Do you mean that you want to have a column with debit - credit ?
>

Well, for a balance report, it is useful to have either extra column for initial balance, or both debit and credit,
but this is probably a simple matter of report formatting which could be site specific.

For GL it is typically an additional line prior to the moves for the period.

>> while suppressing the non-intuitive behaviour currently of 4.x selecting by default only the first account.
>
> That's default behaviour to select the first record, I do not think it
> will change.
>

For a report, this is not intuitive though perhaps a 'selection only' option that limits output to what is
selected on the screen could continue to provide current behaviour... But have you tried to select only used
accounts (say 200-300) out of 1200 in e.g. the French accounting plan?

>> There seems to miss as well a means to be able to select only non-reconciled entries (relative to the
>> accounting year), which appears to be a crucial need expressed by accountants and legal auditors.
>
> I do not understand exactly what you mean. For me, there is no sense to
> care about non-reconciled per fiscal year. Also I do not see the point
> to care about them individually, we have the Aged Balance for that.
>

Balance is not interesting for this, although auxiliary balance/GL is also necessary,
what is expected is a GL *detailing* only non-reconciled entries, typically for year-end
(or periodic situation) closing.
There are many examples such as in SAGE, CEGID, Unit4(if I remember well) ....


--
Richard PALO

[Cédric Krier]

On 2016-12-04 07:59, Richard PALO wrote:
> As a side note, seems a bit heavy if tryton needs to read all previous
> years to get starting balance (I most certinaly hope not).

It is not heaving and indeed it is even a speedup.

> This is one of the benefits of having an annual opening balance as is
> traditionnally done (not to mention it expressly provides for independent
> accounting years, a fundamental principle in accounting).

This is not the design chosen by Tryton because it breaks any attempt to
track receivable or payable.

> In any event, one must not be myopic as to the number of fiscal years.

Do not understand.

> > > 3. with movements (in the period)
> >
> > This could probably be done by creating a Function field that returns
> > the number of moves.
> >
> > > seems there should probably also be an option simply to include or not the 'start balance' as well
> >
> > Do you mean that you want to have a column with debit - credit ?
> >
>
> Well, for a balance report, it is useful to have either extra column for initial balance, or both debit and credit,
> but this is probably a simple matter of report formatting which could be site specific.

For me, it is not necessary on trial balance because the goal is to show
that the accounts are balanced so if start and and balance sum equals 0
and debit equals credit sum, it is OK.

> For GL it is typically an additional line prior to the moves for the period.

There is already.

> > > while suppressing the non-intuitive behaviour currently of 4.x selecting by default only the first account.
> >
> > That's default behaviour to select the first record, I do not think it
> > will change.
> >
>
> For a report, this is not intuitive though perhaps a 'selection only' option that limits output to what is
> selected on the screen could continue to provide current behaviour...

It is better to have always the same behaviour in the all application.
Any action applies on what is selected.

> But have you tried to select only used
> accounts (say 200-300) out of 1200 in e.g. the French accounting plan?

Use the filters.

> > > There seems to miss as well a means to be able to select only non-reconciled entries (relative to the
> > > accounting year), which appears to be a crucial need expressed by accountants and legal auditors.
> >
> > I do not understand exactly what you mean. For me, there is no sense to
> > care about non-reconciled per fiscal year. Also I do not see the point
> > to care about them individually, we have the Aged Balance for that.
> >
>
> Balance is not interesting for this, although auxiliary balance/GL is also necessary,
> what is expected is a GL *detailing* only non-reconciled entries, typically for year-end
> (or periodic situation) closing.

What is the point? It is useless in Tryton because non-deferral lines
stay.

[Richard PALO]

Le 04/12/2016 à 12:30, Cédric Krier a écrit :
> On 2016-12-04 07:59, Richard PALO wrote:
>> As a side note, seems a bit heavy if tryton needs to read all previous
>> years to get starting balance (I most certinaly hope not).
> It is not heaving and indeed it is even a speedup.

I see, account_account_deferral comes in for caching here. Great.

>>>> 3. with movements (in the period)
>>> This could probably be done by creating a Function field that returns
>>> the number of moves.
>>>
>>>> seems there should probably also be an option simply to include or not the 'start balance' as well
>>> Do you mean that you want to have a column with debit - credit ?
>>>
>> Well, for a balance report, it is useful to have either extra column for initial balance, or both debit and credit,
>> but this is probably a simple matter of report formatting which could be site specific.
> For me, it is not necessary on trial balance because the goal is to show
> that the accounts are balanced so if start and and balance sum equals 0
> and debit equals credit sum, it is OK.

To be clear, what I meant about including or not 'start balance' is for the case where
an extract is requested of only the mouvements during the period should show up on 'end balance'

In this case, 'start balance' column is not used in computing the 'end balance', and needs
not be included, but if displayed, the values should necessarily be '0,00'.

with 'start balance'
Account | Start Balance | Debit | Credit | Debit - Credit - Start Balance (like now)

without
Account | 0,00 | Debit | Credit | Debit - Credit
or simply (facultive)
Account | Debit | Credit | Debit - Credit

>> For GL it is typically an additional line prior to the moves for the period.
> There is already.

yes, indeed... but the request is similar.

>>>> while suppressing the non-intuitive behaviour currently of 4.x selecting by default only the first account.
>>> That's default behaviour to select the first record, I do not think it
>>> will change.
>>>
>> For a report, this is not intuitive though perhaps a 'selection only' option that limits output to what is
>> selected on the screen could continue to provide current behaviour...
> It is better to have always the same behaviour in the all application.
> Any action applies on what is selected.

Well, normally, selection is an active process. But when these missing filters are available,
they can be saved and this process will be much improved.

>> But have you tried to select only used
>> accounts (say 200-300) out of 1200 in e.g. the French accounting plan?
> Use the filters.

I guess that brings us back to the subject at hand ;-)

>>>> There seems to miss as well a means to be able to select only non-reconciled entries (relative to the
>>>> accounting year), which appears to be a crucial need expressed by accountants and legal auditors.
>>> I do not understand exactly what you mean. For me, there is no sense to
>>> care about non-reconciled per fiscal year. Also I do not see the point
>>> to care about them individually, we have the Aged Balance for that.
>>>
>> Balance is not interesting for this, although auxiliary balance/GL is also necessary,
>> what is expected is a GL *detailing* only non-reconciled entries, typically for year-end
>> (or periodic situation) closing.
> What is the point? It is useless in Tryton because non-deferral lines
> stay.
>

Well, as already mentioned prior (I believe in tryton-fr and in the issue tracker), the accountant and legal auditor ask

for it, as well as the decision maker who wants a report of only these 'open' entries, for readability purposes.

cordially,

--
Richard PALO