次の方法で共有

When and why the primary entity changes when using a report model

  • [アーティクル]

Got a few questions lately on this subject, so I thought I’d post some additional info that may be helpful.

First, a few related links that may be partially helpful:

Now for the full response.

As stated in the MSDN article linked above, the primary entity represents the “context” or “root” of the report or query. Those familiar with OLAP may like to think of it as the central “fact table” with a host of related “dimensions”. It is the set of detail data to which all grouping and total values are related. It is also the context in which the report filter is defined. In short, all data shown in the report is related somehow to the primary entity.

So, how does one choose the primary entity? Well, you don’t – at least, not directly. When you add the first field to your report, its containing entity becomes the primary entity. All other entities are then rearranged into a tree to show how they are related to the field you selected.

As you add more fields to your report, or change the report filter, the primary entity may occasionally shift. This only occurs in the following cases.

1. You add a field indicating you want to display a lower level of detail.

For example, suppose you started your report by adding a couple of Customer fields – Customer Name and Phone. These both belong to the Customer entity, so Customer becomes the primary entity for the report.

Next, you click on the related entity Orders, and add Sum of Sales Amount. Because this is a total, it does not indicate you want to display a lower level of detail, so the primary entity is unchanged. Also, because your report is primarily “about” Customers at this point, data about Orders with no related Customer is not displayed.

Suppose you then add Order Month from the Orders entity. Order Month is not a total, and a Customer can have many Orders with many different Order Months, so adding this field indicates you want to display a lower level of detail. To accommodate this, the primary entity shifts from Customer down to Orders.

As a result, the report now displays data about Orders and their related Customers, instead of Customers and their related Orders. This may seem like a subtle distinction, but it has some important effects. One is that Customers with no Orders are no longer included, as they were when the report was only about Customers. Conversely, data about Orders with no Customer are now included, though they were not previously. Another effect is that the report filter is now evaluated in the context of Orders instead of Customers. The impact of this will become clearer after the next point.

2. You add a filter suggesting the overall report is about a lower-level set of detail data.

In the example for #1, suppose that instead of displaying total sales for each Order Month, you wanted to filter the report to display only the sales for a particular Month or a particular Store. Typically, this means two things: (1) you want the totals to include only the Orders from that Month or Store, and (2) you only want to see the set of Customers that actually have Orders from that Month or Store (as opposed to a list of all Customers).

When you want both of these restrictions applied (and by default it is assumed that you do), this suggests the report is no longer “about” Customers, but rather about the lower-level set of detail data that relates the Customers you’re showing and the filter you’re trying to apply. In this case, the lower-level data that relates Customers and the filter you’re trying to create is Orders. Hence, the primary entity shifts from Customer down to Orders.

Note: To filter the totals without filtering the set of Customers displayed, you can apply a filter directly to the Sum of Sales Amount field, instead of applying the filter to the entire report. Conversely, to filter the set of Customers without filtering the total sales displayed, you can temporarily uncheck the little checkbox in the report filter dialog. See the related blog post linked above for more information.

3. (Less common) You add a group field to a report that contains only grand totals.

Suppose you clicked on the Orders entity first, and added Sum of Sales Amount. At this point, the primary entity is Orders, but if you run the report, all you see is a grand total of Sales Amount across all Orders. The grand total has not be broken down, or “grouped by” anything yet.

If you then add Customer Name (which is not a total) to the report, this will cause the total you added previously to be broken down or grouped by the new field. At this specific transition point, i.e. when you go from having all totals to at least one group field, the primary entity may shift up, in this case from Orders to Customer. The reason for this shift is to ensure you get the same result you would have gotten if you had added the same fields in the reverse order (e.g. Customer Name, then Sales Amount).

Other than this one case (#3), the primary entity shifts only incrementally in one direction – down – and only as needed based on the fields and filters you’ve selected. In most cases, this behavior provides the most stable experience and most intuitive results for your report. However, when repeatedly adding and removing many fields to/from a report, you may occasionally get into a situation where the primary entity has shifted too far down a particular path, which may make it harder to get a specific result. In that case, removing and re-adding your group fields should move things back into the desired state.