Jump to content
  • 0

How to get aggregate values as columns in a report ?


jsrinivasan

Question

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.

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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 .

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...