Toad World® Forums

Pivot Grid Default Aggregation

In TDP 5.0, the default aggregation seems to be "count" instead of "sum". Is there any way to change the default?
pivot

Currently the default aggregate for number types is sum. What is the datatype of this field in the database or it is result of calculated field or function? It from calculated field for function we have no way of easily knowing the return type.

The datatype for the field in the database is numeric(14,2)

What database type?

SQL Server 2016

I can't seem to reproduce. Let me have someone else take a look.

TDP show count as default if row type is string. QAT-14837 is made to change it

Cindy

It seems to be dependent on the fields in the row area.

In this screen shot with just the deduction code in the row area, it shows the row and columns totals as a sum which is the desired behavior.

If I add employee id as the first row area column, it shows a count for the employee id instead of sum and the grand total row is not displayed even though show
row totals is enabled.. The grand total column is still a sum.

We will add this info to QAT-14837

  • Grand total is not the same with subtotal, at beginning "Robinson, Steven S, Count" it's subtotal, since "Robinson Steven S" is a string, TDP show count not sum for this field, QAT-14837 will resolve it

  • if you add employee id as the first row area column, it show count also --- Could you check "employee id" datatype? if string, that's the issue QAT-14837

  • "Row total" and "Column total" can by invoked by r-click Data Grid "show Row(Column )totals"

Employee id is also a string datatype char(5)

then that's the case QAT-14837

Is there a scheduled date for when this issue will be corrected?

It is scheduled for TDP 5.0.6 but no one has started work on it. So no guarantee.

Hi Steve,

I started to work on this item but it doesn't make sense to me. Can you little bit more describe to me what you want to do?
I can switch default value from COUNT to SUM for char/varchar/text values but it doesn't make sense because it always be 0. Pivot never convert your data but just use datatype from your database. You can modify your SQL and do conversion here if needed.

Filip

Filip, you're correct we want to switch the default subtotals from COUNT to SUM. The totals won't be zero since it's summing the values in the row area which are numeric. Let me know if this makes sense.

Hi Steve, thanks for additional information I just missed part that you want to change it for default subtotal values.
I already changed it and we will test it now.
Filip

Hi Filip, I just tested this issue in 5.0.6.95 and it appears the default aggregation is still count instead of sum.

That is correct. That fix is not in 5.0.6.95. It has been coded but is in testing and will be in 5.0.7