Show aggregate totals in a list report

[gjlook]

Hello,

is it possible to get the aggregate value of transactions in a report view? 

Eg a job record created in appsheets may have 2 separate linked cost lines at 200 each  - totalling 400 . If i created a Job detail view  and include  the cost lines inline view i do have options to show aggregate totals ( usually count or sum ) .

Can I get that aggregate 400 to appear in a report without seeing the underlying transactions ? 

Many thanks 

George 

[WillowMobileSys]

---

@gjlook wrote:

Can I get that aggregate 400 to appear in a report without seeing the underlying transactions ? 

---

If you are ok showing the data in a collapsed view, then simply add _ROWNUMBER as a second Grouping option.

This should collapse the view to the Aggregated rows only - which a user could tap on to expand to see the underlying details.

I hope this helps!

.

[gjlook]

Many thanks for that   -  I will try that and get back 

Many thanks  

[gjlook]

Thanks for getting back .

When i try the _rownumber grouping it doesnt pull figures from the underlying related costings table.

Do i have to add a virtual column to my job level that accumulates figures - i was hoping appsheet could do this automatically ?

many thanks

[WillowMobileSys · Accepted solution]

---

@gjlook wrote:

Do i have to add a virtual column to my job level that accumulates figures - i was hoping appsheet could do this automatically ?

---

I am at a disadvantage since I don't know your data structure nor what you are trying to accomplish in the "report view".  I think I understand now that you have a parent Job record that has one or more Costings child rows.  You want to show the sum of the child rows on each Parent.  What I am not sure of is how you intend to show the Job records in the "report view" or even if the "report view" is intended as an App view or a generated document.

None the less, to answer your question, YES you will want to add a virtual column to the Job table.   This is extremely easy if you have setup the table relationships properly - you have if in your Job table there is aVC(virtual column) named something like "Related Costings"

In the job table add a VC named maybe "Total Costings" and assign its App Formula an expression similar to:

SUM([Related Costings][Cost column])

Obviously change the column names to those actually in your app.  

Now, let say your "report view" is a view that groups jobs by some [Job Category] column and you want to aggregate the Total Costings for that Job Category.  Create a Table view,  Insert {Job Category] as the grouping column then select from the Aggregate property the option for "SUM - Total Costings"  (or something similar to that).

You will have a Grouped Table view that show as a Group Header the Job Category with aggregated value next to it.  Underneath are the Jobs rows that contributed to that Group.  If you wish the view to be collapsed to just the Group Headings, add the -ROWNUMBER column as a second Grouping item.

I hope this helps!

[gjlook]

Hi thanks for getting back  . Yes in the end i used virtual columns to aggregate . 

In the pas i used sum(select etc but i tried to be too clever with some automation which was unnecessary but as a result i stayed away from the sum(select solution 

However in this case virtual columns work fine and i dont need to show underlying records so grouping not necessary. 

Many thanks for coming back and i'll mark this query as solved 

Regards