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

  1. tbellinson says:

    Bob, could this be related to a problem I’m having with aggregates?  I created a calculated field (price * qty) in a line item detail table.  I want to aggregate that to the Item Code supporting table.  If I drag the (price * qty) field after ItemCode, it works fine, but if I create another field which uses the SUM function of the previously created field, I get some big numbers that don’t make any sense.  But it gets weirder, if I put the SUM field to the left of ItemCode, it works fine.  Only on the right does it do some strange grouping magic.  Thoughts?

  2. Gabszig says:

    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?

  3. Anonymous says:

    Since releasing Report Builder almost two years ago, we’ve found that many users run into difficulty

  4. hdb says:

    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,0

    C,C,x,100,1

    C,C,y,200,1

    A,B,z,200,2

    B,D,x,300,3

    etc.

    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 be

    C            a        50

    C    C       x       100

        C       y       200

        C       total   300

    C    total           350

    A    B       z       200

        B       total   200

    A    total           200

    B    D       x       300

        D       total   300

    B    total           300

    In 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?

    Yours

    Dirk