Anticlue

Group your way to understanding

We have the tools to make an excellent detail reporter, but sifting through all that detail is grueling. In order to make areports usable tool, levels of categorization are needed. Categorizing the information is called grouping.

So let's start off by understanding how to insert a group into a pre-existing report. First, create a new detail report. From the Northwind database, have the fields Employee Last Name, Order ID and Order Date.

In order to create groups, click on the insert group icon. For this report group on the Employee's Last Name. Ascending order is good for our needs. Next click on options.

Under options, I would like to change the group field name. Instead of just the Employee's last name also include the employee''s number. So we choose to use a formula as the Group Name. We can prevent the employee from breaking across the middle of the page, because may we would like to give this report to each employee. So select to keep the group together, which prevents breaking information in the middle of a page. Since we have the possibility to cross many pages, repeat the group header on Each Page. Once the config settings are completed, click the formula icon to setup the formula.

The Formula Workshop Editor appears after clicking the formula buttom. In the code window, select the EmployeeID, concatenate a colon and then concatenate the employee's last name.

Probably by now you are beginning to comprehend the power of groups. So let's review a couple of cool features with groups. For our group we didn't want the employee's listed in alphabetical order. We wanted to specify the order. In our report, undo the last grouping and start fresh. In the InsertGroup dialog, select in specified order as indicated below. The specified order tab appears.

In the specified order tab, under name groups, select Peacock, Fuller, and Davolio. As indicated below. You will notice that there are up and down arrows to the left that indicate how to set the order. Finally an others tab appears, please select the others tab.

Under others we have a choice how to handle the remaining group categorizations, we can standardize them under the same name, we can leave them in their own groups,or we can just discard all others. For our purposes here, discard all others.

Now, when we go back to our report and click the preview tab. One notices that there are only Peacock, Fuller, and Davolio on our report.

Perhaps we wanted to add more than just one group on a report or we wanted to change the settings of our groups. The tool for the job is the Group Expert dialog box. For our report, maybe we would like to see the regions for which our employees are accepting orders. So we can add the customer region to our grouping selection, by highlighting the selection and selecting the single arrow to move this field across. Now we have a primary grouping of Employee Last Name and a secondary grouping of customer region. If we wanted to refine the customer region grouping, all we would need to do is click the options button and the change group options dialog box appears. If we wanted to change the order, it is a simple click of the up arrow.

As we head back to the drawing board, and get an idea of how to group on dates! To start off we create a simple detail report containing the order date, the employee who placed the order, company the order was for, and the order id.

Now we add a grouping on order date, we notice that the default date grouping is for each day. However for this report, try using a weekly period. When we click the drop down combo box, select for each week and apply. As we see crystal reports handles a lot of the grouping logic for time periods with dates for us.

In the real word data often has hierarchical relationship, hierarchical refers to a parent / child relationship. This can be seen in organizations under the manager employee relationship. This can be seen in healthcare, under the labs and reflex testing sequences.

So let's build a report with hierarchical grouping. One of the main items to remember is that eligible fields for a report have to be the same field type as the field that is currently grouped upon. Therefore it would be impossible to group on employee id, and then create a hierarchical grouping based upon windows username.

First, create a blank crystal report connected to the Northwind database.
Next, select the employee table. From the employee table create a report with the last name, title, and birthdate. Insert a group on the employee's ID, but instead of displaying the id, display the employee's last name.

We are now ready to apply a hierarchical grouping to the employee table. Under the reports menu item, select Hierarchical Grouping Options. The dialog box appears select the check box for Sort Data Hierarchically, and set the parent id field to whom the employee reports. Also set the Group Indent to .25 inches. Our completed dialog box appears below. When we preview the report, we can see the results.