Home > Articles > Data > SQL Server

This chapter is from the book

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 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.

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.

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

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 in the Summary Location drop-down box, and click OK.

Now a total field is inserted into the report, which acts much like the grand total except that the total is repeated for each group. By examining these summaries, you can 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.

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

On 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 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 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 enables you to only display a specified number of the top groups in order. The default value is 5: Change this value to 3.

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 just to handle running totals. To illustrate this, 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. 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 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.

  4. Figure 3.7Figure 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 prepopulated 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.

  5. For our 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.

  6. Figure 3.8Figure 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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020