Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: SSRS 2005 table group totals summed into parent group

    Good day,

    I have a table in my SSRS report that has a parent with a child who in turn has a child.

    My lowest level child has a total value but I now need to add al those child totals and show them in it's respective parent group and then those totals summed into the top most parent group.

    Is this possible? Unfortunately I am using existing code where the totals for the lowest group are already calculated and therefore used in the groups, and it is not a case of summing the details of he lowest group as that will be a multiple of the totals then.

    I fear I may have to re-write the entire report and sql statement, not ideal, hence me asking if what I am wanting to do is possible.

    Please help

    Thank you

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Does this give you an idea?
    Code:
    DROP TABLE grandchild
    DROP TABLE child
    DROP table parent
    
    CREATE table parent (
    	id	INT NOT NULL,
    	name	VARCHAR(20)	NOT NULL
    )
    
    CREATE TABLE child(
    	id	INT NOT NULL,
    	parent_id	INT	NOT NULL,
    	name	VARCHAR(20)	NOT NULL
    )
    
    CREATE TABLE grandchild(
    	id	INT NOT NULL,
    	child_id	INT	NOT NULL,
    	name	VARCHAR(20)	NOT NULL,
    	total	INT	NOT NULL
    )
    
    INSERT INTO parent (id, name) VALUES (1, 'parent 1');
    INSERT INTO child (id, parent_id, name) VALUES 
    (1, 1, 'child 1'),
    (2, 1, 'child 2'),
    (3, 1, 'child 3');
    
    INSERT INTO grandchild (id, child_id, name, total) VALUES 
    (1, 1, 'grandchild 11', 1),
    (2, 1, 'grandchild 12', 4),
    (3, 1, 'grandchild 13', 6),
    
    (4, 2, 'grandchild 21', 9),
    (5, 2, 'grandchild 22', 10),
    (6, 2, 'grandchild 23', 11),
    
    (7, 3, 'grandchild 31', 200),
    (8, 3, 'grandchild 32', 300)
    
    
    INSERT INTO parent (id, name) VALUES (2, 'parent 2');
    INSERT INTO child (id, parent_id, name) VALUES 
    (11, 2, 'child 11'),
    (12, 2, 'child 12'),
    (13, 2, 'child 33');
    
    INSERT INTO grandchild (id, child_id, name, total) VALUES 
    (11, 11, 'grandchild 111', 10),
    (12, 11, 'grandchild 112', 40),
    (13, 11, 'grandchild 113', 60),
    
    (14, 12, 'grandchild 121', 90),
    (15, 12, 'grandchild 122', 100),
    (16, 12, 'grandchild 123', 110),
    
    (17, 13, 'grandchild 131', 2000),
    (18, 13, 'grandchild 132', 3000)
    
    
    
    ;WITH sum_grandchild AS (
    SELECT child_id, SUM(grandchild.total) as child_total
    FROM grandchild
    GROUP BY child_id
    ),
    sum_child AS (
    SELECT parent_id, SUM(sum_grandchild.child_total) as parent_total
    FROM child
    	INNER JOIN sum_grandchild ON
    		child.id = sum_grandchild.child_id
    GROUP BY parent_id
    )
    SELECT P.name, SC.parent_total, C.name, SGC.child_total, GC.name, GC.total
    FROM parent as P
    	INNER JOIN child as C ON
    		P.id = C.parent_id
    	INNER JOIN grandchild as GC ON
    		C.id = GC.child_id
    	INNER JOIN sum_child as SC ON
    		P.id = SC.parent_id
    	INNER JOIN sum_grandchild as SGC ON
    		C.id = SGC.child_id
    ORDER BY P.name, C.name, GC.name
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Please follow Netiquette.

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    >> I have a table in my SSRS report that has a parent [sic] with a child [sic] who in turn has a child [sic]. <<

    The terms “parent” and “child” are from network DB and not part of RDBMS. We have “referenced” and “referencing” relationships in RDBMS.

    >> My lowest level child [sic] has a total value but I now need to add all those child [sic] totals and show them in it's respective parent [sic] group and then those totals summed into the top most parent [sic] group. <<

    Sure wish I had a hint about the DDL. Your useless narrative sounds like a ROLLUP in a proper schema. But knows

    >> Is this possible? <<

    Again, who knows? How would we tell without even sample data?

    >> I fear I may have to re-write the entire report and SQL statement, not ideal, hence me asking if what I am wanting to do is possible. <<

    Very often, it is ideal. It is like getting old; not much fun but the other option is being dead. Better to throw and re-do crap data and code than to kludge it year after year.

    Want to try again, with Netiquette?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •