In addition to the standard live tables in the i-lign database, customers with inhouse installations of i-lign have access to summary tables.
Summary tables contain denormalised (grouped) data. They do not have the same level of complex joins and business rules as the live database tables, so are more suitable for querying for creating custom reports.
Clients can write and maintain custom reports using a third party reporting tool (such as BusinessObjects). The most recent database model is available for database administrators for reference.
Refreshed Daily
The summary tables are refreshed daily, and contain a snapshot of the information (for projects only) that was current at the time of the refresh. The refresh is carried out automatically, at the same time as the budget refresh.
Hosted instances
Clients using hosted instances of i-lign can produce extract reports using the Extract Reports function (click Reports in the top navigation bar, then the Extract Reports tab). All extracts share a common column, ‘project_id’, thus allowing them to be merged.
Table Details
REPORTING_PROJECT
| Column |
Description |
| PROJECT_ID |
primary key |
| PROGRAMME_ID |
foreign key to programme |
| LIFECYCLE_ID |
foreign key to lifecycle |
| MANAGER_ID |
foreign key to project manager resource |
| CREATED_BY |
foreign key to resource who created project |
| TEAM_ID |
foreign key to team |
| DEFAULT_CALENDAR_ID |
foreign key to project calendar |
| TYPE_ID |
foreign key to project type coded value |
| STATUS |
foreign key to project status coded value |
| RISK_PROFILE |
foreign key to risk profile coded value |
| INVOICE_FREQUENCY |
foreign key to invoice frequency coded value |
| BUDGET_CALCULATION_LEVEL |
foreign key to budget calculation level coded value |
| PREVIOUS_STAGE |
foreign key to the project's previous stage |
| CURRENT_STAGE |
foreign key to the project's current stage |
| NEXT_STAGE |
foreign key to the project's next stage |
| FINAL_REVIEW |
foreign key to the final review |
| LATEST_REVIEW |
foreign key to the latest review |
| NAME |
|
| CREATION_DATE |
|
| DESCRIPTION |
|
| RATIONALE |
|
| RANK |
the numeric rank of the project (if rank is confirmed)
|
| RANK_DESCRIPTION |
a description of the rank ('Pending' if rank not confirmed, 'Inactive' if project is inactive)
|
| PROJECT_NUMBER |
|
| AGREED_DELIVERY_DATE |
|
| HOME_CURRENCY_CODE |
|
| INVOICE_CURRENCY_CODE |
|
| FIXED_PRICE |
the fixed price value in the project's home currency code |
| SRC_FIXED_PRICE |
the fixed price value as entered by the user, if entered in another currency |
| SRC_FIXED_PRICE_CURRENCY_CODE |
the fixed price currency as entered by the user, if entered in another currency |
| FINANCE_NUMBER |
|
| START_DATE |
|
| END_DATE |
|
| CLOSED_DATE |
|
| USE_LITIGATION_FUNDS |
true / false - i-lign Case only |
| INITIATING_PROJECT_ID |
i-lign Case only |
| ELAPSED_DAYS_SINCE_CREATED |
number of days since the project was created in i-lign |
| ELAPSED_DAYS_SINCE_START |
number of days since the project start date |
| WORKING_DAYS_SINCE_CREATED |
number of working days since the project was created in i-lign |
| WORKING_DAYS_SINCE_START |
number of working days since the project start date |
| OVERDUE_MILESTONE_COUNT |
number of overdue milestones |
| OVERDUE_TASK_COUNT |
number of overdue tasks & task pools |
| PERCENT_COMPLETE |
project percent complete |
| TOTAL_PLANNED_EFFORT |
total planned effort derived from all tasks in days
|
| COMPLETED_PLANNED_EFFORT |
completed planned effort derived from all tasks in days (based on each task's planned effort and percent complete) |
| ACTUAL_EFFORT_AGAINST_PROJECT |
total approved hours recorded directly against the project |
| ACTUAL_EFFORT_AGAINST_TASKS |
total approved hours recorded against the project's tasks |
| TOTAL_PLANNED_RESOURCE_HOURS |
total planned time that resources have been assigned to for all tasks |
| IS_ACTIVE |
true / false - does the project have an active status |
| DUE_TO_COMPLETE |
true / false - is the project end date in the next six weeks |
| OVERDUE |
true / false - is the project active but passed its end date |
| LIFECYCLE_AWAITING_APPROVAL |
true / false - does the lifecycle have a stage that's submitted for approval but not yet approved |
| PLANNED_RESOURCE_EXPENSE |
|
| PLANNED_YTD_RESOURCE_EXPENSE |
year to date |
| PLANNED_LTD_RESOURCE_EXPENSE
|
lifetime to date |
| YTD_RESOURCE_EXPENSE |
year to date |
| LTD_RESOURCE_EXPENSE |
lifetime to date |
| PLANNED_EXPENSE |
|
| PLANNED_YTD_EXPENSE
|
year to date |
| PLANNED_LTD_EXPENSE
|
lifetime to date |
| PLANNED_INCOME |
|
| PLANNED_YTD_INCOME
|
year to date |
| PLANNED_LTD_INCOME
|
lifetime to date |
| YTD_EXPENSE |
year to date |
| YTD_INCOME |
year to date |
| LTD_EXPENSE |
lifetime to date |
| LTD_INCOME |
lifetime to date |
| RANK_DESCRIPTION |
text displayed to user |
| RANK |
|
REPORTING_TASK_MILESTONE
| Column |
Description |
| PROJECT_ACTIVITY_ID |
primary key |
| OWNER_ID |
foreign key to resource who is the task's owner |
| PROJECT_ID |
foreign key to project tasks are part of |
| PARENT_ID |
foreign key to the task that is the parent of this task |
| XPD_ID |
foreign key to the task in another project that this project is dependent on |
| NAME |
task name |
| PLANNED_FINISH_DATE |
for milestones, the milestone planned date; for tasks & task pools the planned finish date |
| ACTUAL_FINISH_DATE |
for milestones, the milestone actual date; for tasks & task pools the actual finish date |
| PLANNED_START_DATE |
tasks & task pools only |
| ACTUAL_START_DATE |
tasks & task pools only |
| DESCRIPTION |
|
| LESSONS_LEARNED |
milestones only |
| KEY_GOALS |
milestones only |
| KEY_ACHIEVEMENTS |
milestones only |
| DELIVERABLE |
milestones only |
| NOTES |
|
| PLANNED_EFFORT |
task effort (in days) - tasks only |
| PCT_COMPLETE |
tasks only |
| END_WITH_PROJECT |
true / false - for task pools, whether the task pool extends to the end of the project |
| SEQUENCE_NUMBER |
task order |
| ACTIVITY_NUMBER |
the number i-lign has assigned to the project |
| TASK_TYPE |
M=milestone, T=task, P=task pool |
| PLANNED_HOURS |
total planned time that resources have been assigned to for this task |
| ACTUAL_HOURS |
total approved time recorded against this tasks |
| COMPLETED_PLANNED_EFFORT |
completed planned effort based on planned effort and percent complete |
| COMPLETE |
true / false |
| OVERDUE |
true / false |
| SUMMARY_TASK |
true / false - for tasks only, does this task have children |
| CALCULATION_METHOD |
'Effort-based' or 'Duration-based'
|
| PLANNED_DURATION |
task duration (in days) - tasks only |
