How to get aggregate values as columns in a report ?

[jsrinivasan]

I have component details table per facility eg:

Fac 1, Meter,...

Fac 1, Pump,...

Fac 1, Meter,...

Fac 2, Meter,... etc.

I would like to get this information in a report as follows :

Facility Name, Meter Count, Pump Count

Fac 1,2,1

Fac 2,1,0

When I use the aggregate function I get this information in different rows, however I need the report to show it in different columns.

Any help would be great.

[HongTaiLang]

Aggregation is used to perform vertical calculation on selected fields. The current basical Count function is to count the number of records no matter what field is. It counts all the records' number or non-blank number.

If you only have two types. Here is a workaround.

Group by "Facility Name", enable "Also apply to the first group" which shows as a "new row" at the end/top of each group, and add two calculated fields for counting. Use two calculated fields with case statements:

For Calc Field1:

Case
When [@field:Category]='Meter' Then 1 else null
End

For Calc Field2:

Case
When [@field:Category]='Pump' Then 1 else null

End The results looks like:

Facility Name----Category ----Calc Field1-----Calc Field2

Fac 1

--------------Meter--------------1--------------(blank)..

--------------Pump------------(blank)-------------1..

--------------Meter--------------1--------------(blank)..

SubTotal of Meter----------------------2

SubTotal of Pump------------------------------------------1

Fac 2

--------------Meter--------------1--------------(blank)..

SubTotal of Meter----------------------1

SubTotal of Pump-------------------------------------------0

Total of Meter--------------------------3-------------------1

And then use an aggregation SUM on the calculated fields. But this is not an elegant way. If you are familiar with coding, you can write code to calculate column sum in the result page, for example: http://forums.caspio.com/viewtopic.php?f=14&t=12180 .

[AtayBalunbalunan]

UPDATE 

Totals & Aggregation are now available on the results pages of tabular Report DataPages. Here's the documentation: https://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/

[JolliBeng]

Hi,

Just an additional info, Formula and Calculations are now available on all paid plans (new plans).

You may check Caspio's pricing for more details.

 

-JolliBeng

[NailDyanC]

 

Hi,

You may also check this video tutorial about Totals and Aggregation:

 

I hope that helps.