Grouping Data
You can group data by fields or expressions in data regions. In the Table and the Banded List, you can group details and add group header and footer rows and aggregate data.
The default scope of an aggregate function in a data region is the innermost grouping to which the report item belongs. You can specify the name of another grouping, dataset, or data region, or you can specify Nothing to choose the outermost grouping to which the report item belongs.
For more information on aggregates or subtotals, see Aggregates and Scope. In the Matrix, you can add dynamic columns and rows to group data. In the List, you can group detail data and even nest Lists for more levels of grouping. For more information on how to group data in each of the data regions, see Grouping in the How-To section.
Detail Grouping
Detail grouping is available in the List and Table data regions. It is useful when you do not want to repeat values within the details. When a detail grouping is set, the value repeats for each distinct result of the grouping expression instead of for each row of data. For example, if you use the Customers table of the NorthWind database to create a list of countries without setting the details grouping, each country is listed as many times as there are customers in that country. If you set the details grouping to =Fields!Country.Value each country is listed only once.
 |
Note: If the detail grouping expression you use results in a value that is distinct for every row of data, a customer number for example, you will see no difference in the results. |
Recursive Hierarchies
If you need to report on data in which all parent-child relationships are represented, you can create a recursive hierarchy. To do this, you need a unique ID field for the child to use as the Details Group, and an ID field for the parent to use as the Parent Group.
For example, if you have a dataset pulled from the NorthWind database with the SQL statement SELECT * FROM Employees, you can use the EmployeeID value as the group on expression, and the ReportsTo value as the parent group expression.
Please note that you can use only one group expression when you use a parent group.
To better visualize data in a recursive hierarchy, you can use the Level function:
Level Function
In a recursive hierarchy, you can use the Level function to indent text and further clarify the relationships between parents and children. To do this, you use an expression in the Left Padding property of the text box you want to indent.
For example, if you group on the EmployeeID value, and use the ReportsTo value as the parent group, you can indent the employee name text box based on the level by using the following expression in the Padding > Left property:
=Convert.ToString(2 + (Level()*10)) & "pt"
This gives left padding of 2pt to a top level employee (i.e. Vice President, Sales), 12pt for a second level employee (i.e. Sales Manager, Inside Sales Coordinator), and 22pt for a third level employee (i.e. Sales Representatives who report to a Sales Manager).
For more information, see Functions.
Sorting Data
Even after your employees in the above examples have been grouped and indented by level, you may still want to sort them by a field other than the default, which in this case is the EmployeeID. To do this, you can sort the data on an expression or a field. To alphabetize the employees on each level in the example above, you can set the Sort On expression to the value of the LastName field.
|
Note: The sorting behavior of Data Dynamics Reports differs slightly from that of our competitors. In other reporting solutions, the outer list, data region, or grouping directly affects the sort order of the detail records in the nested list, data regions, or groupings. In DD Reports, the sorting and grouping of the records is disconnected from one grouping level (or list or data region nesting level) to the next to honor the original order of the records coming in from the query. This offloads some of the sorting work to the server, which provides a performance benefit with very large numbers of records.
For example, if you have a parent list ("list1") with two sort fields (CustomerID, OrderData) and a child list ("list2") with no sort fields set, choose one of the following ways to get the same results as you would get with other reporting solutions:
|
For more information on how to sort data in each of the data regions, see Sorting in the How-To section.