Quite often when you build out a report it can be helpful to be able to access specific information about the numbers - whether that’s a running count of contacts in a list, a sum of invoice totals, a percentage of gross earnings, or any other information valuable to your business. The summary function in Report Designer can be used to generate just these sorts of numbers in a variety of different ways. This section of the documentation will run down two examples of how summaries can be used: a basic example to illustrate the fundamentals of using summaries to influence a whole report, and a more advancedexample showing how summaries can be used within individual elements of a larger report. We’ve also included a screen overview of the Summary Editor as a helpful legend for the fields used in that screen!
How to apply a summary (basic)
In order to illustrate how a summary is used, let’s assume you’d like to tally the total number of contacts in a given list. To do this, let’s refer back to the basic report we created in Creating a new report. Follow the steps there to build a basic contact list.
- Once you have done so, drag and drop a label into the Page Header band. If this is a “real” report you may choose to style the label as desired (please see Styling for more information).
- Click the arrow button to bring up the additional options for the label and select Data Binding. This drop down menu will display all the tables associated with the Master Table you selected when you created the report. To meet our goal for this example, choose Contacts > Contact.Name.
- Click the ellipses button [...] next to Summary to bring up the Summary Editor. This pop up will allow you to specify the details of what you want your summary to do.
- You will notice Bound Field is auto-filled to reflect the choice you made in Data Binding, so leave it alone. Instead, click on the Summary Function drop down menu. The default choice on this menu is “Sum”, but for the purposes of this example, choose “Count”. This will prompt the report to provide a total number (or raw count) of contacts in your report.
- The Format String determines how the information will be displayed. Click the ellipses button [...] next to Format String to bring up the FormatString editor. Under the General category, you will see a Prefix and Suffix field. These fields are used to append text before or after the value of the summary. For this example, you might wish to write a prefix like “This list contains” and a suffix that reads “contacts in total”. When we’re done creating this summary and we look at the preview menu, the label will read “This list contains X contacts in total”, where X is the number generated by the summary. These fields are totally optional: you can include only a prefix (e.g. “# of contacts =”), only a suffix (e.g. “contacts in this list”), or neither (only the number will be shown in the label). When you’re satisfied, click Okay. This will return you to the Summary Editor.
- Under Summary Running, you’ll have to choose the scope of the summary - this will determine the span of data to be included in the calculation. For our example, select “report” which will prompt the summary to count every single contact in the whole report (for more information on the other options, see our screen overview). Click Okay to continue.
- When you click the Preview button, you will see the summary has generated a total number of all your contacts in this list in the Page Header band, expressed the way you set it up (for example, “This list contains 35 contacts in total”). Please note: if you have more than 100 contacts in this list, the preview will only read a maximum of 100. However, the final report will display an accurate number, no matter how many contacts you have. And that’s it! You’ve used a basic summary to generate information about your report. Now, let’s take a look at a more complicated example.
How to apply a summary (advanced)
In order to illustrate a different way to use summaries, let’s assume you’d like to find out the total balance for all invoices associated with each customer in a list. To do this, let’s refer back to the detail report we created in Detail reports. Follow the steps there to build your contact list with invoice details.
1. In the Detail Report band, right click and choose Insert band > Group footer. Then drag in a label you’ll use to create the summary.
Note: You may find it helpful to drag in other labels and lines to provide context to this information so it makes sense with your existing report. Please see Styling for more information on how to use styling tools.
2. On the summary label, click on the arrow button and then click on Data Binding. From the table menu, select Customer > InvoiceCustomer > Amount. (For more information on tables, and why this example calls for this specific field, please see our documentation on Detail reports).
3. The Summary Function drop down menu is defaulted to sum, and since we want to determine the total dollar amount of all invoices for each customer, we will leave it as such.
4. Click the ellipses button [...] next to Format String to make sure the number we generate is expressed as a dollar amount. Under Category, click Currency, and in Standard Types, click on $0.00 to ensure the amount will be expressed up to two decimal places. Click Okay to return to the Summary Editor.
5. Under Summary Running, click on Group. This will ensure the total generated will apply to each individual group (which means a total will be generated for all invoices attached to each customer individually since we grouped by customer). Click Okay to continue.
6. When you click the Preview button, you will see the Summary has displayed a total amount for each customer based on the invoices attached (for example, if Bob Crenshaw had three invoices of $250 each, the summary will display a total of $750.00 for Bob).
This illustrates the versatility of the Summary tool in Report Designer, which can be used to summarize information from a variety of sources within a report in a number of ways.
In this section we will break down the two Editor screens used to create summaries and identify the unique characteristics of each.
This editor is used to determine the parameters of the summary data - from which table the data is being drawn, and which parts of the report the summary will be applied to.
- Bound Field: This is another way to identify which table you’ll be using to create the summary - it does the same job as Data Binding, so if you choose your table in Data Binding, you can usually leave this alone. Please note that if you choose your table from the Bound Field menu, the General category in the FormatString Editor will not appear.
- Summary function: This drop down menu lets you choose the action you want the summary to take - what you want it to do with the information from the table. The most common options for general reports are sum (add up the numbers and provide a total - this is also the default function), avg (provide an average of the numbers), count (tally the total of individual numbers included in the data set, as in our first example above), and percentage (express numbers as percents of a whole).
- Format string: Clicking on the ellipses button [...] next to this option will bring up the FormatString Editor.
- Ignore all NULL values: This directs the summary to ignore any values of zero when calculating.
- Summary Running: These four options determine which part of the report the summary will be applied to. None means the summary will not be applied at all (basically it turns the summary off), Page means the summary will be applied at the end of every page of the report, Group means the summary will be applied to the information in each Group band, and Report will apply the summary to all the information in the entire report.
Format String Editor
This editor is used to format how the numbers expressed in a summary will appear in your report. There are six unique format categories available to you, each with a Standard Types and Custom tab. You can use the Custom tab to create your own display versions of each category, should the Standard options not meet your requirements.
- DateTime: This category allows you to express dates and times in a number of different ways (e.g. January 1, 2014 vs. 1/1/2014 vs. Sun. Jan. 1/14 etc.) The options are displayed in the menu when you click on Standard Types, and will display an example based on the type you choose using today’s date and time.
- Number: This category allows you to express numbers in multiple ways, including decimal points and exponents. General reports won’t make use of most of these options unless you are dealing with very large or very small numbers.
- Percent: This category allows you to express percentages either as whole numbers or as fractions using decimal places (e.g. 50% vs. 50.53%).
- Currency: This category allows to you express dollar amounts as whole numbers or as fractions using decimal places, as above. It also allows you to specify currency formatting as it relates to various currency symbols that may be dependent on the currency and/or computer regional settings (e.g. "c2" - uses regional settings). Be very careful to understand the implications of using any c formatting options for currency since it is unlikely you want to always change 100 DOLLARS to 100 POUNDS strictly based on a machine's regional settings.
- Special: This category allows you to express special number types, like phone numbers.
- General: This category allows you to write in the prefix and suffix that will appear around the number generated by the summary (as in step 6 of How to apply a summary).