locked
PowerPivot: Reports using parent and child tables RRS feed

  • Question

  • Hi,

    Have a parent table with following Data.

    ChildName

    ChildName

    1

    Parent1

    2

    Parent2

    Child table with following data. ParentID(FK) is referenced to the above table.

    ChildID

    ChildName

    ParentID

    1

    Child 1

    1

    2

    Child 2

    1

    Both the above tables are in SQL Server.

    I would like to create a report having Parent Name and the child names in a grid like the one below.

    Name

    ChildName

    Parent1

    Child 1

    Parent1

    Child 2

    Parent2

    Child 3

    Parent2

    Child 4

     

    I imported both the values using the power pivot wizard and then created a flattened power pivot and pulled in Name from Parent Table to Rows fields and then dragged ChildName column from Child table on to the Rows Field.

    But I am getting a report where the data is duplicated irrespective of the foreign key constraint.

    Name

    ChildName

    Parent1

    Child 1

    Parent1

    Child 2

    Parent1

    Child 3

    Parent1

    Child 4

    Parent1 Total

    Parent2

    Child 1

    Parent2

    Child 2

    Parent2

    Child 3

    Parent2

    Child 4

    Parent2 Total

     

    Can you please suggest me how to handle this?

    Thanks and Regards,

    Manjunath


    manjunath

    Thursday, December 20, 2012 8:26 AM

Answers

  • Sounds like you need to create a relationship from your child table (ParentID) to the parent table (ID).  Typically, if the RI is defined in the relational database, PowerPivot recognizes it and brings it into the model.  But from the pivot behavior, sounds like that relationship doesn't exist.

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, December 20, 2012 2:41 PM
    Answerer