Announcement

**claudiobosticco** · 20 Nov 2024, 02:14

Also, if you use as summaryFunctions:

Code:

<summaryFunctions>
    <unitCost>count</unitCost>
</summaryFunctions>

and try to filter the unitCost column by typing 9, you'll see this query for the summary row:

Code:

 SELECT LIMIT 0 1  COUNT(supplyItem.unitCost) AS unitCost FROM supplyItem WHERE ('1'='1') HAVING (COUNT(supplyItem.unitCost)=9)

which isn't the expected query

**Isomorphic** · 20 Nov 2024, 09:35

Thanks for letting us know Claudio. We'll take a look

**Isomorphic** · 20 Nov 2024, 10:00

Hi Claudio, we'll look into your SQL syntax error for your first post, but regarding your second post, that is actually the expected behavior - see the Server Summaries overview, specifically the "Criteria" header.

https://smartclient.com/smartclient-...erverSummaries

Criteria could be intended to be applied either to the summarized value or pre-summarization, but if you work through a variety of examples, we think you'll find that more often than not, developers mean to apply the value post-summarization (your example is highly synthetic). That is also the only way the client-side filtering can work on a summarized dataset, since client-side filtering will not have the values for summarized fields.

You can reverse this on a per-OperationBinding basis, as covered in the docs.

**claudiobosticco** · 22 Nov 2024, 02:15

Hello, thanks for the heads up about applyCriteriaBeforeAggregation.

It seems to me that the reasoning flows smoothly with examples like the ones present here: https://www-demos.smartclient.com/sm...ummariesFolder, where the server summaries feature is used to produce a dataset displayed in a grid.

However, my case seems fundamentally different: the summary, for me, is a row that presents data related to the records in the grid, and when I apply a filter, I expect it to be applied to the grid's data.

Maybe it's easier to explain myself with a sample. Please modify the summariesBasic sample like this:

Code:

isc.ListGrid.create({
    ID:"orderItemList",
    dataSource:"basicAggregation_orderItem",
    width:500,
    height:300,
    showFilterEditor:true,
    showGridSummary: true,
    summaryRowDataSource: "basicAggregation_orderItem",
    summaryRowFetchRequestProperties: {
        operationId: "sum"
    },
    autoFetchData:true,
    canEdit:false,
    canRemoveRecords:false,
    fields: [
        { name: "orderCustomerName", hidden: "true", title: "Customer name" },
        { name: "itemDescription" },
        { name: "unitPrice"},
        { name: "pk", title: "Count" }
    ]
});

Code:

<DataSource
        ID="basicAggregation_orderItem"
        serverType="sql"
        tableName="aggregation_orderItem"
        testFileName="/examples/shared/ds/serverExamples/test_data/aggregation_orderItem.data.xml"
>
    <fields>
        <field name="pk" type="sequence" primaryKey="true" hidden="false" />
        <field name="orderID" type="integer" canEdit="false" foreignKey="basicAggregation_order.orderID"/>
        <field name="orderCustomerName" type="text" canEdit="false" includeFrom="basicAggregation_order.customerName"/>
        <field name="itemDescription" type="text" />
        <field name="quantity" type="integer" />
        <field name="unitPrice" type="float" />
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="sum" applyCriteriaBeforeAggregation="true">
            <summaryFunctions>
                <unitPrice>sum</unitPrice>
            </summaryFunctions>
        </operationBinding>
    </operationBindings>
</DataSource>

Here, the behaviour I expect as a user is that filtering for unitPrice less than 12 will show records with a unit price < 12, and the summary will display the sum of those prices lower than 12.

Since I've used this older default behaviour in the past, would it be possible to have a global setting like applyCriteriaBeforeAggregation in the server.properties file?

Also, note that if you try this sample in the latest 13.1 version with applyCriteriaBeforeAggregation="true", the summary row doesn't show after filtering, even though the summary record is returned. This works correctly in version 13.0.

**Isomorphic** · 22 Nov 2024, 10:49

You're correct that this is a case where criteria should be applied pre-grouping, especially since client-side filtering never applies to the summaryRow. However, it's still the case that post-grouping filtering is more common, so we don't want a system-wide flag, especially since that could break built-in features that assume normal filtering.

For now, just set opBinding.applyCriteriaBeforeGrouping for this use case.

We plan to add a flag dsRequest.filterBeforeGrouping which will can be used to dynamically control this, so we can set it for summaryRow requests. However, this is low priority and probably won't happen for a while.

**claudiobosticco** · 22 Nov 2024, 11:38

Originally posted by Isomorphic View Post

You're correct that this is a case where criteria should be applied pre-grouping, especially since client-side filtering never applies to the summaryRow. However, it's still the case that post-grouping filtering is more common, so we don't want a system-wide flag, especially since that could break built-in features that assume normal filtering.

ok, I didn't though of that

Originally posted by Isomorphic View Post

We plan to add a flag dsRequest.filterBeforeGrouping which will can be used to dynamically control this, so we can set it for summaryRow requests. However, this is low priority and probably won't happen for a while.

