for a client I'd like to show a KPI called "Total of Articles to deliever".
The tricky thing comes with the data structure which is build up of the following tables and relationships (as a small exemple, I added not only the twbx but also the small data source).
- orders of articles which could contain complete products (in my example: cars), major parts (here: engines) and small parts (srews, pistons, gaskets etc)
- with the table ORD_ART, the orders are linked to the ARTICLES table (via art_no)
- in ORD_ART also the amount of each order position is given
- all articles of all levels e.g. complete products, major parts, small parts
- via the field cp_id, ARTICLES is linked to the table PARTS which contains all parts and their amounts of which an articles is made of (there are some articles which don't have parts in this table, espcecially the small parts)
- as mentioned, it contains the set of parts and their amount of which the articles in the table ARTICLES are made of
- to get information on the those parts, it's necessary to link PARTS again with ARTICLES
The dashboard ARTICLE shows lists of orders which contain the parameter-selected article.
One list (Level0) shows all orders in which the article is explicitely mentioned as order position.
The other list (Level0) shows all orders in which the article is implicitely order as part of a higher level articles.
For example: the engine ENG-1 is ordered 5 times within order AB/01/17, but implicitely ordered within three orders of cars containing this engine. These implicite orders sum up to 7 according to the ordered amount of each car.
My questions are the following:
1. The calculation of "Total_Level_0" (resp. "Total_Level_1") seems to be OK for the cars and engines but failes for the smaller parts.
2. How could I build up to overall sum "TOTAL" ?
The real case not only contains two product-parts levels but four (jining another two times the tables ARTICLES and PARTS to the chain of JOINs). Therfore the sums should also work for those four levels.
All table calc crunchers and LOD wizards are kindly invited....