Hi Bhav,

How did you implement hierarchy ? Does it have to be SQL statement ?
Maybe you can do UDF traversing herarchy and calculating totals for any given level and then simply return total(s).





>Sorry if this appears as a double post
>
>Hi All
>
>I have an Items master table that is self joinable representing the hierarchy of the items. Each item PK will have their own transactions in various tables. I have a stock ledger report that brings in transactions based on the given PKs, till here everything is fine.
>
>Now the users needs to select an item in the hierarchy that has children, grand-children, great-grand-children and maybe deeper levels and now to provide a stock ledger that consolidates the transactions of all these levels under a given Item PK.
>
>Is this possible using some magical SQL statements like
>

SELECT fields ;
>   FROM TransactTable TT ;
>      SOME JOINS MAYBE ;
>   WHERE TT.iItemID IN (SELECT MagicStatement WHERE iID = ?High-Level-PK)

>
>or do I have to resort to programming that drills downs the hierarchy and brings in transactions piece-meal into a cursor for consolidation purposes.
>
>Please advise.