The philosophy behind Report Builder data navigation
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.
Comments
- Anonymous
October 27, 2005
The comment has been removed - Anonymous
November 02, 2005
I assume by "bread crumb" you mean the description of the path that I gave (i.e. "the products from each order detail of each order..."). You're right, we don't present a description like this to the user in this version, although something like this is definitely being considered for a future version.
We are also working on other ways to improve the experience for less-sophisticated users. - Anonymous
April 25, 2006
Bob,
I definitely think that this "bread crumb" functionality would add value to the application. If this path was always displayed, it could confuse SOME end users, but you could at least include a "Find Target" feature.
Perhaps the user could right click on the column (in design mode) and select "Find Target". The entity list in the Explorer pane could automatically navigate to the source for that field.
Another option would be to have a mouse-over effect that would display the full path of a field (path in the model).
Or perhaps the Advanced Mode would result in the fully qualified field name whereas the Basic Mode would just display the field name.
Anyway, these are just some thoughts. I have run into this issue before with common field names like Name. I didn't know from which table each Name field came. - Anonymous
November 12, 2006
Is it possible to navigate the entities the way you've described if you have a SQL Server 2000 database with no relationships at the database level? For example, I tried to build a model and create the relationships manually via the model designer. The problem is, when I run the Report Builder, I see my list of entities, but after I drag and drop a field(s) the entities list shows no additional items to select from. If I go back and add the relationships via the Enterprise Manager Diagram designer, the Report Builder Entity relationships return after adding the first field to the report. The problem I'm facing is that adding relationships at the database level, decreases application performance significantly (the application that relies on the DB). How can I get the Entity relationships to navigate, without defining relationships in Enterprise Manager?-Jose - Anonymous
June 10, 2007
The comment has been removed - Anonymous
May 19, 2008
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 - Anonymous
October 25, 2010
The interface is appalling and too confusing for anyone but power users to make any sense of. For example, I am in contracts, I browse to suppliers to list suppliers for each contract but instead I get a list of all suppliers and the contracts they are associated with... To my mind the interface is unusable anyway and I can never explain to end users why they get "random" letters and numbers when they run reports due to the decision by Microsoft to include "grouping items".Horrible, horrible mistake of an interface to my mind. It's a shame as it is a lost opportunity and lets the rest of the product down so badly.