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 ordersmade by each customer. Say you want toinclude 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 longerbe displayed.I can findno 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 cardinality of the orders role in the customer entity as 'Optional one' and the cardinality of the Customer Role in the Orders entity as 'One'. When you produce the same report as above in report builder, the primary entity remains the entity that is dragged in first, so you can drag in the customer entity and then fields from order. The report will display all customers - any without orders will just have the order fields with null values. The main side effect of this is that the order count is no longer displayed in the click through report for the Customers entity.My question is - Is there some way to prevent report builder from changing the primary entity other than selecting an incorrect cardinality.

Unforutunately I'm not aware of any other way to enable the scenario that you are describing. However, we've had lots of feedback about the inabillity of Report Builder to allow to do left outer joins and we are looking at ways to enhance the product to meet this need.-pThis posting is provided "AS IS" with no warranties, and confers no rights.

Hi, As Patrick said, the scenario you described is not supported so far. Im afraid you have to create view and then import it to the models. Hope this helps. Raymond

More details on Raymond's response: you can create a named query in the Data Source View (.dsv) called CustomerOrder which does the Left Outer Join between Customer and Order and has roles to each. You can then use this entity in Report Builder to build reports with all Customers and Orders for those who have them.The named query definition can just be: SELECT c.CustomerID, o.OrderID FROM Customer c LEFT OUTER JOIN o ON c.CustomerID = o.CustomerID Then create a relationship between CustomerID and Customer table and one between OrderID and Order table.

Can anyone explain the rational for this? It would seem to me to be much more useful if the prime or base entity remained constant once selected and all selected relationships are then relative to the selected prime entity. I don't understand why it is necessary to change the prime entity when a relationship role is selected. In Adlan77's example above, if the customer entity is selected as the prime entity, then the orders 'role' should be a collection of orders belonging to the customer, rather than making the order entity the prime entity. If I want the customer associated with a specific order, then I would select orders as the prime entity in the first place. The Entity Framework handles these sorts of relationships with no problem, allowing easy navigation through relationships in both directions, why can't the model builder do the same?

The following blog posts may help you: http://blogs.msdn.com/b/bobmeyers/archive/2005/10/15/the-philosophy-behind-report-builder-data-navigation.aspx http://blogs.msdn.com/b/bobmeyers/archive/2006/01/20/grokking-report-builder-queries-this-is-not-your-mother-s-sql.aspx However, we do recognize the difficulties that some users have encountered with the report model navigation in Report Builder and we've been exploring solutions to make it both more intuitive and powerful. It certainly isn't the end-all-be-all query design interface yet :)

Hi, I am trying to connect multiple tables in Data Source view. Some are inner joins and some are left outer joins. If I use the Named query then the performance is very slow. Please let me know if there is any update on this. I have to convert report & models from cognos to SSRS. Thank you. shq

Named queries are still the best workaround for this issue. Consider profiling your queries against the database to see if there are indexes or other strategies you could use to increase performance. Also try keeping the join named queries simple, including only the PK-FK (as in the CustomerOrders example above).