Ok, so, if and when you'll add that flag, the pre-grouping will become the default for this use case?

PS: should I open a new thread for the 13.1 bug:

Originally posted by claudiobosticco View Post

Hello, thanks for the heads up about applyCriteriaBeforeAggregation.
Also, note that if you try this sample in the latest 13.1 version with applyCriteriaBeforeAggregation="true", the summary row doesn't show after filtering, even though the summary record is returned. This works correctly in version 13.0.

?

**Isomorphic** · 22 Nov 2024, 12:05

Ok, so, if and when you'll add that flag, the pre-grouping will become the default for this use case?

Yes, the flag will be automatically set for the dsRequest issued for the summaryRow, since the summaryRow is supposed to represent aggregate values.

No need for another thread, we'll check on the problem with the summaryRow not appearing.

**claudiobosticco** · 13 Jan 2025, 03:19

Originally posted by Isomorphic View Post

No need for another thread, we'll check on the problem with the summaryRow not appearing.

Hello, I still see this problem with
SmartClient Version: v13.1p_2025-01-11/Enterprise Development Only (built 2025-01-11)

**Isomorphic** · 13 Jan 2025, 17:11

Hi Claudio
Implementing support for a dsRequest level flag to support filtering before grouping per request, as well as enabling this for the summary row fetch automatically has not yet been done. This is something we intend to do but is queued behind some other priorities.

However, in our testing, the issue where the summary row value is not appearing when you have an explicit applyCriteriaBeforeAggregation operation should be resolved.
We may be missing something. Just to be sure we're testing your exact scenario, can you share your full test case again -- the dataSource and the JS test code? Also just to make sure there are no additional variables to consider, please confirm whether you're testing this against a downloaded SDKPackage running the embedded tomcat server, or some other configuration, and what database you're testing against

Thanks
Isomorphic Software

**claudiobosticco** · 14 Jan 2025, 01:18

SmartClient Version: v13.1p_2025-01-13/Enterprise Development Only (built 2025-01-13)

Hello, thanks for the update.

Regarding the summary row value not appearing, the test case is this:

Code:

isc.ListGrid.create({
    ID:"orderItemList",
    dataSource:"basicAggregation_orderItem",
    width:500,
    height:300,
    showFilterEditor:true,
    showGridSummary: true,
    summaryRowDataSource: "basicAggregation_orderItem",
    summaryRowFetchRequestProperties: {
        operationId: "sum"
    },
    autoFetchData:true,
    canEdit:false,
    canRemoveRecords:false,
    fields: [
        { name: "orderCustomerName", hidden: "true", title: "Customer name" },
        { name: "itemDescription" },
        { name: "unitPrice"},
        { name: "pk", title: "Count" }
    ]
});

Code:

<DataSource
        ID="basicAggregation_orderItem"
        serverType="sql"
        tableName="aggregation_orderItem"
        testFileName="/examples/shared/ds/serverExamples/test_data/aggregation_orderItem.data.xml"
>
    <fields>
        <field name="pk" type="sequence" primaryKey="true" hidden="false" />
        <field name="orderID" type="integer" canEdit="false" foreignKey="basicAggregation_order.orderID"/>
        <field name="orderCustomerName" type="text" canEdit="false" includeFrom="basicAggregation_order.customerName"/>
        <field name="itemDescription" type="text" />
        <field name="quantity" type="integer" />
        <field name="unitPrice" type="float" />
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="sum" applyCriteriaBeforeAggregation="true">
            <summaryFunctions>
                <unitPrice>sum</unitPrice>
            </summaryFunctions>
        </operationBinding>
    </operationBindings>
</DataSource>

I'm testing this against a downloaded SDK, running the embedded tomcat server, I've just modified the above basicAggregation_orderItem.ds.xml file, and the summariesBasic sample js code. When I run it, I see a first response for the summary:

Code:

{
    affectedRows:0,
    data:[
        {
            unitPrice:222.83999999999997
        }
    ],
    endRow:1,
    invalidateCache:false,
    isDSResponse:true,
    operationType:"fetch",
    progressiveLoading:false,
    queueStatus:0,
    startRow:0,
    status:0,
    totalRows:1
}

and the summary row is correctly shown.
If I filter the itemDescription by typing "green", I see a similar response:

Code:

{
    affectedRows:0,
    data:[
        {
            unitPrice:19.28
        }
    ],
    endRow:1,
    invalidateCache:false,
    isDSResponse:true,
    operationType:"fetch",
    progressiveLoading:false,
    queueStatus:0,
    startRow:0,
    status:0,
    totalRows:1
}

but this time the summary row value isn't visible (and I don't see js warnings):

**Isomorphic** · 16 Jan 2025, 12:44

Hi Claudio
Please try again with the latest 13.1 nightly build (Jan 15) and let us know if the problem persists

Thanks
Isomorphic Software

**claudiobosticco** · 17 Jan 2025, 07:16

SmartClient Version: v13.1p_2025-01-17/Enterprise Development Only (built 2025-01-17)

Now I see the summary row after filtering, thank you very much

Announcement

summaryRow problem

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment