Working with Summaries

Summaries are a special kind of formulas. You can use summaries to generate aggregations for your data using aggregate functions such as Count, Average, Sum, and Standard Deviation. This topic introduces the aggregate functions Report supports for computing data and how you can create summaries in a catalog.

Summaries work based on how you group the data. For example, if you group the data according to country, and you have two groups (US and Canada), then when you add a summary to the country group, you get the summary result for each group.

The aggregation objects in a business view function the same as summaries. In a report that uses business view as the data resource, you can also create dynamic aggregations to use in the report specifically.

This topic contains the following sections:

Aggregate Functions
Predefining Summaries in a Catalog
Creating Summaries in a Catalog on Demand

See also Summary Fields for how to work with summaries as component in a report.

Aggregate Functions

Generally, Report categories the aggregate functions it supports for computing data into two types: normal functions and running functions.

A normal function calculates all records and returns only one fixed value that is reset to 0 for each group.

A running function calculates a value on each record and does not reset to 0 for each group. It is like the total sum of money for each transaction that is recorded on your checkbook. Each time you deposit or withdraw money from the bank, the record computes the total of your checking account balance. When you apply running functions in crosstabs, they can only work on columns.

Count
Total count of all the values in a field. For example, if you have six non-null fields in a group, the value generated by Count is 6.
Sum
This function sums up all the numerical values in a field. For example, if a field contains 5, 10, 15, 20, and 25, the Sum function returns 75.
Minimum
This function finds the smallest numerical value in a field. For example, if a field contains 5, 10, 15, 20, and 25, the Minimum function returns 5.
Maximum
This function finds the largest numerical value in a field. For example, if a field contains 5, 10, 15, 20, and 25, the Maximum function returns 25.
Average
This function takes the average of all the numerical values in a field. For example, if a field contains 5, 10, 15, 20, and 25, the Average function returns 15.
Distinct Count
This function takes a total count of all the distinct values in a field. If a field has 43 values and one of them is repeating 3 times, the result generated by Distinct Count is 41.
Distinct Sum
This function calculates the sum of a field A based on all the unique values of a field B. If the field B has 43 values and Value1 is repeating 3 times, the unique values in the field B is 41 and Value1 is calculated once.
Population Standard Deviation
This function uses the following equation to compute its value. PSD takes the square root of the Population Variance.
Standard Deviation
This function uses the following equation to compute its value. SD takes the square root of the Variance.
Population Variance
This function uses the following equation. PV sums up the square of the difference between a value and its average, and then divides the result by the total number of values.
Variance
This function uses the following equation. Variance sums up the square of the difference between a value and its average, and then divides the result by the total number of values subtracted by one.
Running Count
This function takes a total count on all of the records of the values in a field. It does not reset on each group break.
Running Distinct Count
This function takes a distinct count on all of the records of the values in a field. It does not reset on each group break.
Running Sum
This function sums up all the numerical values on all of the records in a field. It does not reset on each group break.
Running Minimum
This function finds the smallest numerical value on all of the records in a field. It does not take the smallest numerical value of all the records in the query. The value you see is the smallest numerical value for the current and previous groups but not groups it hasn't read yet.
Running Maximum
This function finds the largest numerical value in a field on running records. It does not take the largest numerical value of all the records in the query. The value you see is the largest numerical value for the current and previous groups but not groups it hasn't read yet.
Running Average
This function takes the average of all the numerical values on all of the records in a field. It does not reset on each group break.

Predefining Summaries in a Catalog

You can predefine summaries in a catalog, so you can use them directly when designing reports.

Open the catalog.
In the Catalog Manager, expand the data source to create the summary, then do either of the following:
- Select the Summaries node or any existing summary in the data source and select New Summary on the Catalog Manager toolbar.
- Right-click the Summaries node in the data source and select New Summary from the shortcut menu.
Designer displays the New Summary dialog box.
Select <Create> in the Summaries drop-down list.
Select a field for the summary to compute from the Resource box, then select Add beside the Summary On text box.
Select a function from the Aggregate Function drop-down list to compute the selected field. If you select DistinctSum, you should select the ellipsis next to the Distinct On text box to specify one or more fields according to whose unique values to calculate DistinctSum using the Select Fields dialog box.
Specify to which group you want to apply the summary.
- If you select Static Summary, you can select the field from the Resource box and select beside the Group Bytext box. When the field you select is Numeric, String, Date, or Time data type, you can further specify to which level you want to group the data by selecting a special function from the Special Function drop-down list. If you select Customize, you can set the function by yourself in the Customized Function dialog box.
- If you select Dynamic Summary, first select Up or Down from the Group By drop-down list, then in the combo box, select a value or type in an integer, which should be between 0 to 127, to specify the group on which the summary takes effect.
  Normally, you want to display the value of the group where you use the summary, so leave the value 0. For example, if you select Up or Down and type 0 in the combo box and insert this summary into any group, the summary takes effect on this group; if you select Up and type 1 and insert this summary to a group, the summary takes effect on the group higher than the group where you insert the summary; if you specify Down and 1, the summary takes effect on the lower group; the rest may be deduced by analogy.
  The advantage of dynamic summaries is that you can create one summary and use it on every group level. Using the preceding example with three groups and a final total, you only need one dynamic summary instead of four static summaries, since you can use the dynamic summary for each group and for the final total. Using dynamic summaries enables you to keep a much cleaner catalog without hundreds of static summaries that you can only use in one report at one level.
  If you need a dynamic summary in a chart, when defining the summary, you must choose Down from the Group By drop-down list; if you want to use the summary in other data components, you must define the summary on Up level.
Select OK.
In the Enter Summary Name dialog box, provide a name for the summary and select OK. Designer adds the summary in the catalog resource tree.

Creating Summaries in a Catalog on Demand

Besides predefining summaries in a catalog from the Catalog Manager, Designer also provides you quick access to create summaries from the UI where a summary list is available, for example, in the Resources box of the component wizard. In this summary list, you can find the <New Summary...> option on the top. Select the option and you can create a summary in the catalog and use it for current scenario.

Designer also creates summaries automatically in a catalog when you add summaries for each group while creating a query-based table or banded object with the component wizard. However, the summaries created in this way are static summaries, so when you use the component wizard to create a lot of reports in a catalog, the catalog becomes very messy with many duplicate summaries. Therefore, it is better practice to create dynamic summaries in the catalog and add them to the data components in your reports after you generate the data components.

Previous Topic Next Topic