openerp-india team mailing list archive

["Olivier Dony \(OpenERP\)" <743715@xxxxxxxxxxxxxxxxxx>]

Hello,

This surprising behavior comes from the technical architecture behind
the dynamic aggregation feature built into OpenERP 6.0, usually called
"group by". This behavior can be very surprising for end-users, and even
more so in reporting/analysis screens when filtering on sums/balances
that are related to double-entry systems (stock and accounting).

Technically, the filtering of source data according to the user-selected filters is done on the raw underlying data,  *before* aggregating (summing). Solving the issue, however, is not as simple as deciding that all filtering will be done *after* the aggregation rather than *before*, because this will not be the expected behavior in all situations.
To understand this, you need to know that the "group by" feature is used in 2 different modes:

A) An aggregated presentation mode for normal OpenERP lists. e.g.: on to
the Product list, group by Category. Or on the CRM Opportunities, group
by "Month, then Salesman".  In this mode, each "aggregated" level can be
drilled down to show the individual records it aggregates, e.g. the
individual products or opportunities. But the aggregated lines will not
contain any information that is not simply a sum (or aggregation) of
some field coming from the individual lines it represents.

B) A virtual data mart mode for reporting/analysis screens, e.g:
Warehouse Management>Reporting>Inventory Analysis. In this mode the
underlying records are not visible, they are fully replaced by
aggregated lines. And the aggregated values may possess new fields that
did not exist in the individual records: some are simple aggregations,
but some can be the result of more complex algorithms. Technically, this
mode is known as 'group_by_no_leaf'.

Both modes use the same technical infrastructure, but they have
contradicting needs! Specifically:

A) For mode A you most certainly *need* the filtering to be done at the
individual record level (and thus *before* the aggregation), because
otherwise the sums may not match what is displayed when you drill-down!
Imagine you group Opportunities by Month, and filter on "Expected
revenue > 1000". If results were filtered on aggregated data there is a
big chance your sums will contain *all* opportunities (even w/ 0 exp.
revenue!), because all monthly sums will be > 1000. Definitely not what
you expect, and inconsistent with the drill-down results.

B) For mode B, as explained in this bug report, we usually want to
filter on the aggregated results rather than source data, at least in
the cases where this gives a different result. In certain cases it is
equivalent: filtering the inventory analysis on a given product would
give the same result if filtering is done before or after aggregation.
Moreover, because we should be able to filter on "virtual" fields that
do not exist in the underlying data, we would *need* to filter *after*
aggregation in most cases.

To sum up, we are looking for a way to solve this elegantly in all
cases, and we need to care for both situations appropriately. All
feedback is always welcome, but this will probably not be ready in time
for 6.1.

Thank you for your feedback and your patience!

PS: for OpenERP Enterprise customers, I understand this behavior can be
quite surprising in some cases, but this represents a technical
limitation of the recent "group by" feature of OpenERP v6 rather than a
bug. And this is a limitation we hope to lift in the future.

** Changed in: openobject-addons
   Importance: Low => Medium

** Changed in: openobject-addons
       Status: Incomplete => Triaged

** Summary changed:

- 'Inventory Analysis': different results with extra filter
+ Search filters may produce unexpected results in Reporting/Analysis views

-- 
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Addons.
https://bugs.launchpad.net/bugs/743715

Title:
  Search filters may produce unexpected results in Reporting/Analysis
  views

Status in OpenERP Addons (modules):
  Triaged

Bug description:
  Hard to describe, screenshots will explain more than words....

  Initial situation:

  two locations (one child of the other), different production lots on
  both locations, one production lot partial moved from parent location
  to child

  Open inventory analysis without extra filter, see total qty
  -> set filter 'quantity greater than 0', different value in total qty

  A production lot in both locations seems to cause the trouble....

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-addons/+bug/743715/+subscriptions