This article outlines the tables, and fields that are available for querying in Karbon Practice Intelligence. These tables contain information about clients, client groups, tasks, users, work items, utilization and work hours, etc. If you have any other needs, please let us know in the Karbon Community.

DIMN_CLIENT

This dimension table holds the necessary attributes that define or provide additional information about a client.

Field

Description

CLIENT_ID

The unique identifier of the client.

CLIENT

The name of the client.

DATE_CREATED

The date when the client was created in the system.

CLIENT_TYPE

The type of client. It can be one of an Individual (Contact), Organisation or Client Group.

CLIENT_SUBTYPE

This is a further sub-classification of a client.

CLIENT_PRIMARY_EMAIL_ADDRESS

The primary email address of the client.

USER_DEFINED_CLIENT_ID

An id created by the user for internal use.

CLIENT_OWNER_USER_ID

The identifier of the User Profile who owns the responsibilities of this client.

CLIENT_OWNER_USER_NAME

The name of the User Profile who owns the responsibilities of this client.

CLIENT_MANAGER_USER_ID

The identifier of the User Profile who manages the responsibilities of this client.

CLIENT_MANAGER_USER_NAME

The name of the User Profile who manages the responsibilities of this client.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

DIMN_CLIENT_GROUP

This dimension table holds the necessary attributes that define or provide additional information about a client group.

Field

Description

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

CLIENT_GROUP_ID

The unique identifier of a client group.

CLIENT_GROUP_NAME

The name of the client group.

CLIENT_ID

The unique identifier of the client who is a member of this group.

CLIENT

The name of the client who is a member of this group.

CLIENT_GROUP_MEMBER_TYPE

The type of client. It can be one of an Individual (Contact), Organisation or Client Group.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

CALN_DATE

This is the calendar date.

DIMN_DATE

This is a date dimension that allows analyzing metrics more effectively across different time periods

Field

Description

CALN_DATE

This is the calendar date.

DAY_OF_WEEK

This is the numerical day of the current week. Sunday is considered to be 1.

WEEK_STRT_DATE

The current week start date (Sun-Sat)

WEEK_END_DATE

The current weekend date. (Sun-Sat)

ISO_DAY_OF_WEEK

This is the ISO numerical day of the calendar date in the current week. Monday is considered to be 1.

ISO_WEEK_STRT_DATE

The current week's start date is as per ISO standards. (MON-SUN)

ISO_WEEK_END_DATE

The current weekend date is as per ISO standards. (MON-SUN)

DAY_OF_WEEK_NAME

The name of the day.

DAY_OF_MNTH

The numerical day of the current month.

DAY_OF_YEAR

The numerical day of the current year.

WEEK_OF_YEAR

The week number of the current year.

CALN_MNTH

The numerical month of the current year.

CALN_MNTH_NAME

The name of the month of the current year.

MNTH_STRT_DATE

The month start date.

MNTH_END_DATE

The month-end date.

PRVS_MNTH_STRT_DATE

The previous month's start date

PRVS_MNTH_END_DATE

The previous month's end date

QRTR_OF_YEAR

The current numerical quarter of the year.

QRTR_STRT_DATE

The quarter start date.

QRTR_END_DATE

The quarter end date.

PRVS_QRTR_STRT_DATE

The previous quarter start date.

PRVS_QRTR_END_DATE

The previous quarter's end date.

CALN_YEAR

The calendar year.

YEAR_STRT_DATE

The year start date.

YEAR_END_DATE

The year-end date.

PRVS_YEAR_STRT_DATE

The previous year's start date.

PRVS_YEAR_END_DATE

The previous year-end date.

DIMN_TASK_TYPE

This dimension table holds the necessary attributes that define or provide additional information about a task type.

Field

Description

TASK_TYPE_ID

The unique identifier for the task type.

TASK_TYPE_NAME

The name of the task type.

IS_TASK_TYPE_BILLABLE

A flag saying whether the task type is billable.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

DIMN_USER

This dimension table holds the necessary attributes that define or provide supplementary information about a user/employee belonging to a tenant.

