The top part of the Explorer pane in Report Builder is probably unlike anything most people have ever used. It starts out as a flat list of entities — big “things” in your database like Customers, Orders, Products, etc. — but then rearranges itself once you’ve added the first field to your report into an auto-collapsing, recursive tree. What’s really going on here? Why the new-fangled interface?
Well, the first issue is the peculiar role that the root of the tree plays in building your report. Whichever entity ends up as the root is the “primary” entity of your report. One way to think of this is that the primary entity is the focus of your report, e.g. this report is fundamentally about Customers. Thus, having selected that entity, all other data in your database is represented via its relationship to that entity. Database folks will quickly latch on here and suppose that the navigator is just displaying the tables and relationships in the underlying database. This is not quite true, and it misses the key to the navigator’s power.
The key concept is that the nodes immediately below the root are not just the related entities. They represent the specific instances of that entity that are related to a given instance of the root entity. Thus if Customer is the root entity, the Orders node immediately below it does not represent the Orders entity in general; it represents the orders placed by a given customer.
A good analogue would be a paper filing system with a card for each customer, followed by a folder of orders placed by that customer. There is no folder that contains all Orders — there is just Customers and their Orders.
This explains why adding Customer->Name to your report, and then Customer->Orders->#Orders, results in a report that shows the Name of each Customer with the # of Orders for that customer. No joining or filtering is necessary to constrain #Orders. It is all implicit in the navigation.
The fun really starts, though, when you realize that you can go anywhere using these relationships (including back to the same entity, because the instances will be different), and the path you use fully describes and constrains exactly what data you are referring to. For example, the path Customer->Orders->Employee->Territory->Employees->Orders->OrderDetails->Products represents the products from each order detail of each order taken by the employees in the same territory as the employees that took the orders of this customer. Now, that particular example may not be very useful in a practical sense, but you get the idea.
This also explains why one should be very careful not to just drag in fields from any old node in the tree that looks like it might be what you’re looking for. The instances of that entity to which your fields are referring will depend on the path you used to get there.
I’m curious what others think of this approach. Please comment or drop me a note if you have some thoughts you’d like to share on the subject.