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 (7)

  1. mpusateri says:

    I find the data navigation very confusing to an end user but very powerful for a power user. I have several different high level objects but once you drill down you have no idea or bread crumb (for lack of a better word) where you came from. So, there is no context as your example illustrates.

    Also, is there a way not to have to create a report model for the report builder to work.

  2. Bob Meyers20 says:

    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.

  3. bell.joshua says:


    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.

  4. jrivera says:

    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?


  5. ToniFielder says:

    I like the navigation.  It is a little confusing at first and hard to explain to my clients/users (this article helps), but once I get them over the hump so to speak, they seem ok.  Power users have an easier time with it.

    I do have a quesiton with respect to when/how report builder decides you cannot pull a field into the report.  I assumed once you navigated around so that your underlying query logic doesn’t make any sense (skipping relationships, etc.), then the UI doesn’t let you pull in a field – which is great.  But I am finding that I cannot pull fields in when I am pretty sure the underlying definitions should support it.

    Can you point me to some documentation that might help me figure out if I have a property (Cardinality?) tweaked a little incorrectly such that I am causing my model to "disable" in report builder in the wrong places?  


  6. 247Blogging says:

    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

  7. Richard Hansell says:

    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.