Field

Description

USER_NAME

The name of the user profile.

USER_ID

The unique identifier of the user profile.

USER_JOB_TITLE

The job title of the user profile.

USER_EMAIL_ADDRESS

The email address of the user profile.

CREATED_DATE

The date when the user profile was created in the system.

ACTIVATED_DATE

The date when the user profile was activated in the system.

EXPECTED_BILLABLE_MINUTES

The weekly billable minutes the user profile is expected to work.

EXPECTED_NONBILLABLE_MINUTES

The weekly non-billable minutes the user profile is expected to work.

STATUS

The status of the user profile.

IS_SUPPORT_USER

A flag indicating whether the user profile is a support user.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

DIMN_WORK_ITEM

This dimension table holds the necessary attributes that define or provide supplementary information about a work item.

Field

Description

WORK_ITEM_ID

The unique identifier for the work item whose details are being provided.

WORK_TITLE

The title of the work item

WORK_TYPE

The type of the work item.

CREATED_DATETIME

The date time the work item was created.

START_DATETIME

The date time the work item was started.

DUE_DATETIME

The due date/time of the work item.

COMPLETED_DATETIME

The date/time the work item was completed.

PRIMARY_STATUS

The primary status of the work item

SECONDARY_STATUS

The secondary status of the work item

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

USER_WEEKLY_UTILIZATION_SUMMARY

This table provides a weekly comparison of the billable and non-billable expected and actual minutes for user profiles/employees.

Field

Description

REPORTING_WEEK_START_DATE

The start date of the reporting week.

REPORTING_WEEK_END_DATE

The end date of the reporting week.

USER_ID

The unique identifier of the user profile.

USER_NAME

The name of the user profile.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

EXPECTED_BILLABLE_MINUTES

The weekly billable minutes the user profile is expected to work.

ACTUAL_BILLABLE_MINUTES

The weekly billable minutes the user profile actually worked.

EXPECTED_NONBILLABLE_MINUTES

The weekly non-billable minutes the user profile is expected to work.

ACTUAL_NONBILLABLE_MINUTES

The weekly non-billable minutes the user profile actually worked.

USER_WORK_HOURS_SUMMARY

This table provides metrics around the actual time entries for a user profiles/employees across different time periods.

Field

Description

REPORTING_DATE

The date of which the data is representative of.

INTERNAL_CLIENT_ID

The User Profile to which the work item belongs to.

INTERNAL_CLIENT

The name of the User to which the work item belongs to.

USER_ID

The unique identifier of the user profile.

USER_NAME

The name of the user profile.

CLIENT_ID

The unique identifier of the client.

CLIENT

The name of the client.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

TASK_TYPE_ID

The unique identifier for the task type.

TASK_TYPE

The name of the task type.

TASK_TYPE_BILLABLE_FLAG

A flag saying whether the task type is billable.

ROLE_ID

The unique identifier of the role performed by the user.

ROLE_NAME

The name of the role performed by the user.

WORK_ITEM_TYPE_ID

The type code of the work item.

WORK_ITEM_TYPE

The type of the work item.

REPORTING_DATE_TIME_MINUTES

Minutes the user logged for the given reporting date.

MTD_TIME_MINUTES

Minutes the user logged this month till reporting date.

QTD_TIME_MINUTES

Minutes the user logged this quarter till reporting date.

YTD_TIME_MINUTES

Minutes the user logged this year till reporting date.

PREVIOUS_MONTH_TIME_MINUTES

Minutes the user logged in the previous month.

PREVIOUS_QUARTER_TIME_MINUTES

Minutes the user logged in the previous quarter.

PREVIOUS_YEAR_TIME_MINUTES

Minutes the user logged in the previous year.

REPORTING_DATE_TIME_COST

The total cost amount is based on the minutes logged by the user for the given reporting date.

MTD_COST

The total cost amount is based on the minutes logged by the user this month till the reporting date.

QTD_COST

The total cost amount is based on the minutes logged by the user this quarter till the reporting date.

YTD_COST

