I am having trouble creating a report model that caters for a role with One-Optional Many cardinality.
For example, Say you have a customer entity and an order entity and you want to allow users to build a report with a list of customers, and any order numbers of ordersÃÂ made by each customer.ÃÂ Say you want toÃÂ include those customers who have not made any orders.ÃÂ (ie an outer join expressed in SQL like Customers LEFT JOIN Order on Customers.CustomerId = Order.CustomerId)
If you create an order role within the Customer entity and set its cardinality to Optional Many, and set the cardiality of the related role (customer in the order entity) to One.ÃÂ To me this should mean that every order has exactly one customer and a customer can have zero to many orders.
If you then create a new report based on this model in Report Builder and drag the customer entity into the report, the customer entity becomes the Primary entity, but as soon as you drag any fields from the order entity into the report(other than an aggregate field), the order entity becomes the primary entity.ÃÂ This means that customers without orders will no longerÃÂ be displayed.ÃÂ ÃÂ I can findÃÂ no way for a report designer to prevent this within report builder.ÃÂ
As a model designer, you can work around this problem by selecting an incorrect cardinality for the Orders role.ÃÂ If you select the
View Complete Post