locked
Can an attribute of one entity be included in another entity? RRS feed

  • Question

  • In SSRS 2008, I have database with a one to many relationship (parent -> child) and in my model I have created 2 entities: one for the parent and one for the children.  I want to know if there is a way to have attributes from the child entity show under a folder in the parent entity.  If I drag the the child attribute into a folder under the parent entity, I get an error when I build it because that child attribute is referencing a column that is not in the parent entity.  I have tried using Roles to reference the child entity, but I have a few issues with how that works.  Is there anyway to do what I am trying to do?

    Thanks,
    Rich

    Richard
    Friday, March 12, 2010 4:52 PM

Answers

  • Hi Richard,

     

    Based on my understanding, you want to add one attribute of the child entity into the parent entity when you are building the report model. If so, you can create a Named Query instead of the Parent table in the Model Data Source View, and in the Named Query expression, use the join functionality to add the attribute for the “Parent” table. The following are steps on how to do it:

     

    1.       Double-click the model Data source in solution explorer to open it, right click in its background and select “New Named Query”.

    2.       In the “Create Named Query” dialogue box, define its name and other things. In the Query definition box, type the SQL command to add the attribute into the parent table similar to this:

     

                           select p.ID p.Col1, p.Col2,p.Col3,c.theColumnFromChildTbl

                                                        from ChildTbl c inner join ParentTbl p

                                                                                           on c.ID = p.ID

     

    3.       Please click OK to close this box. Then, associate them by manual in the DSV diagram.

     

    After these steps, the child attribute is added in the “parent” entity.

     

    Please let me know if you have more question.

     

    Thanks,

    Jerry
    • Proposed as answer by Jerry Nee Friday, March 19, 2010 9:19 AM
    • Marked as answer by Jerry Nee Friday, March 19, 2010 9:22 AM
    Monday, March 15, 2010 8:26 AM

All replies

  • Hi Richard,

     

    Based on my understanding, you want to add one attribute of the child entity into the parent entity when you are building the report model. If so, you can create a Named Query instead of the Parent table in the Model Data Source View, and in the Named Query expression, use the join functionality to add the attribute for the “Parent” table. The following are steps on how to do it:

     

    1.       Double-click the model Data source in solution explorer to open it, right click in its background and select “New Named Query”.

    2.       In the “Create Named Query” dialogue box, define its name and other things. In the Query definition box, type the SQL command to add the attribute into the parent table similar to this:

     

                           select p.ID p.Col1, p.Col2,p.Col3,c.theColumnFromChildTbl

                                                        from ChildTbl c inner join ParentTbl p

                                                                                           on c.ID = p.ID

     

    3.       Please click OK to close this box. Then, associate them by manual in the DSV diagram.

     

    After these steps, the child attribute is added in the “parent” entity.

     

    Please let me know if you have more question.

     

    Thanks,

    Jerry
    • Proposed as answer by Jerry Nee Friday, March 19, 2010 9:19 AM
    • Marked as answer by Jerry Nee Friday, March 19, 2010 9:22 AM
    Monday, March 15, 2010 8:26 AM
  • Hi Richard,

    How about this issue? Basically, the issue should be able to be solved by my solution. So, i mark it answer. If you have any question, please feel free to let me know and unmark it.

    thanks,

    Jerry

    Friday, March 19, 2010 9:22 AM
  • Jerry...  Sorry for the delay, but for some reason I was not getting email notifications that anyone replied to this thread.  I just checked back and saw your reply.  I will try this and let you know what I find.

    Thanks again,

    Rich

     


    Richard
    Friday, March 26, 2010 10:28 PM
  • Please note that the approach Jerry describes will inflate the rows in the parent table to include all parent+child combinations.  This is often not the desired result; roles should be used for navigating one-to-many relationships instead.

    You may be able to achieve what you want by setting the ExpandInline property on the role from parent to child.  The user experience would be a little odd as using any of these fields would cause the query to recenter around the parent.

    I suggest trying both of these approaches (named query in DSV and ExpandInline on roles) to determine which is most appropriate for your scenario.

    Saturday, March 27, 2010 12:06 AM
  • Actually we've tried both and neither really works for us.

    We don't want to flatten the model because we don't want duplicate rows as you mentioned.  When we try to use “roles”, the user experience in Report Builder is very poor.  The tree hierarchy changes as users click around and select entities.  It's very difficult to navigate the model.  Is there any other approach that can be used?

    Thanks,

    Rich

     


    Richard
    Tuesday, April 6, 2010 2:47 PM
  • Actually we've tried both and neither really works for us.

    We don't want to flatten the model because we don't want duplicate rows as you mentioned.  When we try to use “roles”, the user experience in Report Builder is very poor.  The tree hierarchy changes as users click around and select entities.  It's very difficult to navigate the model.  Is there any other approach that can be used?

    Thanks,

    Rich


    Richard
    Tuesday, April 6, 2010 2:47 PM
  • The hybrid approach is to keep the individual entities and define a third entity which just does the OUTER JOIN between the other two tables. You will still get some repivoting of the tree in Report Builder but it should be more understandable in this case (e.g. you'll get Customer root if the rows of the query are currently Customers but switch to Customer Order root if the query changes to returning Customer+Order combo rows).

    I've outlined this approach in detail on this thread. Hopefully this helps.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/20d4b4fd-dc0b-428e-a5b8-aedf5c53d340

     

    Friday, April 16, 2010 1:01 AM