I would like to know how to group items and summarize the amount, based on a certain field.
This code in my page (below) works just fine to get all the expenses that meet the criteria and produce the total of all the expenses. What would be cool though would be to have sub-totals of each category, as well as the grand total (like a pivot table)
What I can't figure out is how to get just the records that are of a similar type, in this case all record with the same category (Home Expenses), then sum the amount of that, then keep looping the same for the rest of the categories.
I think I could make a formula for each category, but I already have a dozen or more, so that isn't the right way.
To make this easier for anyone trying to help, I can figure out the right "For each record" script, as long as I know how to get the right subgroup in the first place.
I could use some advice. Thanks!
for each exp in Add_Expense_and_Savings[(Accounts == m_SFFCU_CK_ID || Accounts == k_SFFCU_CK_ID) && Year_number == currentYear]
{
if (exp.Date_field.getYear() == currentYear && exp.Month_number == currentMonth)
{
sumExp=Add_Expense_and_Savings[(Accounts == m_SFFCU_CK_ID || Accounts == k_SFFCU_CK_ID) && Month_number == currentMonth && Year_number == currentYear].sum(Amount).round(2);;
%>
<tr>
<td class="tdBlack"><%=exp.Date_field%></td>
<td class="tdBlack"><%=exp.Category_to_Text%></td>
<td class="tdBlack"><%=exp.Expense_to_Text%></td>
<td class="tdRed"><%=exp.Amount_column%></td>
</tr>
<%
}
}
%>
<thead>
<tr>
<th colspan="3"> </th>
<th style="font-size:19px; color:#ff0000;">$<%=sumExp%></th>
</tr>
</thead>