Hierarchies in Reports

From: <ccatlin_at_my-dejanews.com>
Date: Thu, 13 May 1999 01:12:58 GMT
Message-ID: <7hd8ug$8go$1@nnrp1.deja.com>

I am using D2K Reports 3.0 to create a headcount report. I need to use the organization hierarchy to find the number of employees assigned to an organization in the hierarchy, and total the numbers by level.

HR              5
  HR South      3
  HR East       2
  HR North      4
  HR West       4
Total HR        18
Payroll         2

  Payroll East 3
  Payroll West 2
Total Payroll 7
Grand Total 25

this is what i have so far:
select count(distinct p.person_id) co,
o.name, lev
from per_all_assignments_f p, hr_all_organization_units o, (select organization_id_child chi, level lev from per_org_structure_elements
where org_structure_version_id = '&version' connect by prior organization_id_child = organization_id_parent start with organization_id_child = '&startorg') where p.organization_id=o.organization_id and p.primary_flag = 'Y' and o.organization_id = chi
group by o.name, lev
;

I have been using the Vision database to test it. Try using 2 for the version ID and 620 for the child ID. If anyone understands what I am trying to do and why it isn't working, let me know.

Thanks.

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 12 1999 - 20:12:58 CDT