Ability to report on Master accounts (count of "family" registrations)


#1

We’re interested in knowing how many families signed up and used the GRA this year, but there isn’t a field in the Reports module to flag “Is Master”…

In addition, it would be lovely if this report feature could also list the sub accounts, but since I can’t imagine how that would work it’s not necessary (and may not be easy to set up). ****

This addition to the reporting feature would be awesome! Thanks!

****Actually, being able to run a report for Master accounts that DO have sub accounts would be what I’m after. I realized later that if I got a list of Master accounts, it includes single adults who don’t have subs.


#2

We certainly need this as a reporting feature and we’ve received requests for it over here. For now I’ve got this which should give you counts if you can run it at the SQL Server level:

SELECT sum(CASE 
			WHEN p.ismasteraccount = 0
				AND p.masteracctpid = 0
				THEN 1
			ELSE 0
			END) AS SoloParticipant,
	sum(CASE 
			WHEN p.ismasteraccount = 1
				THEN 1
			ELSE 0
			END) AS FamilyHeadParticipant,
	sum(CASE 
			WHEN p.ismasteraccount = 0
				AND p.masteracctpid != 0
				THEN 1
			ELSE 0
			END) AS FamilyMemberParticipant
FROM patron p
INNER JOIN programs prg ON p.progid = prg.pid
	AND p.registrationdate < dateadd(d, 1, prg.enddate)