Summarizing data within repeat table on a report
I currently have about 20 tabs open trying to figure this out on my own from all the wonderful internet resources. Sadly, I haven't been able to.
Working on a app that allows inspectors to keep track of quantities of items used on a job. So far the repeat functionality is working as intended, creating a daily log of what is used. What I can't figure out is how to sum all entries in the repeat table across time to be able to give a total usage.
This is from my testing environment. I have faked entries for several days using the Inspection Date as a primary key situation.
#DailyQuanities
Inspection Survey
The report only sums the total amount for that day, not the entire Daily Quantities table. When I tried adding a hidden field to sum the total quantity, it of course only totals everything for that day, not the entire Daily Quantities table.
${#DailyQuanities | stats: “sum, item_1_quantity, itemtotal” : "item_1_no" | orderByFields: “item_1_no DESC”}
${item_1_no}: ${itemtotal}
${/}
Results in:
Is there a way to do this? Thinking terms of a Pivot Table aggregating all the items then summing all their totals? End game is to take that running total and subtract it from the allotted amount and present what is left.
Bummed I haven't been able to figure this one out yet!
Within the survey, add the sum() outside the repeat:
${item_1_sum} would equal the sum of all ${item_1_quantity} within the ${DailyQuantities} table for this survey only. If you want to add the ${item_1_sum} from other submitted surveys, you would need to use a pulldata(@layer) function to get those additional values.
In the Feature Report, you can use Report Queries (I think Aggregate Functions are what you are looking for).
Thank you for the response. I actually tired what you said and maybe I need a where statement that works better, but the pulldata didn't work as anticipated either. It did summarize the field, just for only the records on the report date.
Maybe the Survey Jedi can weigh in?
