Summing for an Individual Across Groups?

Zane Magnuson 2

This is a tricky one - we've got a report that displays event ticket purchases, grouped by ticket level. Each group then has a sort of sub-group that lists names of each participant, how many tickets they bought, how much they spent, followed by a total for the group at the bottom.


One of the fields we use for each participant is a calculated "Balance Due" field. It's just "Registration Fee Amount" minus "Amount Paid". It's worked fine for a couple years, but we just ran into our first instance of a registrant buying tickets at multiple levels. We've got a participant that spent $5,000 in the Gold level and $3,000 in the Silver level. He shows up in each of those groups with a "Balance Due" showing the difference between that level's fees and $8,000.


Is there a way I can write a field for individual participants that sums their Registration Fees across groups? That way I can re-write the Balance Due to be "Total Registration Fees For Participant" minus "Amount Paid"

James Andrews

Zane Magnuson:

This is a tricky one - we've got a report that displays event ticket purchases, grouped by ticket level. Each group then has a sort of sub-group that lists names of each participant, how many tickets they bought, how much they spent, followed by a total for the group at the bottom.


One of the fields we use for each participant is a calculated "Balance Due" field. It's just "Registration Fee Amount" minus "Amount Paid". It's worked fine for a couple years, but we just ran into our first instance of a registrant buying tickets at multiple levels. We've got a participant that spent $5,000 in the Gold level and $3,000 in the Silver level. He shows up in each of those groups with a "Balance Due" showing the difference between that level's fees and $8,000.


Is there a way I can write a field for individual participants that sums their Registration Fees across groups? That way I can re-write the Balance Due to be "Total Registration Fees For Participant" minus "Amount Paid"

Rather than doing that, since you're grouping by participation level anyway, is there a way for you to have your balance not calculate on the total registration fees per constituent, but instead on the registration fees per level?

Zane Magnuson 2

James Andrews:

Rather than doing that, since you're grouping by participation level anyway, is there a way for you to have your balance not calculate on the total registration fees per constituent, but instead on the registration fees per level?

Unfortunately, that's not the goal for what was requested in the report. There is a final tally that sums of cash-in-hand vs balance due, but the department wants that information at the participant level as well. With the way the data is configured, I can get the amount paid to reflect the total of $8,000, but I'm struggling to find a way to say "This individual's total registration fees are X".

James Andrews

Zane Magnuson:

James Andrews:

Rather than doing that, since you're grouping by participation level anyway, is there a way for you to have your balance not calculate on the total registration fees per constituent, but instead on the registration fees per level?

Unfortunately, that's not the goal for what was requested in the report. There is a final tally that sums of cash-in-hand vs balance due, but the department wants that information at the participant level as well. With the way the data is configured, I can get the amount paid to reflect the total of $8,000, but I'm struggling to find a way to say "This individual's total registration fees are X".

 

How about a subreport that ignores the participation type and justt sums up registration fees by participant, and then links back to the main report via the participant ID?