Donations - accumulated value in households

Hi Martin,

It doesn't explain in the help, but this Analytics Constituent Element... 

...includes primary affiliates' contributions in the household constituent's total, and include contributions against the household in each primary affiliate's total (without including contributions linked to other primary affiliates in the same household).

Martin,

We transact as household so all of our donations exist on the household accounts (presumably you previously decided against that for some business purpose), so I do not have any direct experience with that, but I feel like this should be doable either with a custom list manager element which points to a custom VIEW or else just a straight custom report.  Essentially, it is just a matter of taking individual accounts and replacing them with the household account, which presumably you would get from the affiliate level and replace the individual customer number with the group customer number.  You could probably even borrow code from the List Manager replace procedure to accomplish that.

The other quirks that come to my mind would be, assuming you do not want just lifetime giving for each account, deciding how you want to limit the contributions (by date/campaign/fiscal year/etc...).

Of course, if you COULD just change your business rules and transact as household, that would simplify the whole thing.  But then you would also need to run a huge update overnight to move said transactions to the household (can be done; we did that years ago).

Best of luck!

John A. Moskal II

We have all contributions on the household, but I wrote a query using constituencies to see if I could replace an affiliated individual's customer_no with the household, then applied the logic to contributions. Perhaps this could help.

Lucie

declare @cust_type table
( customer_no int not null,
cust_type int not null,
expanded_cust_no int not null
)
insert into @cust_type
( customer_no,
cust_type,
expanded_cust_no
)
select distinct
a.customer_no,
a.cust_type,
case when a.cust_type = ## then a.customer_no --## is id of household customer type
when a.cust_type = 1 then b.expanded_customer_no --1 is id of individual customer type
else 0 end
from t_customer a
join VS_CUSTOMER_WITH_PRIMARY_AFFILIATES b on b.customer_no = a.customer_no
where a.customer_no in
(select customer_no
from vs_contribution
where fund_no in (1,2) and --use whatever criteria you are looking for in the where clause
campaign_no in
(select campaign_no
from VS_CAMPAIGN
where fyear = 2023))
and a.customer_no <> b.expanded_customer_no

declare @contribution table
( household_customer_no int not null,
ref_no int not null,
cont_dt datetime not null,
recd_amt money not null,
cont_amt money not null
)
insert into @contribution
( household_customer_no,
ref_no,
cont_dt,
recd_amt,
cont_amt
)
select distinct
b.expanded_cust_no,
a.ref_no,
a.cont_dt,
a.recd_amt,
a.cont_amt
from VS_CONTRIBUTION a
join @cust_type b on b.customer_no = a.customer_no
where a.fund_no in (1,2) and --use whatever criteria you are looking for in the where clause
a.campaign_no in
(select campaign_no
from VS_CAMPAIGN
where fyear = 2023)
order by b.expanded_cust_no

select
a.household_customer_no,
b.display_name,
b.sort_name,
SUM(a.recd_amt) as 'sum_recd_amt',
SUM(a.cont_amt) as 'sum_cont_amt'
from @contribution a
join FT_CONSTITUENT_DISPLAY_NAME() b on b.customer_no = a.household_customer_no
group by
a.household_customer_no,
b.display_name,
b.sort_name