CALS

Aggregation Object

Unlike a Business Object, this object is used for data aggregation and summarization, such as in List Components.

It collects data from multiple tables based on a base table, groups the data according to specific criteria, and applies aggregate functions to generate new data.
This is primarily used to display summarized information on the screen.
While it has similar attributes to a Chart Object, it is used to present data in a list format rather than as chart information

[Table1. List of available components]

Name	Description
ToolbarList	A composite type that combines the Toolbar component and the List component.
ToolbarForm	A composition type that combines Toolbar component and Form component
Tree	It is a component used to represent menu structures and similar elements, and you must set the Objprop property in its Properties
List	It is a component that is used to represent the table-type data on the screen
Form	It is a component that is used to represent form-type data
Box	It is component for displaying data with a single item.
Schedule	It is a component that is used for displaying data in a calendar format.
Timeline	It is a component that is used to display data in a timeline format with dots and lines
Kanban	It is a component that is used to display data in Kanban format.
ToolbarGrid	A composite type that combines the Toolbar and Grid components
Grid	Component for displaying large amounts of data on the screen in table form

An Aggregation Object has fields with specific types, unlike a Business Object, to represent aggregated data.

Type	Description	SQL Example
Group by	Based on the aggregated field criteria displayed on the actual screen, the search is performed according to the displayed results. The conditions entered on the screen are structured as constraints in the Where clause based on the retrieved results.	Select * from (Select t1.a, t1.b, sum(t1.c) as sumC From table t1 Where t1.d = “DDD” Group by t1.a, t1.b ) t2 Where t2.a = “AA”
Search	A query criterion composed solely for grouping purposes, not displayed as results on the screen, but used only as a constraint for limiting. It is actually applied same a Where condition in SQL.	Select t1.a, t1.b, sum(t1.c) From table t1 Where t1.a = `AA` and t1.d = “DDD” Group by t1.a, t1.b
Aggregation	This field calculates values using aggregate functions and is displayed on the screen. It can be used in the Having specification, and the conditions entered on the screen are structured as constraints in the Where clause based on the retrieved results.	Select * from (Select t1.a, t1.b, sum(t1.c) as sumC From table t1 Where t1.d = “DDD” Group by t1.a, t1.b Having sum(t1.c) > 0 ) t2 Where t2.sumC > 1000

How to Create

Go to the Business Layer > Aggregation Object menu, then click on the New button in the Aggregation Object List to create a new Aggregation Object.
In Step 1, specify the Name, Description, and Table Name (Base Table) of the Aggregation Object.
In Step 2, you can enter the Search Spec, Having Spec, and Sort Spec, which allow you to limit, filter, and sort the data based on conditions. These are optional and can be omitted if not needed.
INFO
Search Spec Formula: [AO Search Field Name 1] = A or [AO Search Field Name 2] not in (BB,CC)
INFO
Having Spec Formula: [AO Aggregation Field Name 1] > 100
INFO
Sort Spec Formula: [AO Group by Field Name 1], [AO Aggregation Field Name 2] DESC
In Step 3, select the field to be designated as Group by type.
INFO
The fields selected in Group by are influenced by the order. The sequence is determined according to the selected order.
In Step 4, select the field to be designated as the Search type.
INFO
Select a field to be used only as a Search Spec in the Static Search or BO or Component of the List Component. In addition to the search conditions, you can also declare fields to be used in the Join spec.
In Step 5, select the field to be designated as the Aggregation type.
INFO
In the AO creation step, the selected Aggregation Type is created in the AO Field with the DB Function Flag set to No. Therefore, it is listed in the available field list for the DB Function Expression. You can then select the fields to use in expressions or in Join Specs
After checking the selected AO information in the final step and clicking [Confirm], the basic properties below are selected.
[Optional] If you want to join another table besides the Base Table to create the Aggregation Object, you can create new join information through the Join List [New].
WARNING
If you're using a table with a specified schema as the Base Table, you need to select a table with the schema specified in the Table Information. Tables with no Data Source and Schema information specified by default will require you to create a new table and specify the schema for use.
[Optional] You can create the connection information between the Base Table (specified in Step 1) and the Join Table (specified in the Join) in the Join Spec List. When selecting the connection fields from the Base Table, you can set the Aggregation Type to "Search" or specify "Aggregation" with DB Function Flag = "NO" to ensure that it doesn't affect the aggregation results.
You can register additional fields to be used in aggregate functions. When registering, specify Aggregation Type as Aggregation and set DB Function Flag to NO.
[AO Field List]
[List of AO fields displayed in DB Function Expression]
Once all necessary tables and fields are configured, you can verify the suitability of the constructed AO by validating the SQL using the "Check SQL" button.
1. When you initially press the button, the SQL is constructed based on the registered Meta information (Fields, Joins, Join Specs) of the BO or AO. After that, the constructed SQL statement is saved and displayed using this information for subsequent use
1. If there are changes in the Meta information and you need to recheck the SQL, you can reconstruct the SQL statement using the [Refresh] button.