Create Aggregate Final Row in Grid

Introduction

This method involves using a Database SQL Query with a custom SQL query or a stored procedure to load data, ensuring the final row displays aggregated values.

How can you create a final row that displays the aggregated values of the rest of the rows? This is especially important when it comes to reporting. One can design the form such that it serves as a report rather than a means of data entry.

The final row is not a mere record row but serves as a summary of the record rows.

How does it work?

There are many ways to do this, but we will focus on the dataset data loaded from its data store. First, you can inspect how data is constructed.
The element in question here is a basic grid, let's click into it.

The grid is using a pair of identical load and store data store (which makes sense most of the time so that data is stored and loaded from the same source). We can opt to use Database SQL Query data store as the load data store so that we can gain full control on how dataset is returned and constructed.

With the ability to define our own SQL query, you can compose a query that will always return the last row as an aggregated values row.

SELECT c_name, c_quantity, c_price, c_request_id FROM app_fd_purchase_items WHERE c_request_id = ?
UNION
SELECT 'SUM', SUM(c_quantity), SUM(c_price), c_request_id FROM app_fd_purchase_items WHERE c_request_id = ?

This is a sample result by running them on a command line interface.

+--------+------------+---------+---------------------------------+
 
| c_name | c_quantity | c_price |  c_request_id                   |
 
+--------+------------+---------+---------------------------------+
 
| pen    | 1          | 10      | 1177_purchaseRequition_purchase |
 
| pencil | 2          | 20      | 1177_purchaseRequition_purchase |
 
| SUM    | 3          | 30      | 1177_purchaseRequition_purchase |
 
+--------+------------+---------+---------------------------------+

However, it does not actually play out well with the Database SQL Query data store as it is only expecting one parameter in the query. Our union query has 2 parameters.

In order to overcome this, you can create a stored procedure in the database instead.

DELIMITER //
CREATE PROCEDURE purchase_items_dataset(IN recordId CHAR(255))
BEGIN
  SELECT c_name, c_quantity, c_price, c_request_id FROM app_fd_purchase_items WHERE c_request_id = recordId
  UNION
  SELECT 'SUM', SUM(c_quantity), SUM(c_price), c_request_id FROM app_fd_purchase_items WHERE c_request_id = recordId;
END //
DELIMITER ;

With the stored procedure to return the appropriate dataset you need, you must call it from the Database SQL Query data store.

Expected outcome