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 will discuss various types of summarizing. Grand TotalsThe 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 on to 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 brings up the Insert Summary dialog as shown in Figure 7.6. To insert a summary, the first thing that needs to be specified 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 you'll leave as its default. 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 7.6. Inserting a summary based on the Order Amount field.When looking at the end of the report, a grand total of the order amount will now be visible in bold text. To edit the summary, right-click on it and select Edit Summary from the context menu. This brings up the Edit Summary dialog. Try changing the calculation from Sum to Average. This will now update 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. Group SummariesAlthough 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 the method they were shipped with, 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, and click OK. Now a total field is inserted in to the report, which acts much like the grand total except that the total is repeated for each group. By examining these summaries, it can be determined that the largest 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 an effective summary.
Group Selection and SortingOn the topic of group summaries comes group selection and sorting. This brings together both filtering and summarizing concepts. Group selection and sorting is to groups what record selection is to records. In other words, defining a group selection or sorting defines which groups will be 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 "Group Summaries" section, you created a report that displayed all orders grouped by the shipment method: But in order 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 Expert except 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 allows you to only display a specified number of the top groups in order. The default value is 5: Change this value to 3. Another option important to understand 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.
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. Running TotalsThe last kind of summary to be discussed in this hour is a running total. In some older versions of Crystal Reports, to create a running total, a report developer had to create a collection of formula fields, so a feature was added just to handle running totals. To illustrate this, follow the next few steps:
Four pieces of information need to be provided in this dialog, including
|