Getting grouping right in Report Builder

One thing Report Builder users need to be aware of when building a report is the difference between an entity group, which displays exactly one instance for each row in the underlying table, and a value group, which displays one instance for each distinct value of a particular field. The type of group they get depends on the field(s) they used to create the group initially. Problems can arise if the user intends to create an entity group with many fields, but instead creates a series of value groups (one for each field). This may make it impossible to get the sorting they want, and can introduce unwanted subtotals levels as well.

For example, a user might drag in the Last Name field as the first step in creating an Employee report. However, if the DiscourageGrouping property for this field in the report model is "false", dragging in this field creates a value group on Last Name. This means that the group cannot be used to display data about individual employees; it can only show totals for all employees with a given last name. So, other fields that are not totals (e.g. First Name) must be added to a new group. If they also do not discourage grouping, a value group will be created for them as well. And so on...

The safest way for the user to get an entity group when they really want one is to drag in the entity itself from the entity list, instead of dragging in a field first. This will create an entity group displaying the DefaultDetailAttributes or IdentifyingAttributes for that entity. This is also a convenient shortcut for adding the fields they almost certainly want to include anyway. As the developer of the report model, you can help users who are unaware of this trick by setting the DiscourageGrouping property in the report model on any field for which users are unlikely to want a value group. They can still get one in their report by using a custom field that simply references it, but the default behavior will give them an entity group instead.

Comments

• Anonymous
  May 10, 2007
  The comment has been removed
• Anonymous
  June 07, 2007
  Bob, thanks for clearing up this issue a bit. I'm still a bit confused though - and have to admit that I don't exactly grasp the concept of the property DiscourageGrouping in the Model Designer. The effects of turning on (DiscourageGrouping = True) are that when I in Report Builder drag an attribute with this property set, it automatically groups (looking at the grey tab) with whatever comes next. Shouldn't this be the other way around? And when I set attribute's properties to DiscourageGrouping = False, they are not possible to group in Report Builder whichever way I try to do this. The result of this is that I cannot sort (neither dynamic sorting, nor using the Sort and Grouping-button in Report Builder. Am I missing something here?
• Anonymous
  September 28, 2007
  Since releasing Report Builder almost two years ago, we've found that many users run into difficulty
• Anonymous
  June 01, 2008
  Hi Bob,I got a problem with grouping and sorting in Report Model based reports. My szenario is quite simple. I got a table with 5 colums: Group1, Group2, Detail, Sort and Value. In Sort, I have the sort order for the fields Group1 and group2, like this:C,#null#,a,50,0C,C,x,100,1C,C,y,200,1A,B,z,200,2B,D,x,300,3etc.In my report, I want to use group1 and group2 as groups but want them to be sorted as defined in sort.The result should beC            a        50C    C       x       100    C       y       200    C       total   300C    total           350A    B       z       200    B       total   200A    total           200B    D       x       300    D       total   300B    total           300In the Report Model, I defined "sort" as the entities sort attribute.But in the reports that I create based on the model, sorting does not happen. I found no way to sort the groups by my (only) sort attribute...Do you have any idea how to sort the data in my report by the one Sort-attribute I have AND keep the groups?YoursDirk