Create Product Tree and Report by Summary Product

The purpose of this discussion is to help you add a product tree to your product window, then report gross sales/revenue by product tree node (recursively including each sub-node).

Add a Tree to your Product Window

Perform the following:

Log in as System Administrator
Open the Window, Tab & Field window for the "Product" window.
Click on the Tab sub-tab and find the "Product" tab record.
Go do detail, and check the "Has Tree" field.
Save
Log in as your client's admin (GardenAdmin for example) and view your product window to confirm it now has a tree in the left pane of the window.

References

see: https://www.youtube.com/watch?v=_RXfnnqsLlw

Create a View that will Traverse your tree of Product Nodes

The following view uses a recursive (recursion) function to iterate across all nodes your (summary) product tree:

CREATE or Replace VIEW chuboe_product_tree_children AS
 WITH RECURSIVE included_nodes(node_id, parent_id, orig_id) AS (
         SELECT ad_treenodepr.node_id,
            ad_treenodepr.parent_id,
            ad_treenodepr.parent_id AS orig_id
           FROM ad_treenodepr
          WHERE (ad_treenodepr.parent_id > (0)::numeric)
        UNION ALL
         SELECT p.node_id,
            p.parent_id,
            pr.orig_id
           FROM included_nodes pr,
            ad_treenodepr p
          WHERE (p.parent_id = pr.node_id)
        )
 SELECT included_nodes.node_id,
    included_nodes.parent_id,
    included_nodes.orig_id
   FROM included_nodes
;

Create a View that Sums Sales/Revenue By Product Tree Node

You can modify this view however you wish. A logical improvement is to take the Invoice line's PriceActual - PriceLimit to derive a total gross profit. Use the below videos to take this view and turn it into an iDempiere report.

CREATE OR REPLACE VIEW chuboe_Sales_by_product_summary as
select p.ad_client_id, p.ad_org_id,
p.created, p.createdby, p.updated, p.updatedby,
p.value, p.name, coalesce(
(select sum(linenetamt) from rv_c_invoiceline where m_product_id in (select node_id from chuboe_product_tree_children where orig_id = p.m_product_id)
),0) as totalamt
from m_product p
where issummary = 'Y'
;

Video Demonstration

This video demonstrates the above concepts

Using this Approach in other Areas (BP, Project, etc...)

This same concept can be applied in multiple areas. Open the Tree Maintenance window, and you can see all the trees that exist by default. The one big difference to note is that the product tree is different than all the others. The product tree is held in its own table (ad_treenodepr) as opposed to ad_treenodebp or just plain ad_treenode. You will need to experiment to see where the changes happen when you change your tree. Once you learn the correct table, then you just replace the above values accordingly. Please note that if the tree exists in ad_treenode (with not suffix), it will be shared with other trees. It is important that you use the correct ad_tree_id to correctly identify the right records. Each tree will have a different ad_tree_id.

Generic AD_TreeNode Recursive SQL

CREATE or Replace VIEW chuboe_treenode_children AS
 WITH RECURSIVE included_nodes(ad_client_id, ad_tree_id, node_id, parent_id, orig_id) AS (
 SELECT ad_treenode.ad_client_id,
 ad_treenode.ad_tree_id,
 ad_treenode.node_id,
 ad_treenode.parent_id,
 ad_treenode.parent_id AS orig_id
 FROM ad_treenode
 WHERE (ad_treenode.parent_id > (0)::numeric)
 UNION ALL
 SELECT p.ad_client_id,
 p.ad_tree_id,
 p.node_id,
 p.parent_id,
 pr.orig_id
 FROM included_nodes pr,
 ad_treenode p
 WHERE (p.parent_id = pr.node_id and p.ad_tree_id = pr.ad_tree_id)
 )
 SELECT included_nodes.ad_client_id,
 included_nodes.ad_tree_id,
 included_nodes.node_id,
 included_nodes.parent_id,
 included_nodes.orig_id
 FROM included_nodes
union all -- include all children as parents to themselves to allow for inner joins to this view
select tn.ad_client_id, tn.ad_tree_id, tn.node_id, tn.node_id as parent_id, tn.node_id as orig_id
from ad_treenode tn
where node_id not in (select coalesce(xtn.parent_id,0) from ad_treenode xtn where xtn.ad_tree_id = tn.ad_tree_id)
union all -- include orgs with no parents
select tn.ad_client_id, tn.ad_tree_id, tn.node_id, tn.node_id as parent_id, tn.node_id as orig_id
from ad_treenode tn
where tn.parent_id = 0 
;

If you wish to view the GardenWorld => Org tree, use AD_Tree_ID = 104. This tree is identified in the Client window => Client Info subtab at the bottom or the tab. If you want all children through to the lowest level of the tree, you join chuboe_treenode_children in via the orig_id and select the node_id. If you only want the immediate children, you join chuboe_treenode_children in via the parent_id and select the node_id. Note: the above view is used to map Accounting Schemas to Orgs in the Balance Sheet Defense. Example SQL to view all Organizations in the tree:

select * from chuboe_treenode_children where
AD_Tree_ID=104;

2014-12-30 Create Print Format with View From Scratch - Series

This video series helps you quickly create a Print Format from scratch.

Create Product Tree and Report by Summary Product

Add a Tree to your Product Window

References

Create a View that will Traverse your tree of Product Nodes

Create a View that Sums Sales/Revenue By Product Tree Node

Video Demonstration

Using this Approach in other Areas (BP, Project, etc...)

Generic AD_TreeNode Recursive SQL

2014-12-30 Create Print Format with View From Scratch - Series

Print Format Scratch - Creation Introduction

Print Format Scratch - Report vs. Print Toolbar Buttons

Print Format Scratch - Best Practices

Print Format Scratch - New Print Format from Scratch from View - Demo