The total cost amount is based on the minutes logged by the user this year till the reporting date.

PREVIOUS_MONTH_COST

The total cost amount is based on the minutes logged by the user in the previous month.

PREVIOUS_QUARTER_COST

The total cost amount is based on the minutes logged by the user in the previous quarter.

PREVIOUS_YEAR_COST

The total cost amount is based on the minutes logged by the user in the previous year.

REPORTING_DATE_RECORD_COUNT

The total number of time entries logged for the given reporting date.

MTD_RECORD_COUNT

The total number of time entries logged this month till reporting date.

QTD_RECORD_COUNT

The total number of time entries logged this quarter till reporting date.

YTD_RECORD_COUNT

The total number of time entries logged this year till reporting date.

PREVIOUS_MONTH_RECORD_COUNT

The total number of time entries logged in the previous month.

PREVIOUS_QUARTER_RECORD_COUNT

The total number of time entries logged in the previous quarter.

PREVIOUS_YEAR_RECORD_COUNT

The total number of time entries logged in the previous year.

WORK_ITEM_BUDGET_VS_ACTUAL

This table compares the budgeted minutes/cost against the actual minutes and costs for work items.

Field

Description

REPORTING_DATE

The date as of which the data is representative of.

WORK_ITEM_ID

The unique identifier for the work item whose details are being provided.

WORK_TITLE

The title of the work item

WORK_TYPE_ID

The type code of the work item.

WORK_TYPE

The type of the work item.

TASK_TYPE_ID

The unique identifier for the task type.

TASK_TYPE

The name of the task type.

TASK_TYPE_BILLABLE_FLAG

A flag saying whether the task type is billable.

ROLE_ID

The unique identifier of the roles performed on the work item.

ROLE_NAME

The name of the roles performed on the work item.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

USER_ID

The unique identifier of the user profile for whom the comparison is done.

USER_NAME

The name of the user profile for whom the comparison is done.

INTERNAL_CLIENT_ID

The User Profile to which the work item belongs to.

INTERNAL_CLIENT

The name of the User to which the work item belongs to.

CLIENT_ID

The client's unique identifier to which the work item belongs to.

CLIENT

The client's name to which the work item belongs to.

BUDGETED_MINUTES

Total minutes budget for the work item.

ACTUAL_MINUTES

The actual minutes spent on the work item.

BUDGETED_COST

The total budgeted cost for the work item.

ACTUAL_COST

The actual cost incurred on the work item.

WORK_ITEM_DAYS_IN_STATUS

This table provides information on the number of days a given work items was in a specific status.

Field

Description

WORK_ITEM_ID

The unique identifier for the work item whose details are being provided.

WORK_ITEM_TITLE

The title of the work item

WORK_ITEM_CREATED_AT

The datetime when the work item was created.

ASSIGNED_TO_ID

The user identifier to whom the work item was assigned to.

ASSIGNED_TO

The name of the user to whom the work item was assigned to.

CLIENT_ID

The client's unique identifier to which the work item belongs to.

CLIENT_NAME

The client's name to which the work item belongs to.

USER_ID

The User Profile to which the work item belongs to.

USER_NAME

The name of the User to which the work item belongs to.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

PRIMARY_STATUS_ID

The primary status code in which the work item currently sits.

PRIMARY_STATUS_NAME

The primary status name in which the work item currently sits.

SECONDARY_STATUS_ID

The secondary status code in which the work item currently sits.

SECONDARY_STATUS_NAME

The secondary status name in which the work item currently sits.

STATUS_ENTRY_DATE

The date on which the work item entered the given primary and secondary status.

STATUS_EXIT_DATE

The date on which the work item exited the given primary and secondary status.

DAYS_IN_STATUS

The number of days the work item was in the given primary and secondary status.

WORK_ITEM_DETAILS

This table provides all the details around a work item.

Field

Description

WORK_ITEM_ID

The unique identifier for the work item whose details are being provided.

REPORTING_DATE

The business date for which the report is run.

WORK_TITLE

The title of the work item

CLIENT_ID

