Data Aggregation: To Retrieve Aggregated Data

The following activity will walk you through the process of retrieving aggregated data in a fluent BQL query for an inquiry form.

Story

Suppose that you need to give users the ability to view subtotals for orders of each status on the Open Payment Summary (RS401000) inquiry form. You need to add a filtering parameter that is represented by the Show Unpaid Subtotals check box on the inquiry form. If the check box is selected, the results in the grid need to be grouped by order status, and the subtotal amounts need to be calculated and shown for each status. You also need to update the dynamic queries in the data view delegate so that the appropriate query is triggered, depending on the state of the filtering parameter.

Process Overview

In this activity, for the Open Payment Summary (RS401000) inquiry form, you will add the ability to view grouped data in the grid and subtotal amounts by performing the following steps:

Adding a new filtering parameter to the inquiry form (the Show Unpaid Subtotals check box)
Modifying the dynamic queries in the data view delegate
Testing the aggregation of data on the inquiry form

System Preparation

Make sure that you have configured your instance by performing the Test Instance for Customization: To Deploy an Instance for Developing Inquiry Forms prerequisite activity. Also, be sure that you have completed the steps described in the following prerequisite activities:

Step 1: Adding the Check Box

To add the Show Unpaid Subtotals check box, do the following:

In the RSSVWorkOrderToPayFilter DAC, add the field shown in the following code.

        #region GroupByStatus
        [PXBool]
        [PXUIField(DisplayName = "Show Unpaid Subtotals")]
        public bool? GroupByStatus { get; set; }
        public abstract class groupByStatus :
            PX.Data.BQL.BqlBool.Field<groupByStatus>
        { }
        #endregion

Build the project.
Add the check box to the Content section of the RS401000.aspx file that defines the Selection area by using the following code. (Add the check box after the ServiceID field.)
```
      <px:PXCheckBox CommitChanges="True" runat="server" 
        ID="CstPXCheckBoxGroupByStatus" DataField="GroupByStatus"/>
```
Publish the customization project.

Step 2: Modifying the Data View Delegate

In the detailsView delegate, you need to use different queries, depending on the state of the Show Unpaid Subtotals check box. If the check box is selected, you need to group all the selected data by status and calculate the subtotal amounts to be paid for each status.

To group or aggregate records, you will append the AggregateTo<> clause to the statement and specify the grouping condition and aggregation function by using the GroupBy clause and the Sum aggregation function. To modify the data view delegate, do the following:

Replace the detailsView delegate with the following code.

        protected virtual IEnumerable detailsView()
        {
            BqlCommand query;
            var filter = Filter.Current;
            if (filter.GroupByStatus != true)
            {
                query = new SelectFrom<RSSVWorkOrderToPay>.InnerJoin<ARInvoice>.
                    On<ARInvoice.refNbr.IsEqual<RSSVWorkOrderToPay.invoiceNbr>>.
                  Where<RSSVWorkOrderToPay.status.IsNotEqual<
                      RSSVWorkOrderWorkflow.States.paid>.
                    And<RSSVWorkOrderToPayFilter.customerID.FromCurrent.IsNull.
                      Or<RSSVWorkOrderToPay.customerID.IsEqual<
                        RSSVWorkOrderToPayFilter.customerID.FromCurrent>>>.
                      And<RSSVWorkOrderToPayFilter.serviceID.FromCurrent.IsNull.
                        Or<RSSVWorkOrderToPay.serviceID.IsEqual<
                          RSSVWorkOrderToPayFilter.serviceID.FromCurrent>>>>();
            }
            else
            {
                query = new SelectFrom<RSSVWorkOrderToPay>.InnerJoin<ARInvoice>.
                    On<ARInvoice.refNbr.IsEqual<RSSVWorkOrderToPay.invoiceNbr>>.
                  Where<RSSVWorkOrderToPay.status.IsNotEqual<
                      RSSVWorkOrderWorkflow.States.paid>.
                    And<RSSVWorkOrderToPayFilter.customerID.FromCurrent.IsNull.
                      Or<RSSVWorkOrderToPay.customerID.IsEqual<
                        RSSVWorkOrderToPayFilter.customerID.FromCurrent>>>.
                      And<RSSVWorkOrderToPayFilter.serviceID.FromCurrent.IsNull.
                        Or<RSSVWorkOrderToPay.serviceID.IsEqual<
                          RSSVWorkOrderToPayFilter.serviceID.FromCurrent>>>>.
                    AggregateTo<GroupBy<RSSVWorkOrderToPay.status>,
               Sum<ARInvoice.curyDocBal>>();
            }
            var view = new PXView(this, true, query);
            foreach (PXResult<RSSVWorkOrderToPay, ARInvoice> order in
                view.SelectMulti(null))
            {
                if (filter.GroupByStatus == true)
                {
                    ((RSSVWorkOrderToPay)order[0]).OrderNbr = "";
                    ((RSSVWorkOrderToPay)order[0]).PercentPaid = null;
                    ((RSSVWorkOrderToPay)order[0]).InvoiceNbr = "";
                    ((ARInvoice)order[1]).DueDate = null;
                }
                yield return order;
            }

            var sorders = SelectFrom<SOOrderShipment>.InnerJoin<ARInvoice>.
                On<ARInvoice.refNbr.IsEqual<SOOrderShipment.invoiceNbr>>.
                Where<RSSVWorkOrderToPayFilter.customerID.FromCurrent.IsNull.
                Or<SOOrderShipment.customerID.IsEqual<
                    RSSVWorkOrderToPayFilter.customerID.FromCurrent>>>.
                    View.Select(this);
            foreach (PXResult<SOOrderShipment, ARInvoice> order in sorders)
            {
                SOOrderShipment soshipment = order;
                ARInvoice invoice = order;
                RSSVWorkOrderToPay workOrder = ToRSSVWorkOrderToPay(soshipment);
                workOrder.OrderType = OrderTypeConstants.SalesOrder;
                var result = new PXResult<RSSVWorkOrderToPay, ARInvoice>(
                    workOrder, invoice);
                yield return result;
            }
        }

In the code above, first you have declared the query variable. You will use it later to assign a query that depends on the filter value. Then you have obtained the current value of the filter. You have assigned a value to the query variable that depends on whether the Show Unpaid Subtotals check box is selected. If it is selected, you construct a query in which you group data by the Status field value by using the AggregateTo clause.

In the AggregateTo clause, you have grouped data by the RSSVWorkOrderToPay.status value, and calculated the sum for each group. Then you have returned the results of the query.

In the grid, you have not displayed values in the Order Nbr, Percent Paid, Invoice Nbr, and Due Date columns for aggregated data, because these values are not relevant to the subtotal rows.

Build the project.

Tip: No sales orders are grouped because the Status column displays no data for sales orders. This happens because the sets of statuses for sales orders and repair work orders are different, and a status of a sales order cannot be converted to a status of a repair work order.

Step 3: Testing the Aggregation of Data

To test how data is grouped and calculated on the Open Payment Summary (RS401000) form, do the following:

In the Selection area of the Open Payment Summary form, select the Show Unpaid Subtotals check box.
The form should look as shown in the following screenshot.
Figure 1. The filtered and aggregated data on the Open Payment Summary form

Notice that the Balance column now displays the total amount to be paid for all orders of the status displayed in the Status column. The values of the Balance column will remain unaffected for the rows that do not have a value in the Status column.