Summaries

Aggregate functions

Predefining summaries in a catalog

Creating summaries in a catalog when needed

Summaries are a special kind of formulas that are built into JReport to save you time and effort. They can be used to generate aggregations for your data using aggregate functions such as Count, Average, Sum, and Standard Deviation. Summaries work based on how your data is grouped. For example, if you group your data according to country, and you have two groups (US and Canada), then when you add a summary to the country group, you will get the summary result for each group. The aggregation objects in a business view function the same as summaries.

This chapter introduces creating summaries in a catalog. In a report that uses business view as the data source, you can also create dynamic aggregations to use in the report specifically.

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

Aggregate functions

The aggregate functions JReport supports for computing data are generally divided 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 will compute the total of your checking account balance. Running functions when applied in crosstabs 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 will be 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 will return 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 will return 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 will return 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 will return 15.
Distinct Count
This function takes a total count of all the distinct fields in a report. For example, if you have forty three fields in your report and three fields have identical values, the value generated by Distinct Count will be 41.
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 as to use them directly in reports.

Open the required catalog.
In the Catalog Manager, expand the data source in which to create the summary, then:
- Select the Summaries node or any existing summary in the data source and click New Summary on the Catalog Manager toolbar.
- Right-click the Summaries node in the data source and click New Summary on the shortcut menu.
The New Summary dialog appears.
Select <Create> in the Summaries drop-down list.
Select a function from the Aggregate Function drop-down list for your field to be summed by.
Select a field for the summary to compute from the Resource box, then click next to Summary On.
If required, specify to which group the summary will be applied.
- If Static Summary is checked, select the field from the Resource box and click next to Group By. If the field you select is of Numeric/String/Date/Time type, you can further specify to which level data will be grouped by selecting a special function from the Special Function drop-down list. If Customize is selected, you can set the function by yourself in the Customized Function dialog.
- If Dynamic Summary is checked, first select Up or Down from the Group By drop-down list, then in the combo box, select a value or input an integer, which should be between 0 to 127, to specify the group on which the summary will take 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 input 0 in the combo box and insert this summary into any group, the summary will take effect on this group; if you select Up and input 1 and insert this summary to a group, the summary will take effect on the group higher than the group where the summary is inserted, and if you specify Down and 1, it will take effect on the lower group.
  
  The advantage of dynamic summaries is you can create one summary and use it on every group level. Using the example above with 3 groups and a final total, you still only need one dynamic summary instead of 4 static summaries since the dynamic summary can be used for each group and for the final total. Using dynamic summaries will enable you to keep a much cleaner catalog without hundreds of static summaries that can only be used in one report at one level.
Click the OK button.
In the Enter Summary Name dialog, provide a name for the summary and click OK. The summary is now created in the catalog.

Creating summaries in a catalog when needed

Besides predefining summaries in a catalog via the Catalog Manager, JReport Designer also provides you quick access to create summaries in a catalog from the UI where a summary list is provided, for example in the Resources box of the report wizard. In this summary list, the <New Summary...> item is available on the top. By clicking the item, you can create any summaries you want with the New Summary dialog, which are then added into the current catalog.

Summaries will also be created automatically in a catalog when you add summaries for each group while creating a query based table or banded object with the report wizard. However all summaries created automatically by the report wizard are static summaries, so when you use the report wizard to create a lot of reports in your catalog it will become very messy with many duplicate summaries. Therefore it is better to create dynamic summaries in the catalog and add them to the data components in your reports after you generate the data components with the wizards.

Previous Page Next Page