What to do with IdentifyingAttributes and DefaultDetailAttributes?

SQL 2005 report models will ship with a curious and admittedly incomplete solution for handling "the information users typically want to see about an instance of entity X". The problem is that there are many scenarios for which the answer to that question differs:

Scenarios

1. Dropdown selection (applies to entities with a relatively small number of instances): The cramped interface and low number of distinct items leads users to want the smallest number of fields -- preferably one -- necessary to identify each instance.

2. Large list selection (applies to entities with a relative large number of instances): The more spacious interface and higher number of distinct items leads users to want a moderate number of fields that allow for multiple ways of identifying an instance.

3. Generating a drillthrough report for multiple instances of entity X: The fact that the report is fundamentally "about" instances of entity X leads users to want an even larger number of fields, including some that are very interesting/useful, though not necessarily identifying.

4. Creating a group on instances of entity X: The uncertainty about whether the report is fundamentally "about" instances of entity X leads users to want just enough fields to easily identify each instance (they can easily add more if they want them).

5. Model item security: Users will not get permission for an entity if they cannot see the canonical minimum set of fields required to identify an instance of that entity. To allow flexibility for granting permission to other fields, administrators want only the absolute minimum number of fields necessary in this set.

Options

In SQL 2005 there are only two collections defined on an entity that accommodate all five of these scenarios. IdentifyingAttributes is used for dropdown selection, large list selection, and model item security. DefaultDetailAttributes is used for generating drillthrough reports and creating a group on instances of entity X. [Note: Use of DefaultDetailAttributes always reverts to IdentifyingAttributes if the first is empty.]

Recommended Approach

Given the limited options and the sometimes conflicting pressure to add/remove fields from each of these collections, the following is my recommended approach.

IdentifyingAttributes should be as small as possible, preferably one field. The exception would be an entity that uses large list selection, which cries out for multiple fields to help the user identify each instance. In this case you should consider adding at most one or two more fields (e.g. including Name along with ID), but keep in mind that in doing so you are giving up the ability to expose these fields independently through model item security.

DefaultDetailAttributes, if used at all,should at most be only slightly larger than the set of fields in IdentifyingAttributes. If you have too many fields, you will quickly start to annoy users when they want to create a group on that entity, especially a chart group (which will display the concatenation of all of those fields in the data label for the corresponding category or series -- ouch).

For entities where the DefaultDetailAttributes selected above are just not enough to generate a decent multi-instance drillthrough report to that entity, use Report Builder to throw together a report with the fields you really want, enable the Drillthrough (to this report) option in the Report Properties dialog, save it to the server, and use SQL Management Studio to specify it as the multi-instance drillthrough report instead.

Comments

• Anonymous
  December 20, 2006
  One thing Report Builder users need to be aware of when building a report is the difference between an