TeamForm Reports is designed to enable anyone, regardless of the level of technical sophistication, to harness the power of your team data big and small. TeamForm Reports uses the SQL language to enable you to explore, query, visualise, and share your teams data.
This page describes information about the various tables that are available in TeamForm Reports to query against.
Tables
The primary keys are either:
ID of a person (e.g. person_id, manager_id), or
ID of a team (e.g. team_id, supply_id, demand_id). 'Team' is a generic term for any grouping of people
allocations
This table shows the current state of allocations as observed on the Team Details tab. It includes direct only allocations (does not include indirect memberships). There is one record per person’s team membership [person : team].
Example 1: Find People who are in Agile Teams within the Alpha Function.
SELECT demand_name, person_preferred_name, person_surname
FROM allocations
WHERE
demand_type = 'Agile Team'
AND demand_hierarchy_name_3 = 'Alpha'
forecast
This table holds the results of the supply team to demand team resource forecasting. There is one row per supply team to demand team to allocation project combination [supply team : demand team : allocation project].
Example 1: Find the forecasts entered by demand team ABC App against the supply team DB Solutioning.
SELECT allocationprojectname, forecastfte
FROM forecast
WHERE
targetteamname = 'ABC App'
and sourceteamname = 'DB Solutioning'
ORDER BY allocationprojectname
Example 2: Find the total forecast per quarter from all B&MS demand teams.
SELECT allocationprojectname, supply_hierarchy_name_1, sum(forecastfte) AS forecastfte FROM forecast WHERE demand_hierarchy_name_1 = 'B & MS' GROUP BY allocationprojectname, supply_hierarchy_name_1 ORDER BY allocationprojectname, supply_hierarchy_name_1
memberships
This table stores allocations and other memberships between people and teams in the current state of the organisation. There is one record per person to team association [person : team], so people and teams will appear multiple times. The table includes both:
Direct membership (person is immediately in the team), and
Indirect memberships (person is a member by association due to a child team)
Example 1: Find People from A&B division who are in Agile Teams within the Alpha Function.
SELECT team_name, person_preferred_name, person_surname
FROM memberships
WHERE
team_type = 'Agile Team'
AND team_hierarchy_name_3 = 'Alpha'
AND person_division_name_3 = 'Able and Bebba'
Example 2: Count the number of people in each Agile Team who are not from A&B division.
SELECT team_name, count(person_id) as count
FROM memberships
WHERE
team_type = 'Agile Team'
AND team_direct_membership = true
AND division_hierarchy_name_3 != 'Able and Bebba'
GROUP BY team_name
ORDER BY count DESC
Example 3: Count the number of people in each Chapter Area.
SELECT DISTINCT team_name AS chapter, count(person_id) AS chaptermembercount FROM memberships WHERE team_hierarchy_type_3 = 'Chapter Area' GROUP BY team_name ORDER BY chaptermembercount DESC
objectives
This table hosts a list of all the different objectives in an organisation. There is one row per unique objective. Objectives have a type and can have one or more parents. They can also have an external ID for reference in a foreign system.
Example 1: Find all the company objectives.
select * from objectives where objective_type = 'company-objective'
Example 2: Find all the objectives with the parent of '1S3Q0WN2'
select * from objectives where contains(parent_objective_ids,'1S3Q0WN2')
team_objectives
This table stores the relationships between objectives and teams have. There is one record per team to objective [team : objective], so teams and objectives will appear multiple times. Teams with no objectives will not appear. Objectives with no teams will not appear.
Example 1: Find all the objectives linked to team Alpha.
select objective_id, objective_name from team_objectives where team_name = 'Alpha'
planning
This table holds the exchange of planning requests made in Planner. There is one row per supply team to demand team combination [supply team : demand team].
Example 1: Find planning allocations for increases over 2.00 approved FTE to Agile Mini-Groups from Chapter Areas.
SELECT *
FROM planning
WHERE
approval_submitted_fte_delta > 2.00
AND targethierarchy5teamtype = 'Agile Mini-Group'
AND sourcehierarchy3teamtype = 'Chapter Area'
Example 2: Show the largest approved allocations by supply to demand team
SELECT sum(approval_submitted_fte_actual) as sumfte, targetteamname, sourceteamname FROM customer_planning GROUP BY targetteamid, sourceteamid, targetteamname, sourceteamname ORDER BY sumfte desc
projects
This table holds the list of projects in TeamForm. There is one row per project.
Example 1: Find projects where Planner is active.
SELECT *
FROM projects
WHERE
project_planner = true
people
This table stores all currently active people in the organisation. Each person has a single record. People who have left the organisation are not included in the table.
Example 1: Find the names and email addresses of people who have a Role of Product Owner.
SELECT person_first_name, person_surname, person_email FROM people WHERE person_jobtitle = 'Product Owner'
people_attributes
This table stores the attributes of people. There is one record per person to attribute [person : attribute], so people and attributes will appear multiple times. People with no attributes will not appear.
Example 1: Find all the attributes for a particular person.
SELECT person_id, attribute_id, attribute_type, attribute_subtype, attribute_value FROM people_attributes WHERE person_id = '10000001'
teams
This table stores a list of all teams. Teams are any set of individuals such as; Agile teams, Chapters, and other bespoke teams. Each team has a single record. Teams can be empty with no members; either because everyone has departed the team or if the team is newly created with no members.
Example 1: Find the Function and team name of all teams that are the type CoE.
SELECT team_hierarchy_name_1, team_name FROM teams WHERE team_type = 'CoE'
Example 2: Count the number of teams of type CoE per Function.
SELECT team_hierarchy_name_1, count(*) FROM teams WHERE team_type = 'CoE' GROUP BY team_hierarchy_name_1
team_attributes
This table stores the attributes of teams. There is one record per team to attribute [team : attribute], so teams and attributes will appear multiple times. Teams with no attributes will not appear.
Example 1: Find all the attributes for a particular Team.
SELECT team_id, attribute_id, attribute_type, attribute_subtype, attribute_value FROM team_attributes WHERE person_id = 'ABCDEFGH'
divisions
This table stores a list of all divisions. Divisions are any set of individuals as populated by a HR integration. Each divisions has a single record. Divisions can be empty with no members.
Example 1: Find all the divisions under the division ‘Sales’ which has an ID of '12345678'
SELECT division_id, division_name FROM divisions WHERE division_parent_id = '12345678'
tags
This table stores a list of all tags.
Example 1: Find all possible Specialist Role tags.
SELECT tag_id, tag_value FROM tags WHERE tag_type = 'SPECIALIST_ROLE'
tag_attributes
This table stores the attributes of tags. There is one record per tag to attribute [tag : attribute], so tags and attributes will appear multiple times. Tags with no attributes will not appear.
Example 1: Find all the attributes for a tag Specialist Role tags.
SELECT tag_id, attribute_id, attribute_label, attribute_value FROM tag_attributes WHERE tag_id = 'abcde'
tag_teams
This table stores the tags that teams have. There is one record per team to tag [team : tag], so teams and tags will appear multiple times. Teams with no tags will not appear.
Example 1: Find all the team of type 'Agile Team' that have a Interactions tag type.
SELECT team_name, team_id FROM tag_teams WHERE team_type = 'Agile Team' AND tag_type = 'Interaction'
tag_people
This table stores the tags that people have. There is one record per person to tag [person : tag], so people and tags will appear multiple times. People with no tags will not appear.
Example 1: Find all the people that have ‘Scrum Master’ specialise role tag.
SELECT person_id, person_first_name, person_surname FROM tag_people WHERE tag_type = 'SPECIALIST_ROLE' AND tag_value = 'Scrum Master'
associations
This table holds cooperative relationships between two teams defined by an association type and direction.
Example 1: Find all the entities that have a risk dependancy.
SELECT left_id, left_name, association_tag_type, right_id, right_name FROM associations WHERE association_tag_type = 'RISK' AND association_tag_value = 'financial risk'
Example 2: Find all groups related to team ‘Alpha’.
SELECT association_tag_type, association_direction, right_id, right_name FROM associations WHERE left_team_name = 'Alpha'