The client's unique identifier to which the work item belongs to.

CLIENT

The client's name to which the work item belongs to.

CLIENT_TYPE

The client's type to which the work item belongs to. Can be one of - Individual, Organization or Client Group.

INTERNAL_CLIENT_ID

The User Profile to which the work item belongs to.

INTERNAL_CLIENT

The name of the User to which the work item belongs to.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

WORK_TYPE_ID

The type code of the work item.

WORK_TYPE

The type of the work item.

PRIMARY_STATUS_ID

The primary status code in which the work item currently sits.

SECONDARY_STATUS_ID

The secondary status code in which the work item currently sits.

PRIMARY_STATUS

The primary status name in which the work item currently sits.

SECONDARY_STATUS

The secondary status name in which the work item currently sits.

CREATED_DATETIME

The date and time when the work item was created.

CREATED_BY_ID

The user profile's unique identifier who created the work item.

CREATED_BY

The user profile's name who created the work item.

START_DATETIME

The date and time when the work item was started. In <timezone>

ASSIGNED_TO_ID

The user profile's unique identifier to whom the work item is currently assigned to.

ASSIGNED_TO

The user profile's name to whom the work item is currently assigned to.

DUE_DATETIME

The date and time when the work item is expected to be completed.

COMPLETED_DATETIME

The date and time when the work item is completed. In <timezone>

COMPLETED_BY_ID

The user profile's unique identifier who completed the work item.

COMPLETED_BY

The user profile's name who completed the work item.

USER_DEFINED_CLIENT_ID

An id created by the user for internal use.

FIXED_FEE_ESTIMATED_COST

The estimated fixed fee cost associated with the work item.

BUDGETED_MINUTES

The budgeted minutes for a work item.

BUDGETED_COST

The budgeted cost for a work item.

EXPENSE_AMOUNT

The total expense amount against the work item.

BILLABLE_EXPENSE_AMOUNT

The billable expense amount against the work item.

TIME_ENTRY_MINUTES

Total time entries in minutes against the work item.

TIME_ENTRY_COST

Total time entry costs against the work item.

WORK_ITEM_STATUS_HISTORY

This table provides the status life cycle of a work item.

Field

Description

WORK_ITEM_ID

The unique identifier for the work item whose details are being provided.

CHANGED_AT_DATETIME

The date time when the status changed from previous to current.

WORK_TITLE

The title of the work item

WORK_TYPE_ID

The id for the work type to which the work item belongs.

WORK_TYPE

The work type name to which the work item belongs.

ASSIGNED_TO_ID

The user identifier to whom the work item was assigned to.

ASSIGNED_TO

The name of the user to whom the work item was assigned to.

CHANGED_BY_ID

The user identifier who changed the work item status.

CHANGED_BY

The name of the user who changed the work item status.

CLIENT_ID

The client's unique identifier to which the work item belongs to.

CLIENT

The client's name to which the work item belongs to.

INTERNAL_CLIENT_ID

The User Profile to which the work item belongs to.

INTERNAL_CLIENT

The name of the User to which the work item belongs to.

ACCOUNT_ID

The tenant's unique identifier to whom the Work Item belongs to.

ACCOUNT_NAME

The tenant's name to whom the Work Item belongs to.

CHANGED_AT_DATE

The date when the status changed from previous to current.

PREVIOUS_PRIMARY_STATUS_ID

The primary status id of the work item before the change.

PREVIOUS_PRIMARY_STATUS

The primary status name of the work item before the change.

PREVIOUS_SECONDARY_STATUS_ID

The secondary status id of the work item before the change.

PREVIOUS_SECONDARY_STATUS

the secondary status name of the work item before the change.

PRIMARY_STATUS_ID

The primary status id of the work item after the change

PRIMARY_STATUS

the primary status name of the work item after the change.

SECONDARY_STATUS_ID

The secondary status id of the work item after the change.

SECONDARY_STATUS

the secondary status name of the work item after the change.

AUTOMATICALLY_UPDATED

Flag indicating if the status change was done by an automator.

Did this answer your question?