Documentation
Beta program
Registration to the program
You can register to our Beta program via this form on the home page of this site. We will contact you to discuss your goals and specific use cases and we will give you the instructions to provide the Jira API token so we can setup the data refresh.
Jira Extract Setup
Generating the API Token
In order to access your Jira data we will need your API Token. You can generate it using the instructions found here.
Data Connection Strings
ADO.NET
Server=tcp:axodbsprod.database.windows.net,1433;Initial Catalog=axo;Persist Security Info=False;UserID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
JDBC
jdbc:sqlserver://axodbsprod.database.windows.net:1433;database=axo;user={your_user_name}@axodbsprod;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
ODBC
Driver={ODBC Driver 13 for SQL Server};Server=tcp:axodbsprod.database.windows.net,1433;Database=axo;Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
Connecting with Microsoft Excel
In order to access your data using Microsoft Excel you can follow these instructions.
SELECT
prj.Name AS "project.name",
tsk.Name AS "task.name",
res.Fullname AS "resource.fullname",
sum(tsh.Hours) AS Hours
FROM
Tasks AS tsk
JOIN
Projects AS prj ON tsk.ProjectSk = prj.ProjectSk
LEFT JOIN
Timesheets AS tsh ON tsh.TaskSk = tsk.TaskSk
LEFT JOIN
Resources AS res ON res.ResourceSk = tsh.ResourceSk
GROUP BY
prj.Name,
tsk.Name,
res.Fullname
SELECT
prj.Name AS "Project.Name",
sum(tsh.Hours) AS "Task.Hours (sum)",
sum(tsh.Cost) AS "Task.Cost (sum)",
sum(tsh.Revenue) AS "Task.Revenue (sum)",
sum(tsk.EstimatedCost) AS "Task.EstimatedCost (sum)",
sum(tsk.EstimatedRevenue) AS "Task.EstimatedRevenue (sum)",
sum(tsk.RemainingCost) AS "Task.RemainingCost (sum)",
sum(tsk.RemainingRevenue) AS "Task.RemainingRevenue (sum)"
FROM
Tasks AS tsk
JOIN
Projects AS prj ON tsk.ProjectSk = prj.ProjectSk
LEFT JOIN
Timesheets AS tsh ON tsk.TaskSk = tsh.TaskSk
GROUP BY
prj.Name
SELECT
res.FullName AS "Resource.Name",
count(distinct prj.Id) AS "Projects",
sum(tsh.Hours) / count(distinct prj.Id) AS "Avg Hours per project",
sum(tsh.Hours) AS "Hours (sum)",
sum(tsh.Cost) AS "Cost (sum)",
sum(tsh.Revenue) AS "Revenue (sum)",
sum(tsk.EstimatedCost) AS "EstimatedCost (sum)",
sum(tsk.EstimatedRevenue) AS "EstimatedRevenue (sum)",
sum(tsk.RemainingCost) AS "RemainingCost (sum)",
sum(tsk.RemainingRevenue) AS "RemainingRevenue (sum)"
FROM
Resources AS res
LEFT JOIN
Timesheets AS tsh ON tsh.ResourceSk = res.ResourceSk
LEFT JOIN
Tasks AS tsk ON tsk.TaskSK = tsh.TaskSK
LEFT JOIN
Projects AS prj ON tsk.ProjectSK = prj.ProjectSK
GROUP BY
res.FullName
-- Lists ALL tasks with associated sprints and time performed within each sprints (time performed outside a sprint IS SHOWN)
SELECT
prj.Name AS "Project.Name",
spr.Id AS "Sprint.Id",
spr.Name AS "Sprint.Name",
tsk.Name AS "Task.Name",
sum(tsh.entries) AS "Timesheets",
sum(tsh.Hours) AS "Hours.Sum",
sum(tsh.Cost) AS "Cost.Sum",
sum(tsh.Revenue) AS "Revenue.Sum",
sum(tsh.Revenue - tsh.Cost) AS "Gross Margin.Sum"
FROM
Tasks AS tsk
JOIN Projects AS prj ON tsk.ProjectSk = prj.ProjectSk
LEFT JOIN sprints_tasks AS stk ON tsk.TaskSk = stk.TaskSk
LEFT JOIN Sprints AS spr ON stk.SprintSk = spr.SprintSk
LEFT JOIN Timesheets AS tsh ON tsh.TaskSk = tsk.TaskSk and tsh.SprintSk = spr.SprintSk
GROUP BY
prj.Name,
spr.Id,
spr.Name,
tsk.Name
ORDER BY
prj.Name,
tsk.Name,
spr.Name
;
-- Lists ALL tasks with associated time and sprint when time was worked (time performed outside a sprint IS SHOWN)
SELECT
prj.Name AS "Project.Name",
spr.Id AS "Sprint.Id",
spr.Name AS "Sprint.Name",
tsk.Name AS "Task.Name",
sum(tsh.entries) AS "Timesheets",
sum(tsh.Hours) AS "Hours.Sum",
sum(tsh.Cost) AS "Cost.Sum",
sum(tsh.Revenue) AS "Revenue.Sum",
sum(tsh.Revenue - tsh.Cost) AS "Gross Margin.Sum"
FROM
Tasks AS tsk
JOIN Projects AS prj ON tsk.ProjectSk = prj.ProjectSk
LEFT JOIN Timesheets AS tsh ON tsh.TaskSk = tsk.TaskSk
LEFT JOIN Sprints AS spr ON spr.SprintSk = tsh.SprintSk
GROUP BY
prj.Name,
spr.Id,
spr.Name,
tsk.Name
ORDER BY
prj.Name,
tsk.Name,
spr.Name
;
-- Lists ALL timesheets with associated task and sprint (time performed outside a sprint IS SHOWN)
SELECT
prj.Name AS "Project.Name",
spr.Id AS "Sprint.Id",
spr.Name AS "Sprint.Name",
tsk.Name AS "Task.Name",
sum(tsh.entries) AS "Timesheets",
sum(tsh.Hours) AS "Hours.Sum",
sum(tsh.Cost) AS "Cost.Sum",
sum(tsh.Revenue) AS "Revenue.Sum",
sum(tsh.Revenue - tsh.Cost) AS "Gross Margin.Sum"
FROM
Timesheets AS tsh
JOIN Tasks AS tsk ON tsh.TaskSk = tsk.TaskSk
JOIN Projects AS prj ON tsk.ProjectSk = prj.ProjectSk
JOIN Sprints AS spr ON spr.SprintSk = tsh.SprintSk
GROUP BY
prj.Name,
spr.Id,
spr.Name,
tsk.Name
ORDER BY
prj.Name,
tsk.Name,
spr.Name
;