Creating Effective Summaries

The third key aspect of a good report after filtering and sorting is summarizing. Summarizing creates totals and subtotals that help the viewer of the report understand the data better. The following sections discuss various types of summarizing.

Creating Grand Totals

The simplest kind of summary is a grand total. This takes a single field and creates a total at the end of the report. To try this out, create a new report from the Orders table and add both the Order ID and the Order Amount fields onto the report.

Initially, this report is more than 30 pages long. A report of this length would make it very difficult to estimate the total amount of all orders, but a summary does that quite easily. Right-click the Order Amount field and select Insert, Summary from the context menu. This opens the Insert Summary dialog shown in Figure 3.6. To insert a summary, the first thing you need to specify is the field to summarize. Because you right-clicked the Order Amount field, this is already filled in for you. The next piece of information to fill in is the summary operation. The default is Sum, which is what you desire in this example, so leave it as is. Finally, Crystal Reports needs to know for which group the summary should be performed. Because there is no grouping in this report, the only option is Grand Total, which is already filled in for you. Click OK to close this dialog.

Figure 3.6. Inserting a summary based on the Order Amount field.

When looking at the end of the report, you see a grand total of the order amount is now visible in bold text. To edit the summary, right-click on it and select Edit Summary from the context menu. This opens the Edit Summary dialog. Try changing the calculation from Sum to Average. This now updates the summary to show the average order amount. There are various calculations to choose from including minimum, maximum, variance, count, deviation and median.

Besides the order amount total, it might be helpful to know how many orders there are. To do this, right-click the Order ID field and select Insert, Summary. Change the calculation from Sum to Count and click OK. Now besides the order amount summary, there is a count of all orders.

Creating Group Summaries

Although grand totals are useful, summarizing starts to become really powerful when it is applied at the group level. This enables totaling for each level of a group and tells more about the data than a simple grand total does because it measures the relationships between the various groups. To apply a group summary, a group must first exist in the report.

Using the same report from the last example with the Order ID and Order Amount fields, insert a group on the Ship Via field. This produces a report showing all the orders grouped by shipping method, for example, FedEx, Loomis, and so on. To compare the different methods of shipment, right-click the Order Amount field and select Insert, Summary. Previously, when you created a grand total, you accepted all the defaults in this dialog. But this time, the summary location needs to be changed. Change Grand Total (Report Footer) to Group #1: Orders.Ship Via in the Summary Location drop-down box, make sure the summary type is Average, and click OK.

Now a summary field is inserted into the report, which acts much like the grand total except that the average is repeated for each group. By examining these summaries, you can determine that the largest average order amount was shipped via UPS. You could also add a group-level summary to the Order ID field to determine the count of orders for each shipping method. Doing this reveals that the most orders were shipped via Loomis. These conclusions would have been difficult to reach without effective summaries.

Tip

When groups have many records inside of them, it sometimes becomes difficult to compare summaries because they aren't all visible on the page at the same time. A good tip for comparing these values is to hide the details section, which contains all the records, and only display the group header and footer that normally contains the group name and its summary. To hide the details section, move to the Design tab, right-click the Details bar on the left side of the screen, and select Hide.

Using Group Selection and Sorting

Following closely on the topic of group summaries comes group selection and sorting. These bring together both filtering and summarizing concepts. Group selection and sorting is to groups what record selection and record sorting is to records. In other words, defining a group selection or sorting defines which groups are included in the report and in which order, respectively. A key point to understand is that whereas record selection and sorting work from values of individual fields, group selection and sorting work from summary fields.

In the example from the previous "Creating Group Summaries" section, you created a report that displayed all orders grouped by the shipment method but to determine which shipment method shipped the highest dollar value of orders, you had to manually browse through the report comparing the numbers. Applying a group sort would provide an easy way to see the rankings. Also, what if you only wanted to show the top three shipment methods? Group selection provides a way to filter out groups in such a manner.

As you might expect, there is an expert for applying group selection and sorting. It's called the Group Sort Expert, and it can be found on the Experts toolbar, as well as from the Group Sort Expert item on the Report menu. When the Group Sort Expert is launched, it displays one tab for each group in the report. In the previous example, there was only a single group on the Ship Via field so that's what you should see. Inside that tab, there is initially only a single list box with a value of No Sort. Changing this list box to All displays a set of options very similar to that of the Record Sort Expertexcept instead of having a list of all report fields to choose to sort on, only summaries are listed.

