One-to-Many relationships and parent/child records

[ry1633]

Hi all,

In Framework Manager I have two views:  RESTRICTIONS and PRODUCT_LICENSE.      I have them tied like this:   

RESTRICTIONS_VIEW (field: PRODUCT_LICENSE_ID, 1:1)   <----------->   PRODUCT_LICENSE_VIEW (field: ID, 1:n)

I have a user who seems to have a problem.   They want the report to display all "parent" records and then if there are no corresponding child records for that parent, it is just blank where the child record value would be.  An example would be product license restrictions.  They want the report to show all product licenses, whether or not there are associated restrictions for a given license.  If the product license is unrestricted, the report would just be blank in the restrictions column for the row containing that product license.

Right now, they can only seem to generate reports for those product licenses that ARE restricted.  The rest of the product licenses don't show on the report at all.     

I have a modeling issue between the two views?

[bdbits]

Yes, the current relationship is going to generate an inner join. You need to allow for an outer join, so change the 1:n to a 0:n in the join properties. If you watch the relationship impact verbiage at the bottom of the properties dialog, you can see the effect of the change.

[ry1633]

yeah I've tried it with Left, Right, and Full OUTER JOINS, and I think Full Outer Join (0:n on both sides)  might work the best but I'm not sure how it would impact what the users sees, negatively or not.

I'd like to also be able to pull Licenses without (or blank) Restrictions.

[cognostechie]

Changing from inner to outer would result in the existing reports showing more data than what they currently show. Restrictions without Product Licenses and vice versa might start showing in the reports which they currently do not see. Going forward is easy to predict but the problem will be in the existing reports showing it differently than what they are used to so that is something you might have to consider.

Cognos does not force the joins so if the user wants to see only the Product Licenses then they can simply make a report with items from Product License without putting anything from Restrictions. if the requirement is that they want to see restrictions also then you would have to think about what strategy to adopt.

You can also ask them to use Business Insight Advanced which allows to put multiple queries on the same screen so that way they can make a report to show all product licenses and another report to show Licenses and restrictions. That way they will be able to identify which ones do not have restrictions.

Just thoughts..