...

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].

...

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 planning period (called allocation projects in reporting) combination [supply team : demand team : allocation project].

...

SELECT allocationprojectname, forecastfte
FROM forecast
WHERE
    targetteamname = 'ABC App'
    andAND sourceteamname = 'DB Solutioning'
ORDER BY allocationprojectname

...

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:

...

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.

selectSELECT *
fromFROM objectives
whereWHERE objective_type = 'company-objective'

Example 2: Find all the objectives with the parent of '1S3Q0WN2'

selectSELECT *
fromFROM objectives
whereWHERE 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.

selectSELECT objective_id, objective_name
fromFROM team_objectives
whereWHERE 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].

...

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.

...

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.

...

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.

...

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.

...

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.

...

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.

...

SELECT division_id, division_name
FROM divisions
WHERE division_parent_id = '12345678'

tags

This table stores a list of all 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.

...

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.

...

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.

...

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.

...

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'

...

SELECT association_tag_type, association_direction, right_id, right_name
FROM associations
WHERE left_team_name = 'Alpha'

Related Pages