The Group Sort Expert should have initially selected the Sum of Orders.Order Amount summary field and selected Ascending order. In this case, because it's more useful to see the highest dollar value first rather than last, change the sort order to Descending. Clicking OK closes the Group Sort Expert and returns focus to the report, which should have re-ordered the groups from largest to smallest. It's easy to see now that UPS was the method that shipped the highest dollar amount because it is the first group to appear.

There are only six shipment methods, but you can imagine reports that contain many more groups than six. Even if the groups are sorted, sometimes it's just too much data for the consumer of the report to absorb. To solve this problem, you can apply a group selection. To do this, launch the Group Sort Expert and change the All option on the left to Top N. Notice that the options are different from sorting. Applying a Top N selection implies that the groups will be sorted, but enables you to only display a specified number of the top groups in order. The default value is 5: Change this value to 3.

Note

New to version XI is the ability to set the N value of a Top or Bottom N sort to a formula. These formulas are created in the Formula Workshop accessed by the x+2 button beside the N value. This new functionality combined with parameters, covered in Chapter 5, "Implementing Parameters for Dynamic Reporting," enables improved report flexibility and allows report viewers to dynamically determine the N value of the Top/Bottom N at report-viewing time.

Another important option is relating to the set of groups that are excluded by the group selection. By default, these groups are all combined under a new group called Others. You might or might not want to include this Others group in your report. If you choose not to, uncheck the option labeled Include Others. Clicking OK returns focus to the report that now should only display the top three shipment methods based on the total order amount.

Note

Like the record selection, the group selection also has a formula that can be defined to use a custom expression to determine which groups to include in the report. The group selection formula can be found on the Report menu, under Selection Formulas, Group.

Some other options available in the group sort expert include Bottom N, which is the opposite of Top N, and Top and Bottom Percentage, which allow a filtering of the top x percent of groups.

Caution

It is instructive to note that group selection formulas are executed on the second pass of the Crystal Reports Engine. This second pass takes place after grand totals, group subtotals, and the group navigation tree have been created. To understand the nuances of multi-pass reporting, review the last topic in Chapter 4.

Creating Running Totals

The last kind of summary to be discussed in this chapter is a running total. In some older versions of Crystal Reports, to create a running total, you had to create a collection of formula fields, so a feature was added in version 9 just to handle running totals. To create a running total, follow these steps:

1.	Create a new report using the Orders table. Add the Order ID, Order Date, and Order Amount fields to the details section of the report. You can reformat the order date to a more user-friendly format if you prefer by right-clicking the field and selecting Format.
2.	Add a sort based on the Order Date field in ascending order. This report now shows all orders in the order they were placed. This is a perfect scenario for a running total that would show a cumulative total of orders so that the viewer of the report could see what the current total order amount was at any given time.
3.	To add a running total, right-click the Order Amount field and select Insert, Running Total from the Context menu. The Create Running Total Field dialog is shown in Figure 3.7. Figure 3.7. Creating a Running Total field is quickly accomplished through the Create Running Total Field dialog. Four pieces of information need to be provided in this dialog, including Name of the running total field The default is somewhat cryptic; it's best to give this a more meaningful name. The summary to perform The Field to Summarize should be pre-populated for you, but you can change the summary type from the default of sum to other standard summary types. Some of the more useful types for a running total are Count and Average. When to evaluate the running total The default and most common setting here is For Each Record, but this can be modified to only be evaluated when the value of another field is changed or a group value is changed, or you can define a custom formula that defines the evaluation criteria. When to reset the running total This setting determines whether the running total should reset itself. If no groups are present in the report, you'll likely want to keep the default of Never. But if you have groups, you might want to reset the running total for each group or define more complex criteria with a formula.
4.	For this example, give the running total a name of Cumulative Orders and leave all other settings at their defaults. Completing this running total adds this new field to the report next to the Order Amount field and provides a cumulative total of orders. The output of this report is shown in Figure 3.8. Figure 3.8. A cumulative orders report using a Running Total Field. Note Running totals can also be created from the Field Explorer by selecting the Running Total Field item and clicking the New button or right-clicking and selecting New from the context menu. Creating a field in this way does not automatically add it to the report; you need to place it on the report in a desired location yourself.