Reporting on Organization Employees with Employees of Subsidiaries Included

Options
Amelia Ketzle
Amelia Ketzle ✭
Fifth Anniversary Facilitator 2 Name Dropper Photogenic
in Blackbaud Raiser's Edge NXT®: General Discussion
Hi, all!


We store organizations as relationships with their subsidiaries, indicated by a relationship of Parent Organization/Subsidiary. What we are trying to achieve is a way to report on all the employees of a given organization, as well as all that organization's subsidiary employees. Basically, we need to roll up the employee count/reporting to the parent organization level, and be able to include all the employees of all the subsidiaries, as well as any subsidiaries of those subsidiaries, for as many levels as that may go. We have our university record set up as close to reality as possible, with departments as their own records, and these levels go down 6 or more sometimes, all of which may have employee relationships. We need to be able to pull all the employees of our parent org and have it include the employees of the subsids, as well as the employees of the subsids of the subsids (and the subsids of the subsids of the subsids...).


Any thoughts?


Thank you!
Tagged:

Comments

  • Elaine Tucker
    Elaine Tucker ✭
    Ancient Membership Facilitator 2 Name Dropper Photogenic
    Amelia, is this scenario only for the University? If so, then I'd suggest using a constituent code of "staff" with to and from dates. If this is for all organizations in your database you can query on Individuals with an organization relationship name "one 'of" and then select your organization names. as needed
  • Amelia Ketzle
    Amelia Ketzle ✭
    Fifth Anniversary Facilitator 2 Name Dropper Photogenic

    Hi, Elaine! Thanks for your response, and sorry for the delay in my answer. Our primary concern is reporting on employees of our own university. We do have Constituent Codes of ‘Employee - Current’ and ‘Employee - Former’. This doesn't tell us which department they work for, though, so if we need to include employees of the College of Liberal Arts, for example, that would include employees of the English department, and the Sociology department, etc. These will be set up as subsidiaries of the main university record. To select the orgs by name, we'd have to know all the names that make up that department, which may not be realistic in some cases.

    That said, we do want to be able to do this with other orgs, too, as needed. If there is a particular org that we're interested in (e.g., Caterpillar employs a lot of our grads), we might want to be able to report on those employees, too.

    We did come up with an idea to create a Constituent Attribute that contains the “Parent Org ID” which is the Constituent ID of the organization directly above that department/subsidiary, as well as attributes for that organization's parent record, and on up the chain as far as it goes. However, that has to be set up on each org. It's workable, but not ideal, so I was hoping BB would have something designed specifically for this, or that others would have solved this before me. ?

Categories