sql - SSRS: Need to group & sum items & sub items into one record -

sql - SSRS: Need to group & sum items & sub items into one record -

i'm having ssrs issue creating study can grouping & sum similar items (i know in ways, requirement doesn't create sense, it's client wants).

i have table of items (i.e. products). in cases, items can components of item (called "kits"). in scenario, consider kit itself, "parent item" , components within kit called "child items". in our items table, have field called "parent_item_id". records kid items contain item id of parent. sample of database following:

itemid | parent_item_id | name | quantityavailable ---------------------------------------- 1 | null | kit | 10 2 | 1 | item 1 | 2 3 | 1 | item 2 | 3 4 | null | kit b | 4 5 | 4 | item 3 | 21 6 | null | item 4 | 100

item's 2 & 3 kid items of "kit a", item 5 kid item of "kit b" , item 6 stand lone item.

so, in report, client wants see sum of both kit & components in single line, grouped parent item. illustration of study following:

name | available qty -------------------------- kit | 15 kit b | 25 item 4 | 100

any thought how can setup study grouping properly?

thanks in advance.

you can seek way sum of both kit & components.

select a.name,a.quantityavailable+isnull(b.quantityavailable,0) [available qty] itemtable left bring together ( select parent_item_id ,sum(quantityavailable) quantityavailable itemtable parent_item_id not null grouping parent_item_id )b on a.itemid=b.parent_item_id a.parent_item_id null

output

name available qty kit 15 kit b 25 item 4 100

sql sql-server reporting-services ssrs-2008