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.

1 allocations
2 forecast
3 memberships
4 objectives
5 team_objectives
6 planning
7 projects
8 people
9 people_attributes
10 teams
11 team_attributes
12 divisions
13 tags
14 tag_attributes
15 tag_teams
16 tag_people
17 associations
18 Related Pages

TeamForm Reporting Data Model.

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 planning period (called allocation projects in reporting) 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.

Example 2: Count the number of people in each Agile Team who are not from A&B division.

Example 3: Count the number of people in each Chapter Area.

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.

Example 2: Find all the objectives with the parent of '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.

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.

Example 2: Show the largest approved allocations by supply to demand team

projects

This table holds the list of projects in TeamForm. There is one row per project.

Example 1: Find projects where Planner is active.

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.

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.

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.

Example 2: Count the number of teams of type CoE per Function.

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.

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'

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.

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.

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.

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.

Example 2: Find all groups related to team ‘Alpha’.

Related Pages

Page:

Reporting reference - query basics
Page:

Reporting reference - tables
Page:

Reporting reference - concepts
Page:

Reporting
Page:

Reporting reference - fields

Reporting reference - tables