CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
goalie35
goalie35

Reputation: 786

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

I'm having an ssrs issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name   | QuantityAvailable
----------------------------------------
1      | NULL           | Kit A  | 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 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:

Name   | Available Qty
--------------------------
Kit A  | 15
Kit B  | 25
Item 4 | 100

Any idea how I can setup my report to group properly?

Thanks in advance.

Upvotes: 0

Views: 335

Answers (4)

TPhe
TPhe

Reputation: 1671

If your query is highly complicated, what about using a Common Table Expression to create a temporary table-like object of the results? Then the SQL-based answer to your question would be as easy as applying one of the other answers' subquery techniques.

If that really doesn't work for you, I would probably create a group within your tablix with a group by expression something like this: 

=iif(isNothing(Parent_Item_Id.Value), ItemId, Parent_Item_ID)

That will group the parent item together with its children, and you can do a Sum() function at the group level. To get the name of the parent item, you would use an expression like:

=max(iif(isNothing(Parent_Item_Id.Value), Name.Value, ""))

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

select name, sum(quantityavailable) as available_qty
  from (select name, quantityavailable
          from tbl
         where parent_item_id is null
        union all
        select y.name, x.quantityavailable
          from tbl x
          join tbl y
            on x.parent_item_id = y.itemid) x
 group by name

Fiddle: http://sqlfiddle.com/#!6/36871/3/0

Upvotes: 1

Tsarin
Tsarin

Reputation: 181

It would be a lot easier to do in your dataset.

Sample data:

WITH items
AS (
SELECT
     1 AS ItemId
    ,NULL AS Parent_Item_Id
    ,'Kit A' AS Name
    ,10 AS QuantityAvailable

UNION ALL

SELECT
     2 AS ItemId
    ,1 AS Parent_Item_Id
    ,'Item 1' AS Name
    ,2 AS QuantityAvailable

UNION ALL

SELECT
     3 AS ItemId
    ,1 AS Parent_Item_Id
    ,'Item 2' AS Name
    ,3 AS QuantityAvailable

UNION ALL

SELECT
     4 AS ItemId
    ,NULL AS Parent_Item_Id
    ,'Kit B' AS Name
    ,4 AS QuantityAvailable

UNION ALL

SELECT
     5 AS ItemId
    ,4 AS Parent_Item_Id
    ,'Item 3' AS Name
    ,21 AS QuantityAvailable

UNION ALL

SELECT
     6 AS ItemId
    ,NULL AS Parent_Item_Id
    ,'Item 4' AS Name
    ,100 AS QuantityAvailable
)

Query:

 SELECT
         parent.Name
        ,parent.QuantityAvailable + ISNULL(child.QuantityAvailable, 0) AS [Available Qty]
    FROM items parent
        LEFT JOIN (
                SELECT
                    Parent_Item_Id
                    ,SUM(QuantityAvailable) AS QuantityAvailable
                FROM items
                WHERE Parent_Item_Id IS NOT NULL
                GROUP BY Parent_Item_Id
                ) AS child
            ON parent.ItemId = child.Parent_Item_Id
    WHERE parent.Parent_Item_Id IS NULL

Upvotes: 0

Angus Chung
Angus Chung

Reputation: 1587

You can try this way to get the SUM of both the kit & its components.

SELECT A.Name,A.QuantityAvailable+isnull(B.QuantityAvailable,0) AS [Available Qty] 
FROM ItemTable A
LEFT JOIN 
(
    SELECT Parent_Item_Id
    ,SUM(QuantityAvailable) as QuantityAvailable 
    FROM ItemTable
    WHERE Parent_Item_Id IS NOT null
    GROUP BY Parent_Item_Id 
)B
ON A.ItemId=B.Parent_Item_Id
WHERE A.Parent_Item_Id IS NULL

OUTPUT

Name    Available Qty
Kit A   15
Kit B   25
Item 4  100

Upvotes: 2

Related Questions