Page Comparison

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

Image Removed

Image Added

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'

Related articles