Search filters may produce unexpected results in Reporting/Analysis views

[Steffi Frank (Bremskerl, DE)]

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....

[DBR (OpenERP) (dbr-openerp)]

Hello Steffi Frank ,

I have tested the 'Inventory Analysis' as you specified.But it is working fine at my end.
On basis of that I have attached Screenshots so please check and Inform us.

Location :
Parent location : stock
Child location : shop 1
Production lot : same for parent and child [ Finished products location ]

If I am missing something than please notify me.

Thank You.

[Steffi Frank (Bremskerl, DE) (steffi-frank)]

Hello Devishree,

how did you create the stock for production lot 0000002 in location 'Shop 1'? Through 'incoming shipments' or 'internal move'?

It works if you make both moves through incoming shipments.

It doesn't work if you:

first: create new move/production lot through 'incoming shipment' with dest.location = 'Stock'
second: create an 'internal move' for the same production lot from 'Stock' to 'Shop 1'

Could you please try this?

Thanks

[DBR (OpenERP) (dbr-openerp)]

Thanks for Reporting.

[Rohan Nayani(Open ERP) (ron-tinyerp)]

Hello Steffi,
We have checked your problem,
the bug is quite easy to generate, but there is nothing wrong in the "Inventory Analysis" report,
the report is considering every move lines and creates a record in report for both locations of move (source and destination):
for the case of your pdf i have attached one pdf with queries and its results (its a bit technical! :-) ):
share your views please...
thanks

[Steffi Frank (Bremskerl, DE) (steffi-frank)]

Hello Rohan,

thanks for your information! from technical point of view I agree: it's not a bug

From functional point of view I find it quite hard to explain to a front-end user what happens.....

This view does not lists the stock moves but the stock level depending on the filters you set. This view contains column 'Quantity' which is always the result (+/-) of all stock moves. Therefor the filter 'quantity' as well should filter the sum - not the stock moves

What do you think?

[Steffi Frank (Bremskerl, DE) (steffi-frank)]

Hello Rohan,

any chance for an answer?

thanks

[Rucha (Open ERP) (rpa-openerp)]

Hello Steffi,
Indeed your point is right,
(no chance for answer ;-) )
we need to check it deeply...
thanks for your patience.

[Steven_M (stevenmoca)]

We are having some problems with this report giving different results when using Filters as well. It seems as if as soon as you add a Filter, it doesn't pull from the view and the data is different. For example:

We are trying to use the report to perform audits and find empty locations: So for example we will select Real, Internal and then in Location we will enter '02-' to show us only locations that start with 02, which is a row in our case.

Then in the Group Bys we will only select Location.

As soon as we add a filter of Quantity and say 'is equal to' 0 we get much different results.

I have also attached a doc with screenshots. Since we have a publisher warranty, could we get this bug moved to a higher importance?

[Olivier Dony (Odoo) (odo-openerp)]

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:...

[Kirti Savalia(OpenERP) (ksa-openerp)]

Hello,

According to given comment #11 currently setting this bug as wishlist.

Thanks.

[Corinne Cobbel (ccobbel)]

Hi,

I understand that the issue could be technically difficult as there are many filters and group by. However the date filter is a very important filter and it doesn't work. In my case as soon as I put an end date I have less stock than without date (and there have been only expeditions, no reception), I should have more stock in the past than today.
Common need is to provide accountant or bank manager with a stock valorization at a fixed date (End of the year or end of the month) without to do a physical inventory. How can we provide this information (e.g stock valorization at the end of November and we are December 20th)) if the filter date on Inventory analysis doesn't work ? Is there an other way with OpenERP to do that ?

Many thanks at OpenERP team, you do a great job, I wish you Happy Holidays.

Corinne

[Fabien (Open ERP) (fp-tinyerp)]

we do not plan to implement searches on function fields, I close this bug.