| complex cross tab report [message #690452] |
Thu, 15 January 2026 07:32 |
Amine
Messages: 377
Registered: March 2010
|
Senior Member |
|
|
Hi all,
I have a hierarchy between levels in my_hierarchy table (between id_lvl and id_lvl_sup).
Each level (lvl) may have items (id_item), and each item has a category (id_item_sup). These data is stored in my_items table.
Now I want to build a report based on the my_view view.
In rows, we have id_lvl1 and id_lvl2.
In columns, we have id_item_sup (first level) and id_item (second level)
In cells, we have the total of items for each id_lvl2.
I want to transform the definition of my_view to "normalize" the number of items that appears in each id_item_sup.
For example, id_item_sup = 2001, we have 4 items.
ID_H ID_ITEM_SUP ORD_ITEM_SUP NB_ITEM_PER_ITEM_SUP
---------- ----------- ------------ --------------------
1000 1002 1002 11
1000 2001 2001 4
1000 2100 2100 5
1000 2200 2200 40
1000 3001 3001 25
1000 3500 3500 13
1000 4001 4001 1
1000 6001 6001 9
1000 7001 7001 3
1000 8001 8001 9
1000 0
The padding consists of adding items with lib_item = '----' at the last of existing items.
ID_H ID_ITEM_SUP ORD_ITEM_SUP ID_ITEM LIB_I ORD_ITEM NB
---------- ----------- ------------ ---------- ----- ---------- ----------
1000 2001 2001 2002 2002 2002 47
1000 2001 2001 2003 2003 2003 501
1000 2001 2001 2004 2004 2004 3
1000 2001 2001 2007 2007 2007 3
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
- number of items < 10, so we pad items to reach 10 items ;
- number of items >= 10, so we create blocks of 10 items. Then, in all the blocks different from the first block we add " next" to lib_item_sup.
For example, number of items is 11, so we create two (02) blocks :
- The first one, with 10 items
- The second one, with one (01) item padded with 9 items with lib '----', and we concatenate " next" to lib_item_sup
Another example, number of items is 34, so we create four (04) blocks :
- The first one, with 10 items
- The second and the third one, with 10 items and we concatenate " next" to lib_item_sup
- The fourth one, with four (04) items, pad with six (06) items with lib '----', then concatenate " next" to lib_item_sup
Hope it s clear
Thanks in advance
PS: Tried to work with ChatGPT, but human interactions are so far better than a tool.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
