Microsoft Project Connection
This documentation is based on version 21.0.8383 of the connector.
This documentation is based on version 21.0.8383 of the connector.
Enabling bidirectional SQL access to Project Server, the connector models Microsoft Project's CSOM and OData APIs as a relational database. The Jitterbit Connector for Microsoft Project can connect to both the cloud and local versions of Project Server.
In order to connect to the Microsoft Project data source, you will first need to specify the below connection properties.
http://contoso.com/sites/pwa/
There are several authentication methods available for connecting to Microsoft Project including the SharePoint Online Cookie Service to connect on SharePoint Online, OAuth and SSO. The supported SSO identity providers are: Azure Active Directory, OneLogin, and OKTA.
To connect to Microsoft Project on SharePoint Online using your login credentials, set the AuthScheme to SharePointOnlineCookies, the User and Password to your user credentials.
To use the OAuth 2.0 authentication standard you should first create a custom OAuth app to obtain the OAuthClientId, OAuthClientSecret. In addition to those properties, set CallbackURL. See Using OAuth2 Authentication for more information.
You should set the AuthScheme to OAuth to enforce the OAuth Authentication scheme.
Set the AuthScheme to ADFS. The following connection properties need to be set:
Below is an example connection string:
AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';
The ADFS Integrated flow indicates you are connecting with the currently logged in Windows user credentials. To use the ADFS Integrated flow, simply do not specify the User and Password, but otherwise follow the same steps in the ADFS guide above.
Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:
The following SSOProperties are needed to authenticate to Okta:
The following is an example connection string:
AuthScheme=Okta;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;
Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:
The following SSOProperties are needed to authenticate to OneLogin:
The following is an example connection string: The following connection string uses an API key to connect to OneLogin:
AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;SSOProperties='OAuthClientID=3fc8394584f153ce3b7924d9cd4f686443a52b;OAuthClientSecret=ca9257fd5cc3277abb5818cea28c06fe9b3b285d73d06;Subdomain=OneLoginSubDomain;AppId=1433920';
OAuth 2.0 requires the authenticating user to interact with Microsoft Project using the browser. The connector facilitates this in various ways as described below.
You can register an app to obtain the OAuthClientId and OAuthClientSecret.
After setting the following, you are ready to connect:
Set the following connection properties to obtain the OAuthAccessToken:
You can then call stored procedures* to complete the OAuth exchange:
Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback you specified in your app settings.
The stored procedure* returns the URL to the OAuth endpoint.You can set InitiateOAuth to REFRESH to automatically refresh the OAuth access token when it expires, or you can call the RefreshOAuthAccessToken stored procedure* to refresh the token manually.
Automatic Refresh
To refresh the token with InitiateOAuth, set the following on the first data connection:
On subsequent data connections, set the following:
Manual Refresh
You can use the RefreshOAuthAccessToken stored procedure* to manually refresh the OAuthAccessToken. Call the stored procedure* after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed. You need the following connection properties to be set:
You can then call RefreshOAuthAccessToken with the following parameter set:
To establish a connection to the Microsoft Project data source on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.
Create a Custom OAuth App
See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.
Obtain a Verifier Code
Set the following properties on the headless machine:
You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.
On the headless machine, set the following connection properties to obtain the OAuth authentication values:
Connect to Data
After the OAuth settings file is generated, set the following properties to connect to data:
Transfer OAuth Settings
Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.
On a second machine, install the connector and connect with the following properties set:
Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:
You can create an OAuth application through your site's UI, or by manually entering your site's create app link into your browser: https://{site}.sharepoint.com/sites/{Project_Web_App_Site}/_layouts/15/appregnew.aspx.
After filling in the required fields, you may click "Create" and the following credentials will be displayed.
Note that while creating the OAuth application, you will be required to specify a Callback URL.
This is the URL you will be redirected to after explicitly granting access. Microsoft Project requires this callback to use 'https://'.
You can work around this restriction by supplying 'https://oauth.cdata.com/oauth/' as callback, and adding an extra state connection property which will contain the actual redirect callback.
Copy the ClientID and ClientSecret which will be needed later.
In order to access the microsoft project api we have to give scope permissions to the created app. In order to do that go to the following URL: https://{site}.sharepoint.com/sites/{Project_Web_App_Site}/_layouts/15/AppInv.aspx.
In the App ID field enter the ClientID obtained when creating the app and click on Lookup. The other fields should automatically be filled with the apps details like Title, App Domain and Redirect URL. In the permissions request XML enter the following:
<AppPermissionRequests> <AppPermissionRequest Scope="http://sharepoint/projectserver" Right="Manage"/> <AppPermissionRequest Scope="http://sharepoint/projectserver/projects" Right="Write"/> <AppPermissionRequest Scope="http://sharepoint/projectserver/enterpriseresources" Right="Write"/> <AppPermissionRequest Scope="http://sharepoint/projectserver/reporting" Right="Read"/> </AppPermissionRequests>These are the default permissions needed to have full access to the api. Also the logged in user should make sure they have the appropriate permissions to access the api. In case your Project Web App (PWA) is in Project permission mode, you can explicitly grant access to the Web Services for specified PWA users. To do this, you can follow the steps below.
In case your PWA is in SharePoint permission mode, you will need to add the interested user in a group with at least the 'Access Project Server Reporting Service' permission enabled, such as: Portfolio Viewers, Portfolio Managers, and Administrators. To do this, you can follow the steps below.
You can now use these credentials to connect to Microsoft Project by setting them as the following connection properties:
Alternatively, to use the callback:
You can now use these credentials to connect to Microsoft Project by setting them as the following connection properties:There have been some changes made to the connector in the 2020 edition that should be taken into consideration.
Prior to the 2020 edition of the connector, connections to Microsoft Project using both the CSOM and OData interface were supported, which lead to difficulties in maintaining the two schemas concurrently.
By removing support for the CSOM, connections to Microsoft Project are streamlined due to the lack of special connection properties made specifically for each schema. Also, the OData interface can now perform all of the same CRUD operations that the CSOM is capable of. Lastly, the CSOM is poorly documented and more error-prone than OData.
For these reasons, the decision was made to deprecate support for the CSOM in favor of exclusively supporting the OData interface.
One additional advantage of using the Microsoft Project OData interface exclusively is the support for efficient bulk operations.
Microsoft Project data must be checked out, an INSERT/UPDATE/DELETE operation is performed, the changes are published, and finally the changes are checked in.
At times, the checkout step may fail, leading to numerous requests per single INSERT/UPDATE/DELETE operation.
With the connector's batch support, you can dramatically reduce the numbers of requests submitted, since several records can be submitted through the checkin/checkout process at a time.
See Batch Processing for details on batch operations.
This section details a selection of advanced features of the Microsoft Project connector.
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.
The Jitterbit Connector for Microsoft Project allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:For example:
{ "MyView": { "query": "SELECT * FROM Projects WHERE MyColumn = 'value'" }, "MyView2": { "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)" } }Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
SELECT * FROM Customers WHERE City = 'Raleigh';An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
The Jitterbit Connector for Microsoft Project models Microsoft Project objects as relational tables and views. A Microsoft Project object has relationships to other objects; in the tables, these relationships are expressed through foreign keys.
The ODataV2 Data Model (default) can be used for full CRUD operations. Main columns metadata is dynamically retrieved for each entity, but the schema is fixed and custom fields are not included.
Tables and Views describe a sample the tables available in the ODataV2 API.
The ODataV1 Data Model can only be used for reading data. Metadata is dynamically retrieved, including the custom fields for this schema.
Views describe a sample the views available in the ODataV1 API.
The connector offloads as much of the SELECT statement processing as possible to Microsoft Project and then processes the rest of the query in memory. See SupportEnhancedSQL for more information on how the connector circumvents API limitations with in-memory client-side processing.
SELECT statements for the main tables are mostly supported server-side. For example:
SELECT * FROM Tasks WHERE Id = '61a6cc2d-cff5-e911-b093-00155ddc7224'will be run server-side.
Note: When executing SELECT statements on child tables, everything is processed client side.
The ODataV2 schema implements the ProjectServer service endpoints, which has support for full CRUD operations.
Tables are retrieved dynamically from the authenticated Microsoft Project account.
Read-only Views are retrieved dynamically from the authenticated Microsoft Project account.
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored Procedures are function-like interfaces to Microsoft Project. Stored procedures* allow you to execute operations to Microsoft Project.
the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.
The connector models the data in Microsoft Project into a list of tables that can be queried using standard SQL statements.
Generally, querying Microsoft Project tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
Assignments | Retrieve and modify assignments. |
Calendars | Retrieve and modify calendars. |
EnterpriseResources | Retrieve and modify resources. |
Phases | Retrieve and modify project phases. |
ProjectResources | Retrieve and modify project resources. |
Projects | Retrieve and modify projects. |
Stages | Retrieve and modify project stages. |
TaskLinks | Retrieve and modify task links. |
Tasks | Retrieve and modify tasks. |
TimeSheetLines | Retrieve and modify timesheets. |
TimeSheets | Retrieve and modify timesheets. |
Retrieve and modify assignments.
To query this table, the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.
SELECT * FROM Assignments WHERE ProjectId = '99a38309-dc1d-eb11-bf8a-00155d9c6312'
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | True | False | ||
ActualCostWorkPerformed | Double | True | False | ||
ActualOvertimeCost | Double | True | False | ||
BaselineCost | Double | True | False | ||
BaselineCostPerUse | Double | True | False | ||
BaselineFinish | Datetime | True | False | ||
BaselineStart | Datetime | True | False | ||
BaselineWork | String | True | False | ||
BaselineWorkMilliseconds | Int | True | False | ||
BaselineWorkTimeSpan | Time | True | False | ||
BudgetedCostWorkPerformed | Double | True | False | ||
BudgetedCostWorkScheduled | Double | True | False | ||
CostVariance | Double | True | False | ||
CostVarianceAtCompletion | Double | True | False | ||
Created | Datetime | True | False | ||
CurrentCostVariance | Double | True | False | ||
Finish | Datetime | True | False | ||
FinishVariance | String | True | False | ||
FinishVarianceMilliseconds | Int | True | False | ||
FinishVarianceTimeSpan | Time | True | False | ||
IsConfirmed | Bool | True | False | ||
IsOverAllocated | Bool | True | False | ||
IsPublished | Bool | True | False | ||
IsResponsePending | Bool | True | False | ||
IsUpdateNeeded | Bool | True | False | ||
LevelingDelay | String | True | False | ||
LevelingDelayMilliseconds | Int | True | False | ||
LevelingDelayTimeSpan | Time | True | False | ||
Modified | Datetime | True | False | ||
Notes | String | True | False | ||
OvertimeCost | Double | True | False | ||
RemainingCost | Double | True | False | ||
RemainingOvertimeCost | Double | True | False | ||
Resume | Datetime | True | False | ||
ScheduleCostVariance | Double | True | False | ||
Start | Datetime | True | False | ||
StartVariance | String | True | False | ||
StartVarianceMilliseconds | Int | True | False | ||
StartVarianceTimeSpan | Time | True | False | ||
Stop | Datetime | True | False | ||
WorkContourType | Int | True | False | ||
WorkVariance | String | True | False | ||
WorkVarianceMilliseconds | Int | True | False | ||
WorkVarianceTimeSpan | Time | True | False | ||
LinkedCustomFields | String | False | False | ||
ActualCost | Double | False | False | ||
ActualFinish | Datetime | False | False | ||
ActualOvertimeWork | String | False | False | ||
ActualOvertimeWorkMilliseconds | Int | False | False | ||
ActualOvertimeWorkTimeSpan | Time | False | False | ||
ActualStart | Datetime | False | False | ||
ActualWork | String | False | False | ||
ActualWorkMilliseconds | Int | False | False | ||
ActualWorkTimeSpan | Time | False | False | ||
BudgetedCost | Double | False | False | ||
BudgetedWork | String | False | False | ||
BudgetedWorkMilliseconds | Int | False | False | ||
BudgetedWorkTimeSpan | Time | False | False | ||
Cost | Double | False | False | ||
CostRateTable | Int | False | False | ||
DefaultBookingType | Int | False | False | ||
Delay | String | False | False | ||
DelayMilliseconds | Int | False | False | ||
DelayTimeSpan | Time | False | False | ||
IsLockedByManager | Bool | False | False | ||
IsWorkResource | Bool | False | False | ||
OvertimeWork | String | False | False | ||
OvertimeWorkMilliseconds | Int | False | False | ||
OvertimeWorkTimeSpan | Time | False | False | ||
PercentWorkComplete | Int | False | False | ||
RegularWork | String | False | False | ||
RegularWorkMilliseconds | Int | False | False | ||
RegularWorkTimeSpan | Time | False | False | ||
RemainingOvertimeWork | String | False | False | ||
RemainingOvertimeWorkMilliseconds | Int | False | False | ||
RemainingOvertimeWorkTimeSpan | Time | False | False | ||
RemainingWork | String | False | False | ||
RemainingWorkMilliseconds | Int | False | False | ||
RemainingWorkTimeSpan | Time | False | False | ||
ResourceCapacity | Double | False | False | ||
ResourceId | String | False | False | ||
TaskId | String | False | False | ||
Work | String | False | False | ||
WorkMilliseconds | Int | False | False | ||
WorkTimeSpan | Time | False | False | ||
LinkedOwner | String | False | False | ||
LinkedParent | String | False | False | ||
LinkedResource | String | False | False | ||
LinkedTask | String | False | False | ||
ProjectId [KEY] | String | True | False |
Id of the parent. |
Retrieve and modify calendars.
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | False | False | ||
Created | Datetime | False | False | ||
IsStandardCalendar | Bool | False | False | ||
Modified | Datetime | False | False | ||
Name | String | False | False | ||
OriginalId | String | False | False | ||
LinkedBaseCalendarExceptions | String | False | False |
Retrieve and modify resources.
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | False | False | ||
CanLevel | Bool | False | False | ||
Code | String | False | False | ||
CostAccrual | Int | False | False | ||
CostCenter | String | False | False | ||
Created | Datetime | False | False | ||
DefaultBookingType | Int | False | False | ||
String | False | False | |||
ExternalId | String | False | False | ||
Group | String | False | False | ||
HireDate | Datetime | False | False | ||
HyperlinkName | String | False | False | ||
HyperlinkUrl | String | False | False | ||
Initials | String | False | False | ||
IsActive | Bool | False | False | ||
IsBudget | Bool | False | False | ||
IsCheckedOut | Bool | False | False | ||
IsGeneric | Bool | False | False | ||
IsTeam | Bool | False | False | ||
MaterialLabel | String | False | False | ||
Modified | Datetime | False | False | ||
Name | String | False | False | ||
Phonetics | String | False | False | ||
RequiresEngagements | Bool | False | False | ||
ResourceIdentifier | Int | False | False | ||
ResourceType | Int | False | False | ||
TerminationDate | Datetime | False | False | ||
LinkedAssignments | String | False | False | ||
LinkedBaseCalendar | String | False | False | ||
LinkedCostRateTables | String | False | False | ||
LinkedCustomFields | String | False | False | ||
LinkedDefaultAssignmentOwner | String | False | False | ||
LinkedEngagements | String | False | False | ||
LinkedResourceCalendarExceptions | String | False | False | ||
LinkedSelf | String | False | False | ||
LinkedTimesheetManager | String | False | False | ||
LinkedUser | String | False | False | ||
LinkedUserPermissions | String | False | False |
Retrieve and modify project phases.
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | False | False | ||
Description | String | False | False | ||
Name | String | False | False | ||
LinkedStages | String | False | False |
Retrieve and modify project resources.
To query this table, the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.
SELECT * FROM ProjectResources WHERE ProjectId = '99a38309-dc1d-eb11-bf8a-00155d9c6312'
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | True | False | ||
ActualCost | Double | True | False | ||
ActualCostWorkPerformed | String | True | False | ||
ActualCostWorkPerformedMilliseconds | Int | True | False | ||
ActualCostWorkPerformedTimeSpan | Time | True | False | ||
ActualOvertimeCost | Double | True | False | ||
ActualOvertimeWork | String | True | False | ||
ActualOvertimeWorkMilliseconds | Int | True | False | ||
ActualOvertimeWorkTimeSpan | Time | True | False | ||
ActualWork | String | True | False | ||
ActualWorkMilliseconds | Int | True | False | ||
ActualWorkTimeSpan | Time | True | False | ||
AvailableFrom | Datetime | True | False | ||
AvailableTo | Datetime | True | False | ||
BaselineCost | Double | True | False | ||
BaselineWork | String | True | False | ||
BaselineWorkMilliseconds | Int | True | False | ||
BaselineWorkTimeSpan | Time | True | False | ||
BudetCostWorkPerformed | Double | True | False | ||
BudgetedCost | Double | True | False | ||
BudgetedCostWorkScheduled | Double | True | False | ||
BudgetedWork | String | True | False | ||
BudgetedWorkMilliseconds | Int | True | False | ||
BudgetedWorkTimeSpan | Time | True | False | ||
Cost | Double | True | False | ||
CostVariance | Double | True | False | ||
CostVarianceAtCompletion | Double | True | False | ||
Created | Datetime | True | False | ||
CurrentCostVariance | Double | True | False | ||
Finish | Datetime | True | False | ||
IsBudgeted | Bool | True | False | ||
IsGenericResource | Bool | True | False | ||
IsOverAllocated | Bool | True | False | ||
Modified | Datetime | True | False | ||
Notes | String | True | False | ||
OvertimeCost | Double | True | False | ||
OvertimeWork | String | True | False | ||
OvertimeWorkMilliseconds | Int | True | False | ||
OvertimeWorkTimeSpan | Time | True | False | ||
PeakWork | String | True | False | ||
PeakWorkMilliseconds | Int | True | False | ||
PeakWorkTimeSpan | Time | True | False | ||
PercentWorkComplete | Int | True | False | ||
RegularWork | String | True | False | ||
RegularWorkMilliseconds | Int | True | False | ||
RegularWorkTimeSpan | Time | True | False | ||
RemainingCost | Double | True | False | ||
RemainingOvertimeCost | Double | True | False | ||
RemainingOvertimeWork | String | True | False | ||
RemainingOvertimeWorkMilliseconds | Int | True | False | ||
RemainingOvertimeWorkTimeSpan | Time | True | False | ||
RemainingWork | String | True | False | ||
RemainingWorkMilliseconds | Int | True | False | ||
RemainingWorkTimeSpan | Time | True | False | ||
ScheduleCostVariance | Double | True | False | ||
Start | Datetime | True | False | ||
Work | String | True | False | ||
WorkMilliseconds | Int | True | False | ||
WorkTimeSpan | Time | True | False | ||
WorkVariance | String | True | False | ||
WorkVarianceMilliseconds | Int | True | False | ||
WorkVarianceTimeSpan | Time | True | False | ||
LinkedCustomFields | String | False | False | ||
LinkedEnterpriseResource | String | False | False | ||
CanLevel | Bool | False | False | ||
Code | String | False | False | ||
CostAccrual | Int | False | False | ||
CostCenter | String | False | False | ||
CostPerUse | Double | False | False | ||
DefaultBookingType | Int | False | False | ||
String | False | False | |||
Group | String | False | False | ||
Initials | String | False | False | ||
MaterialLabel | String | False | False | ||
MaximumCapacity | Double | False | False | ||
Name | String | False | False | ||
OvertimeRate | Double | False | False | ||
OvertimeRateUnits | Int | False | False | ||
Phonetics | String | False | False | ||
StandardRate | Double | False | False | ||
StandardRateUnits | Int | False | False | ||
LinkedAssignments | String | False | False | ||
LinkedDefaultAssignmentOwner | String | False | False | ||
ProjectId [KEY] | String | True | False |
Id of the parent. |
Retrieve and modify projects.
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | True | False | ||
ApprovedEnd | Datetime | True | False | ||
ApprovedStart | Datetime | True | False | ||
CalculateActualCosts | Bool | True | False | ||
CalculatesActualCosts | Bool | True | False | ||
CheckedOutDate | Datetime | True | False | ||
CheckOutDescription | String | True | False | ||
CheckOutId | String | True | False | ||
CreatedDate | Datetime | True | False | ||
CriticalSlackLimit | Int | True | False | ||
DefaultFinishTime | Datetime | True | False | ||
DefaultOvertimeRateUnits | Int | True | False | ||
DefaultStandardRateUnits | Int | True | False | ||
DefaultStartTime | Datetime | True | False | ||
HasMppPendingImport | Bool | True | False | ||
HonorConstraints | Bool | True | False | ||
IsCheckedOut | Bool | True | False | ||
LastPublishedDate | Datetime | True | False | ||
LastSavedDate | Datetime | True | False | ||
ListId | String | True | False | ||
MoveActualIfLater | Bool | True | False | ||
MoveActualToStatus | Bool | True | False | ||
MoveRemainingIfEarlier | Bool | True | False | ||
MoveRemainingToStatus | Bool | True | False | ||
MultipleCriticalPaths | Bool | True | False | ||
OptimizerDecision | Int | True | False | ||
PercentComplete | Int | True | False | ||
PlannerDecision | Int | True | False | ||
ProjectSiteUrl | String | True | False | ||
ProjectType | Int | True | False | ||
SplitInProgress | Bool | True | False | ||
SpreadActualCostsToStatus | Bool | True | False | ||
SpreadPercentCompleteToStatus | Bool | True | False | ||
SummaryTaskId | String | True | False | ||
TaskListId | String | True | False | ||
WebId | String | True | False | ||
LinkedCheckedOutBy | String | False | False | ||
LinkedCustomFields | String | False | False | ||
LinkedEngagements | String | False | False | ||
LinkedEnterpriseProjectType | String | False | False | ||
LinkedPhase | String | False | False | ||
LinkedProjectSummaryTask | String | False | False | ||
LinkedQueueJobs | String | False | False | ||
LinkedStage | String | False | False | ||
LinkedUserPermissions | String | False | False | ||
CurrencyCode | String | False | False | ||
CurrencyDigits | Int | False | False | ||
CurrencyPosition | Int | False | False | ||
CurrencySymbol | String | False | False | ||
CurrentDate | Datetime | False | False | ||
DaysPerMonth | Int | False | False | ||
DefaultEffortDriven | Bool | False | False | ||
DefaultEstimatedDuration | Bool | False | False | ||
DefaultFixedCostAccrual | Int | False | False | ||
DefaultOvertimeRate | Double | False | False | ||
DefaultStandardRate | Double | False | False | ||
DefaultTaskType | Int | False | False | ||
DefaultWorkFormat | Int | False | False | ||
Description | String | False | False | ||
FinishDate | Datetime | False | False | ||
FiscalYearStartMonth | Int | False | False | ||
MinutesPerDay | Int | False | False | ||
MinutesPerWeek | Int | False | False | ||
Name | String | False | False | ||
NewTasksAreManual | Bool | False | False | ||
NumberFiscalYearFromStart | Bool | False | False | ||
OwnerId | String | False | False | ||
ProjectIdentifier | String | False | False | ||
ProtectedActualsSynch | Bool | False | False | ||
ScheduledFromStart | Bool | False | False | ||
ShowEstimatedDurations | Bool | False | False | ||
StartDate | Datetime | False | False | ||
StatusDate | Datetime | False | False | ||
TrackingMode | Int | False | False | ||
UtilizationDate | Datetime | False | False | ||
UtilizationType | Int | False | False | ||
WeekStartDay | Int | False | False | ||
WinprojVersion | Decimal | False | False | ||
LinkedAssignments | String | False | False | ||
LinkedCalendar | String | False | False | ||
LinkedIncludeCustomFields | String | False | False | ||
LinkedOwner | String | False | False | ||
LinkedProjectResources | String | False | False | ||
LinkedTaskLinks | String | False | False | ||
LinkedTasks | String | False | False |
Retrieve and modify project stages.
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | False | False | ||
Behavior | Int | False | False | ||
CheckInRequired | Bool | False | False | ||
Description | String | False | False | ||
Name | String | False | False | ||
SubmitDescription | String | False | False | ||
LinkedCustomFields | String | False | False | ||
LinkedPhase | String | False | False | ||
LinkedProjectDetailPages | String | False | False | ||
LinkedWorkflowStatusPage | String | False | False |
Retrieve and modify task links.
To query this table, the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.
SELECT * FROM TaskLinks WHERE ProjectId = '99a38309-dc1d-eb11-bf8a-00155d9c6312'
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | True | False | ||
DependencyType | Int | False | False | ||
EndId | String | False | False | ||
LinkLagDuration | String | False | False | ||
StartId | String | False | False | ||
LinkedEnd | String | False | False | ||
LinkedStart | String | False | False | ||
ProjectId [KEY] | String | True | False |
Id of the parent. |
Retrieve and modify tasks.
To query this table, the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.
SELECT * FROM Tasks WHERE ProjectId = '99a38309-dc1d-eb11-bf8a-00155d9c6312'
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | True | False | ||
ActualCostWorkPerformed | Double | True | False | ||
ActualDuration | String | True | False | ||
ActualDurationMilliseconds | Int | True | False | ||
ActualDurationTimeSpan | Time | True | False | ||
ActualOvertimeCost | Double | True | False | ||
ActualOvertimeWork | String | True | False | ||
ActualOvertimeWorkMilliseconds | Int | True | False | ||
ActualOvertimeWorkTimeSpan | Time | True | False | ||
BaselineCost | Double | True | False | ||
BaselineDuration | String | True | False | ||
BaselineDurationMilliseconds | Int | True | False | ||
BaselineDurationTimeSpan | Time | True | False | ||
BaselineFinish | Datetime | True | False | ||
BaselineStart | Datetime | True | False | ||
BaselineWork | String | True | False | ||
BaselineWorkMilliseconds | Int | True | False | ||
BaselineWorkTimeSpan | Time | True | False | ||
BudgetCost | Double | True | False | ||
BudgetedCostWorkPerformed | Double | True | False | ||
BudgetedCostWorkScheduled | Double | True | False | ||
Contact | String | True | False | ||
CostPerformanceIndex | Double | True | False | ||
CostVariance | Double | True | False | ||
CostVarianceAtCompletion | Double | True | False | ||
CostVariancePercentage | Int | True | False | ||
Created | Datetime | True | False | ||
CurrentCostVariance | Double | True | False | ||
DurationVariance | String | True | False | ||
DurationVarianceMilliseconds | Int | True | False | ||
DurationVarianceTimeSpan | Time | True | False | ||
EarliestFinish | Datetime | True | False | ||
EarliestStart | Datetime | True | False | ||
EstimateAtCompletion | Double | True | False | ||
ExternalProjectUid | String | True | False | ||
ExternalTaskUid | String | True | False | ||
FinishSlack | String | True | False | ||
FinishSlackMilliseconds | Int | True | False | ||
FinishSlackTimeSpan | Time | True | False | ||
FinishVariance | String | True | False | ||
FinishVarianceMilliseconds | Int | True | False | ||
FinishVarianceTimeSpan | Time | True | False | ||
FixedCostAccrual | Int | True | False | ||
FreeSlack | String | True | False | ||
FreeSlackMilliseconds | Int | True | False | ||
FreeSlackTimeSpan | Time | True | False | ||
IgnoreResourceCalendar | Bool | True | False | ||
IsCritical | Bool | True | False | ||
IsDurationEstimate | Bool | True | False | ||
IsExternalTask | Bool | True | False | ||
IsOverAllocated | Bool | True | False | ||
IsRecurring | Bool | True | False | ||
IsRecurringSummary | Bool | True | False | ||
IsRolledUp | Bool | True | False | ||
IsSubProject | Bool | True | False | ||
IsSubProjectReadOnly | Bool | True | False | ||
IsSubProjectScheduledFromFinish | Bool | True | False | ||
IsSummary | Bool | True | False | ||
LatestFinish | Datetime | True | False | ||
LatestStart | Datetime | True | False | ||
LevelingDelay | String | True | False | ||
LevelingDelayMilliseconds | Int | True | False | ||
LevelingDelayTimeSpan | Time | True | False | ||
Modified | Datetime | True | False | ||
Notes | String | True | False | ||
OutlinePosition | String | True | False | ||
OvertimeCost | Double | True | False | ||
OvertimeWork | String | True | False | ||
OvertimeWorkMilliseconds | Int | True | False | ||
OvertimeWorkTimeSpan | Time | True | False | ||
PercentWorkComplete | Int | True | False | ||
PreLevelingFinish | Datetime | True | False | ||
PreLevelingStart | Datetime | True | False | ||
RegularWork | String | True | False | ||
RegularWorkMilliseconds | Int | True | False | ||
RegularWorkTimeSpan | Time | True | False | ||
RemainingCost | Double | True | False | ||
RemainingOvertimeCost | Double | True | False | ||
RemainingOvertimeWork | String | True | False | ||
RemainingOvertimeWorkMilliseconds | Int | True | False | ||
RemainingOvertimeWorkTimeSpan | Time | True | False | ||
RemainingWork | String | True | False | ||
RemainingWorkMilliseconds | Int | True | False | ||
RemainingWorkTimeSpan | Time | True | False | ||
Resume | Datetime | True | False | ||
ScheduleCostVariance | Double | True | False | ||
ScheduledDuration | String | True | False | ||
ScheduledDurationMilliseconds | Int | True | False | ||
ScheduledDurationTimeSpan | Time | True | False | ||
ScheduledFinish | Datetime | True | False | ||
ScheduledStart | Datetime | True | False | ||
SchedulePerformanceIndex | Double | True | False | ||
ScheduleVariancePercentage | Int | True | False | ||
StartSlack | String | True | False | ||
StartSlackMilliseconds | Int | True | False | ||
StartSlackTimeSpan | Time | True | False | ||
StartVariance | String | True | False | ||
StartVarianceMilliseconds | Int | True | False | ||
StartVarianceTimeSpan | Time | True | False | ||
Stop | Datetime | True | False | ||
ToCompletePerformanceIndex | Double | True | False | ||
TotalSlack | String | True | False | ||
TotalSlackMilliseconds | Int | True | False | ||
TotalSlackTimeSpan | Time | True | False | ||
WorkBreakdownStructure | String | True | False | ||
WorkVariance | String | True | False | ||
WorkVarianceMilliseconds | Int | True | False | ||
WorkVarianceTimeSpan | Time | True | False | ||
LinkedCustomFields | String | False | False | ||
LinkedSubProject | String | False | False | ||
ActualCost | Double | False | False | ||
ActualFinish | Datetime | False | False | ||
ActualStart | Datetime | False | False | ||
ActualWork | String | False | False | ||
ActualWorkMilliseconds | Int | False | False | ||
ActualWorkTimeSpan | Time | False | False | ||
AddAfterId | String | False | False | ||
BudgetWork | String | False | False | ||
BudgetWorkMilliseconds | Int | False | False | ||
BudgetWorkTimeSpan | Time | False | False | ||
Completion | Datetime | False | False | ||
ConstraintStartEnd | Datetime | False | False | ||
ConstraintType | Int | False | False | ||
Cost | Double | False | False | ||
Deadline | Datetime | False | False | ||
Duration | String | False | False | ||
DurationMilliseconds | Int | False | False | ||
DurationTimeSpan | Time | False | False | ||
Finish | Datetime | False | False | ||
FinishText | String | False | False | ||
FixedCost | Double | False | False | ||
IsActive | Bool | False | False | ||
IsEffortDriven | Bool | False | False | ||
IsLockedByManager | Bool | False | False | ||
IsManual | Bool | False | False | ||
IsMarked | Bool | False | False | ||
IsMilestone | Bool | False | False | ||
LevelingAdjustsAssignments | Bool | False | False | ||
LevelingCanSplit | Bool | False | False | ||
Name | String | False | False | ||
OutlineLevel | Int | False | False | ||
ParentId | String | False | False | ||
PercentComplete | Int | False | False | ||
PercentPhysicalWorkComplete | Int | False | False | ||
Priority | Int | False | False | ||
RemainingDuration | String | False | False | ||
RemainingDurationMilliseconds | Int | False | False | ||
RemainingDurationTimeSpan | Time | False | False | ||
Start | Datetime | False | False | ||
StartText | String | False | False | ||
TaskType | Int | False | False | ||
UsePercentPhysicalWorkComplete | Bool | False | False | ||
Work | String | False | False | ||
WorkMilliseconds | Int | False | False | ||
WorkTimeSpan | Time | False | False | ||
LinkedAssignments | String | False | False | ||
LinkedCalendar | String | False | False | ||
LinkedParent | String | False | False | ||
LinkedPredecessors | String | False | False | ||
LinkedStatusManager | String | False | False | ||
LinkedSuccessors | String | False | False | ||
ProjectId [KEY] | String | True | False |
Id of the parent. |
Retrieve and modify timesheets.
Name | Type | ReadOnly | References | Required | Description |
Id [KEY] | String | False | False | ||
Comment | String | False | False | ||
LineClass | Int | False | False | ||
ProjectName | String | False | False | ||
Status | Int | False | False | ||
TaskName | String | False | False | ||
TotalWork | String | False | False | ||
TotalWorkMilliseconds | Int | False | False | ||
TotalWorkTimeSpan | Time | False | False | ||
ValidationType | Int | False | False | ||
LinkedAssignment | String | False | False | ||
LinkedTimeSheet | String | False | False | ||
LinkedWork | String | False | False | ||
PeriodId [KEY] | String | True | False |
Id of the parent. |
Retrieve and modify timesheets.
Name | Type | ReadOnly | References | Required | Description |
PeriodId [KEY] | String | False | False | ||
Comments | String | False | False | ||
EntryMode | Int | False | False | ||
IsControlledByOwner | Bool | False | False | ||
IsProcessed | Bool | False | False | ||
Name | String | False | False | ||
Status | Int | False | False | ||
TotalActualWork | String | False | False | ||
TotalActualWorkMilliseconds | Int | False | False | ||
TotalActualWorkTimeSpan | Time | False | False | ||
TotalNonBillableOvertimeWork | String | False | False | ||
TotalNonBillableOvertimeWorkMilliseconds | Int | False | False | ||
TotalNonBillableOvertimeWorkTimeSpan | Time | False | False | ||
TotalNonBillableWork | String | False | False | ||
TotalNonBillableWorkMilliseconds | Int | False | False | ||
TotalNonBillableWorkTimeSpan | Time | False | False | ||
TotalOvertimeWork | String | False | False | ||
TotalOvertimeWorkMilliseconds | Int | False | False | ||
TotalOvertimeWorkTimeSpan | Time | False | False | ||
TotalWork | String | False | False | ||
TotalWorkMilliseconds | Int | False | False | ||
TotalWorkTimeSpan | Time | False | False | ||
WeekStartsOn | Int | False | False | ||
LinkedCreator | String | False | False | ||
LinkedLines | String | False | False | ||
LinkedManager | String | False | False | ||
LinkedPeriod | String | False | False |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
Assignment_Resource | Retrieve and modify assignments. |
Assignment_Task | Retrieve and modify assignments. |
Events | Retrieve events. |
ProjectDetailPages | Retrieve project details. |
StageDetailPages | Retrieve project details.. |
Tasks_Assignments | Retrieve and modify assignments. |
Tasks_Calendar | Retrieve and modify assignments. |
TimeSheetPeriods | Retrieve timesheet periods. |
Retrieve and modify assignments.
Name | Type | References | Description |
Id [KEY] | String | The ID of the table. | |
Name | String | The name of the association. | |
ProjectId | String | The project Id. | |
AssignmentId | String | The assignment Id. |
Retrieve and modify assignments.
Name | Type | References | Description |
Id [KEY] | String | The ID of the table. | |
Name | String | The name of the association. | |
ProjectId | String | The project Id. | |
AssignmentId | String | The assignment Id. |
Retrieve events.
Name | Type | References | Description |
Id [KEY] | Int | ||
EventName | String | ||
SourceName | String |
Retrieve project details.
Name | Type | References | Description |
Id [KEY] | String | ||
Name | String | ||
PageType | Int | ||
LinkedItem | String |
Retrieve project details..
Name | Type | References | Description |
Id [KEY] | String | ||
Name | String | ||
PageType | Int | ||
LinkedItem | String | ||
StageId [KEY] | String | Id of the parent. |
Retrieve and modify assignments.
Name | Type | References | Description |
Id [KEY] | String | The ID of the table. | |
ProjectId | String | The project Id. | |
TaskId | String | The task Id. |
Retrieve and modify assignments.
Name | Type | References | Description |
Id [KEY] | String | The ID of the table. | |
Name | String | The name of the association. | |
ProjectId | String | The project Id. | |
TaskId | String | The task Id. |
Retrieve timesheet periods.
Name | Type | References | Description |
Id [KEY] | String | ||
End | Datetime | ||
Name | String | ||
Start | Datetime | ||
LinkedTimeSheet | String |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
CheckIn | Check in the specified project. |
CheckOut | Check out the specified project. |
GetOAuthAccessToken | Gets an authentication token from Microsoft Project. |
GetOAuthAuthorizationURL | Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL. |
GetOnlineCookies | Retrieves the cookies for authentication with SharePoint Online. |
Publish | Publish the specified project. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with Microsoft Project. |
Search | Searches OData using the given URL. |
Check in the specified project.
Name | Type | Description |
ProjectId | String | The ID of the project you are checking in. |
Force | String | Set to true to force check in. Default is false. |
Name | Type | Description |
Success | String | The result of the operation. |
Check out the specified project.
Name | Type | Description |
ProjectId | String | The ID of the project you are checking out. |
Name | Type | Description |
Success | String | The result of the operation. |
Gets an authentication token from Microsoft Project.
Name | Type | Description |
Scope | String | A comma-separated list of permissions to request from the user. Please check the Microsoft Project API for a list of available permissions. |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Microsoft Project app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | The verifier returned from Microsoft Project after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Name | Type | Description |
OAuthAccessToken | String | The access token used for communication with Microsoft Project. |
OAuthRefreshToken | String | The OAuth refresh token. This is the same as the access token in the case of Microsoft Project. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Name | Type | Description |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Microsoft Project app settings. |
Scope | String | A comma-separated list of scopes to request from the user. Please check the Microsoft Project API documentation for a list of available permissions. |
State | String | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Name | Type | Description |
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
Retrieves the cookies for authentication with SharePoint Online.
Name | Type | Description |
User | String | Your SharePoint Online username. |
Password | String | Your SharePoint Online password. |
STSURL | String | Your SharePoint Online STSURL. This only needs to be set if your organization uses a local ADFS server for authentication. |
Name | Type | Description |
Cookie | String | The cookies that will need to be entered in the AuthCookie connection string property. |
Publish the specified project.
Name | Type | Description |
ProjectId | String | The ID of the project you are publishing. |
CheckIn | String | Set to true to publish the proejct. Default value is false. |
Name | Type | Description |
Success | String | The result of the operation. |
Refreshes the OAuth access token used for authentication with Microsoft Project.
Name | Type | Description |
OAuthRefreshToken | String | Set this to the token value that expired. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Microsoft Project. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | This is the same as the access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Searches OData using the given URL.
Name | Type | Description |
Url | String | Full URL to use while searching OData. |
Name | Type | Description |
* | String | Output will vary for each entity. |
The ODataV1 schema implement the ProjectData service endpoints, which is read-only and can only be used for reporting.
Read-only Views are retrieved dynamically from the authenticated Microsoft Project account.
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored Procedures are function-like interfaces to Microsoft Project. Stored procedures* allow you to execute operations to Microsoft Project.
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
AssignmentBaselines | Retrieve assignment baselines. |
AssignmentBaselineTimephasedDataSet | Retrieve assignment baseline timephased dataset. |
Assignments | Retrieve assignments. |
AssignmentTimephasedDataSet | Retrieve assignment timephased dataset. |
BusinessDriverDepartments | Retrieve business driver departments. |
BusinessDrivers | Retrieve business drivers. |
CostConstraintScenarios | Retrieve cost constraint scenarios. |
CostScenarioProjects | Retrieve cost scenario projects. |
Deliverables | Retrieve deliverables. |
Engagements | Retrieve engagements. |
EngagementsComments | Retrieve engagements comments. |
EngagementsTimephasedDataSet | Retrieve engagements timephased dataset. |
FiscalPeriods | Retrieve fiscal periods. |
Issues | Retrieve issues. |
IssueTaskAssociations | Retrieve issue-task associations. |
PortfolioAnalyses | Retrieve portfolio analyses. |
PortfolioAnalysisProjects | Retrieve portfolio analysis projects. |
PrioritizationDriverRelations | Retrieve prioritization driver relations. |
PrioritizationDrivers | Retrieve prioritization drivers. |
Prioritizations | Retrieve prioritizations. |
ProjectBaselines | Retrieve project baselines. |
Projects | Retrieve projects. |
ProjectWorkflowStageDataSet | Retrieve project workflow stage dataset. |
ResourceConstraintScenarios | Retrieve resource constraint scenarios. |
ResourceDemandTimephasedDataSet | Retrieve resource demand timephased dataset. |
Resources | Retrieve resources. |
ResourceScenarioProjects | Retrieve resource scenario projects. |
ResourceTimephasedDataSet | Retrieve resource timephased dataset. |
Risks | Retrieve risks. |
RiskTaskAssociations | Retrieve risk task associations. |
TaskBaselines | Retrieve task baselines. |
TaskBaselineTimephasedDataSet | Retrieve task baseline timephased dataset. |
Tasks | Retrieve tasks. |
TaskTimephasedDataSet | Retrieve task timephased dataset. |
TimeSet | Retrieve timeset. |
TimesheetClasses | Retrieve timesheet classes. |
TimesheetLineActualDataSet | Retrieve timesheet line actual dataset. |
TimesheetLines | Retrieve timesheet lines. |
TimesheetPeriods | Retrieve timesheet periods. |
Timesheets | Retrieve timesheets. |
Retrieve assignment baselines.
Name | Type | References | Description |
AssignmentId [KEY] | String | ||
BaselineNumber [KEY] | Int | ||
ProjectId [KEY] | String | ||
AssignmentBaselineBudgetCost | Decimal | ||
AssignmentBaselineBudgetMaterialWork | Decimal | ||
AssignmentBaselineBudgetWork | Decimal | ||
AssignmentBaselineCost | Decimal | ||
AssignmentBaselineFinishDate | Datetime | ||
AssignmentBaselineMaterialWork | Decimal | ||
AssignmentBaselineModifiedDate | Datetime | ||
AssignmentBaselineStartDate | Datetime | ||
AssignmentBaselineWork | Decimal | ||
AssignmentType | Int | ||
ProjectName | String | ||
TaskId | String | ||
TaskName | String | ||
LinkedAssignment | String | ||
LinkedAssignmentBaselineTimephasedDataSet | String | ||
LinkedProject | String | ||
LinkedTask | String |
Retrieve assignment baseline timephased dataset.
Name | Type | References | Description |
AssignmentId [KEY] | String | ||
BaselineNumber [KEY] | Int | ||
ProjectId [KEY] | String | ||
TimeByDay [KEY] | Datetime | ||
AssignmentBaselineBudgetCost | Decimal | ||
AssignmentBaselineBudgetMaterialWork | Decimal | ||
AssignmentBaselineBudgetWork | Decimal | ||
AssignmentBaselineCost | Decimal | ||
AssignmentBaselineMaterialWork | Decimal | ||
AssignmentBaselineModifiedDate | Datetime | ||
AssignmentBaselineWork | Decimal | ||
FiscalPeriodId | String | ||
ProjectName | String | ||
ResourceId | String | ||
TaskId | String | ||
TaskName | String | ||
LinkedAssignment | String | ||
LinkedBaseline | String | ||
LinkedProject | String | ||
LinkedTasks | String | ||
LinkedTime | String |
Retrieve assignments.
Name | Type | References | Description |
AssignmentId [KEY] | String | ||
ProjectId [KEY] | String | ||
AssignmentActualCost | Decimal | ||
AssignmentActualFinishDate | Datetime | ||
AssignmentActualOvertimeCost | Decimal | ||
AssignmentActualOvertimeWork | Decimal | ||
AssignmentActualRegularCost | Decimal | ||
AssignmentActualRegularWork | Decimal | ||
AssignmentActualStartDate | Datetime | ||
AssignmentActualWork | Decimal | ||
AssignmentACWP | Decimal | ||
AssignmentAllUpdatesApplied | Bool | ||
AssignmentBCWP | Decimal | ||
AssignmentBCWS | Decimal | ||
AssignmentBookingDescription | String | ||
AssignmentBookingId | Int | ||
AssignmentBookingName | String | ||
AssignmentBudgetCost | Decimal | ||
AssignmentBudgetMaterialWork | Decimal | ||
AssignmentBudgetWork | Decimal | ||
AssignmentCost | Decimal | ||
AssignmentCostVariance | Decimal | ||
AssignmentCreatedDate | Datetime | ||
AssignmentCreatedRevisionCounter | Int | ||
AssignmentCV | Decimal | ||
AssignmentDelay | Decimal | ||
AssignmentFinishDate | Datetime | ||
AssignmentFinishVariance | Decimal | ||
AssignmentIsOverallocated | Bool | ||
AssignmentIsPublished | Bool | ||
AssignmentMaterialActualWork | Decimal | ||
AssignmentMaterialWork | Decimal | ||
AssignmentModifiedDate | Datetime | ||
AssignmentModifiedRevisionCounter | Int | ||
AssignmentOvertimeCost | Decimal | ||
AssignmentOvertimeWork | Decimal | ||
AssignmentPeakUnits | Decimal | ||
AssignmentPercentWorkCompleted | Int | ||
AssignmentRegularCost | Decimal | ||
AssignmentRegularWork | Decimal | ||
AssignmentRemainingCost | Decimal | ||
AssignmentRemainingOvertimeCost | Decimal | ||
AssignmentRemainingOvertimeWork | Decimal | ||
AssignmentRemainingRegularCost | Decimal | ||
AssignmentRemainingRegularWork | Decimal | ||
AssignmentRemainingWork | Decimal | ||
AssignmentResourcePlanWork | Decimal | ||
AssignmentResourceType | Int | ||
AssignmentStartDate | Datetime | ||
AssignmentStartVariance | Decimal | ||
AssignmentSV | Decimal | ||
AssignmentType | Int | ||
AssignmentUpdatesAppliedDate | Datetime | ||
AssignmentVAC | Decimal | ||
AssignmentWork | Decimal | ||
AssignmentWorkVariance | Decimal | ||
CostType_R | String | ||
FlagStatus_T | Bool | ||
Health_T | String | ||
IsPublic | Bool | ||
ProjectName | String | ||
RBS_R | String | ||
ResourceId | String | ||
ResourceName | String | ||
TaskId | String | ||
TaskIsActive | Bool | ||
TaskName | String | ||
TimesheetClassId | String | ||
TypeDescription | String | ||
TypeName | String | ||
LinkedBaseline | String | ||
LinkedProject | String | ||
LinkedResource | String | ||
LinkedTask | String | ||
LinkedTimephasedData | String |
Retrieve assignment timephased dataset.
Name | Type | References | Description |
AssignmentId [KEY] | String | ||
ProjectId [KEY] | String | ||
TimeByDay [KEY] | Datetime | ||
AssignmentActualCost | Decimal | ||
AssignmentActualOvertimeCost | Decimal | ||
AssignmentActualOvertimeWork | Decimal | ||
AssignmentActualRegularCost | Decimal | ||
AssignmentActualRegularWork | Decimal | ||
AssignmentActualWork | Decimal | ||
AssignmentBudgetCost | Decimal | ||
AssignmentBudgetMaterialWork | Decimal | ||
AssignmentBudgetWork | Decimal | ||
AssignmentCombinedWork | Decimal | ||
AssignmentCost | Decimal | ||
AssignmentMaterialActualWork | Decimal | ||
AssignmentMaterialWork | Decimal | ||
AssignmentModifiedDate | Datetime | ||
AssignmentOvertimeCost | Decimal | ||
AssignmentOvertimeWork | Decimal | ||
AssignmentRegularCost | Decimal | ||
AssignmentRegularWork | Decimal | ||
AssignmentRemainingCost | Decimal | ||
AssignmentRemainingOvertimeCost | Decimal | ||
AssignmentRemainingOvertimeWork | Decimal | ||
AssignmentRemainingRegularCost | Decimal | ||
AssignmentRemainingRegularWork | Decimal | ||
AssignmentRemainingWork | Decimal | ||
AssignmentResourcePlanWork | Decimal | ||
AssignmentWork | Decimal | ||
FiscalPeriodId | String | ||
ProjectName | String | ||
ResourceId | String | ||
TaskId | String | ||
TaskIsActive | Bool | ||
TaskName | String | ||
LinkedAssignment | String | ||
LinkedProject | String | ||
LinkedTask | String | ||
LinkedTime | String |
Retrieve business driver departments.
Name | Type | References | Description |
BusinessDriverId [KEY] | String | ||
DepartmentId [KEY] | String | ||
BusinessDriverName | String | ||
DepartmentName | String | ||
LinkedBusinessDriver | String |
Retrieve business drivers.
Name | Type | References | Description |
BusinessDriverId [KEY] | String | ||
BusinessDriverCreatedDate | Datetime | ||
BusinessDriverDescription | String | ||
BusinessDriverIsActive | Bool | ||
BusinessDriverModifiedDate | Datetime | ||
BusinessDriverName | String | ||
CreatedByResourceId | String | ||
CreatedByResourceName | String | ||
ImpactDescriptionExtreme | String | ||
ImpactDescriptionLow | String | ||
ImpactDescriptionModerate | String | ||
ImpactDescriptionNone | String | ||
ImpactDescriptionStrong | String | ||
ModifiedByResourceId | String | ||
ModifiedByResourceName | String | ||
LinkedCreatedByResource | String | ||
LinkedDepartments | String | ||
LinkedModifiedByResource | String |
Retrieve cost constraint scenarios.
Name | Type | References | Description |
ScenarioId [KEY] | String | ||
AnalysisId | String | ||
AnalysisName | String | ||
CreatedByResourceId | String | ||
CreatedByResourceName | String | ||
CreatedDate | Datetime | ||
ModifiedByResourceId | String | ||
ModifiedByResourceName | String | ||
ModifiedDate | Datetime | ||
ScenarioDescription | String | ||
ScenarioName | String | ||
SelectedProjectsCost | Decimal | ||
SelectedProjectsPriority | Double | ||
UnselectedProjectsCost | Decimal | ||
UnselectedProjectsPriority | Double | ||
UseDependencies | Bool | ||
LinkedAnalysis | String | ||
LinkedCostScenarioProjects | String | ||
LinkedCreatedByResource | String | ||
LinkedModifiedByResource | String | ||
LinkedResourceConstraintScenarios | String |
Retrieve cost scenario projects.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
ScenarioId [KEY] | String | ||
AbsolutePriority | Double | ||
AnalysisId | String | ||
AnalysisName | String | ||
ForceAliasLookupTableId | String | ||
ForceAliasLookupTableName | String | ||
ForceStatus | Int | ||
HardConstraintValue | Decimal | ||
Priority | Double | ||
ProjectName | String | ||
ScenarioName | String | ||
Status | Int | ||
LinkedAnalysis | String | ||
LinkedCostConstraintScenario | String | ||
LinkedProject | String |
Retrieve deliverables.
Name | Type | References | Description |
DeliverableId [KEY] | String | ||
ProjectId [KEY] | String | ||
CreateByResource | String | ||
CreatedDate | Datetime | ||
Description | String | ||
FinishDate | Datetime | ||
IsFolder | Bool | ||
ItemRelativeUrlPath | String | ||
ModifiedByResource | String | ||
ModifiedDate | Datetime | ||
ProjectName | String | ||
StartDate | Datetime | ||
Title | String | ||
LinkedDependentProjects | String | ||
LinkedDependentTasks | String | ||
LinkedParentProjects | String | ||
LinkedParentTasks | String | ||
LinkedProject | String |
Retrieve engagements.
Name | Type | References | Description |
EngagementId [KEY] | String | ||
CommittedFinishDate | Datetime | ||
CommittedMaxUnits | Decimal | ||
CommittedStartDate | Datetime | ||
CommittedWork | Decimal | ||
EngagementCreatedDate | Datetime | ||
EngagementModifiedDate | Datetime | ||
EngagementName | String | ||
EngagementReviewedDate | Datetime | ||
EngagementStatus | Int | ||
EngagementSubmittedDate | Datetime | ||
ModifiedByResourceId | String | ||
ModifiedByResourceName | String | ||
ProjectId | String | ||
ProjectName | String | ||
ProposedFinishDate | Datetime | ||
ProposedMaxUnits | Decimal | ||
ProposedStartDate | Datetime | ||
ProposedWork | Decimal | ||
ResourceId | String | ||
ResourceName | String | ||
ReviewedByResourceId | String | ||
ReviewedByResourceName | String | ||
SubmittedByResourceId | String | ||
SubmittedByResourceName | String | ||
LinkedComment | String | ||
LinkedTimephasedInfo | String |
Retrieve engagements comments.
Name | Type | References | Description |
CommentId [KEY] | String | ||
AuthorId | String | ||
AuthorName | String | ||
CommentCreatedDate | Datetime | ||
CommentMessage | String | ||
EngagementId | String | ||
EngagementName | String | ||
LinkedEngagement | String |
Retrieve engagements timephased dataset.
Name | Type | References | Description |
EngagementId [KEY] | String | ||
TimeByDay [KEY] | Datetime | ||
CommittedMaxUnits | Decimal | ||
CommittedWork | Decimal | ||
EngagementModifiedDate | Datetime | ||
EngagementName | String | ||
ProjectId | String | ||
ProjectName | String | ||
ProposedMaxUnits | Decimal | ||
ProposedWork | Decimal | ||
ResourceId | String | ||
ResourceName | String | ||
LinkedEngagement | String |
Retrieve fiscal periods.
Name | Type | References | Description |
FiscalPeriodId [KEY] | String | ||
CreatedDate | Datetime | ||
FiscalPeriodFinish | Datetime | ||
FiscalPeriodModifiedDate | Datetime | ||
FiscalPeriodName | String | ||
FiscalPeriodQuarter | Int | ||
FiscalPeriodStart | Datetime | ||
FiscalPeriodYear | Int |
Retrieve issues.
Name | Type | References | Description |
IssueId [KEY] | String | ||
ProjectId [KEY] | String | ||
AssignedToResource | String | ||
Category | String | ||
CreateByResource | String | ||
CreatedDate | Datetime | ||
Discussion | String | ||
DueDate | Datetime | ||
IsFolder | Bool | ||
ItemRelativeUrlPath | String | ||
ModifiedByResource | String | ||
ModifiedDate | Datetime | ||
NumberOfAttachments | Int | ||
Owner | String | ||
Priority | String | ||
ProjectName | String | ||
Resolution | String | ||
Status | String | ||
Title | String | ||
LinkedProject | String | ||
LinkedRelatedRisks | String | ||
LinkedSubIssues | String | ||
LinkedTasks | String |
Retrieve issue-task associations.
Name | Type | References | Description |
IssueId [KEY] | String | ||
ProjectId [KEY] | String | ||
RelationshipType [KEY] | Int | ||
TaskId [KEY] | String | ||
ProjectName | String | ||
RelatedProjectId | String | ||
RelatedProjectName | String | ||
TaskName | String | ||
Title | String | ||
LinkedIssue | String | ||
LinkedProject | String | ||
LinkedRelatedProject | String | ||
LinkedTask | String |
Retrieve portfolio analyses.
Name | Type | References | Description |
AnalysisId [KEY] | String | ||
AnalysisDescription | String | ||
AnalysisName | String | ||
AnalysisType | Int | ||
BookingType | Int | ||
CreatedByResourceId | String | ||
CreatedByResourceName | String | ||
CreatedDate | Datetime | ||
DepartmentId | String | ||
DepartmentName | String | ||
FilterResourcesByDepartment | Bool | ||
FilterResourcesByRBS | Bool | ||
FilterResourcesByRBSValueId | String | ||
FilterResourcesByRBSValueText | String | ||
ForcedInAliasLookupTableId | String | ||
ForcedInAliasLookupTableName | String | ||
ForcedOutAliasLookupTableId | String | ||
ForcedOutAliasLookupTableName | String | ||
HardConstraintCustomFieldId | String | ||
HardConstraintCustomFieldName | String | ||
ModifiedByResourceId | String | ||
ModifiedByResourceName | String | ||
ModifiedDate | Datetime | ||
PlanningHorizonEndDate | Datetime | ||
PlanningHorizonStartDate | Datetime | ||
PrioritizationId | String | ||
PrioritizationName | String | ||
PrioritizationType | Int | ||
RoleCustomFieldId | String | ||
RoleCustomFieldName | String | ||
TimeScale | Int | ||
UseAlternateProjectDatesForResourcePlans | Bool | ||
LinkedAnalysisProjects | String | ||
LinkedCostConstraintScenarios | String | ||
LinkedCreatedByResource | String | ||
LinkedModifiedByResource | String | ||
LinkedPrioritization | String | ||
LinkedResourceConstraintScenarios | String |
Retrieve portfolio analysis projects.
Name | Type | References | Description |
AnalysisId [KEY] | String | ||
ProjectId [KEY] | String | ||
AbsolutePriority | Double | ||
AnalysisName | String | ||
Duration | Decimal | ||
FinishNoLaterThan | Datetime | ||
Locked | Int | ||
OriginalEndDate | Datetime | ||
OriginalStartDate | Datetime | ||
Priority | Double | ||
ProjectName | String | ||
StartDate | Datetime | ||
StartNoEarlierThan | Datetime | ||
LinkedAnalysis | String | ||
LinkedProject | String |
Retrieve prioritization driver relations.
Name | Type | References | Description |
BusinessDriver1Id [KEY] | String | ||
BusinessDriver2Id [KEY] | String | ||
PrioritizationId [KEY] | String | ||
BusinessDriver1Name | String | ||
BusinessDriver2Name | String | ||
PrioritizationName | String | ||
RelationValue | String | ||
LinkedBusinessDriver1 | String | ||
LinkedBusinessDriver2 | String | ||
LinkedPrioritization | String |
Retrieve prioritization drivers.
Name | Type | References | Description |
BusinessDriverId [KEY] | String | ||
PrioritizationId [KEY] | String | ||
BusinessDriverName | String | ||
BusinessDriverPriority | Double | ||
PrioritizationName | String | ||
LinkedBusinessDriver | String | ||
LinkedPrioritization | String |
Retrieve prioritizations.
Name | Type | References | Description |
PrioritizationId [KEY] | String | ||
ConsistencyRatio | Double | ||
CreatedByResourceId | String | ||
CreatedByResourceName | String | ||
DepartmentId | String | ||
DepartmentName | String | ||
ModifiedByResourceId | String | ||
ModifiedByResourceName | String | ||
PrioritizationCreatedDate | Datetime | ||
PrioritizationDescription | String | ||
PrioritizationIsManual | Bool | ||
PrioritizationModifiedDate | Datetime | ||
PrioritizationName | String | ||
LinkedCreatedByResource | String | ||
LinkedModifiedByResource | String | ||
LinkedPrioritizationDriverRelations | String | ||
LinkedPrioritizationDrivers | String |
Retrieve project baselines.
Name | Type | References | Description |
BaselineNumber [KEY] | Int | ||
ProjectId [KEY] | String | ||
ProjectBaselineBudgetCost | Decimal | ||
ProjectBaselineBudgetWork | Decimal | ||
ProjectBaselineCost | Decimal | ||
ProjectBaselineDeliverableFinishDate | Datetime | ||
ProjectBaselineDeliverableStartDate | Datetime | ||
ProjectBaselineDuration | Decimal | ||
ProjectBaselineDurationString | String | ||
ProjectBaselineFinishDate | Datetime | ||
ProjectBaselineFinishDateString | String | ||
ProjectBaselineFixedCost | Decimal | ||
ProjectBaselineModifiedDate | Datetime | ||
ProjectBaselineStartDate | Datetime | ||
ProjectBaselineStartDateString | String | ||
ProjectBaselineWork | Decimal | ||
ProjectName | String | ||
TaskId | String | ||
LinkedProject | String |
Retrieve projects.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
EnterpriseProjectTypeDescription | String | ||
EnterpriseProjectTypeId | String | ||
EnterpriseProjectTypeIsDefault | Bool | ||
EnterpriseProjectTypeName | String | ||
OptimizerCommitDate | Datetime | ||
OptimizerDecisionAliasLookupTableId | String | ||
OptimizerDecisionAliasLookupTableValueId | String | ||
OptimizerDecisionID | Int | ||
OptimizerDecisionName | String | ||
OptimizerSolutionName | String | ||
ParentProjectId | String | ||
PlannerCommitDate | Datetime | ||
PlannerDecisionAliasLookupTableId | String | ||
PlannerDecisionAliasLookupTableValueId | String | ||
PlannerDecisionID | Int | ||
PlannerDecisionName | String | ||
PlannerEndDate | Datetime | ||
PlannerSolutionName | String | ||
PlannerStartDate | Datetime | ||
ProjectActualCost | Decimal | ||
ProjectActualDuration | Decimal | ||
ProjectActualFinishDate | Datetime | ||
ProjectActualOvertimeCost | Decimal | ||
ProjectActualOvertimeWork | Decimal | ||
ProjectActualRegularCost | Decimal | ||
ProjectActualRegularWork | Decimal | ||
ProjectActualStartDate | Datetime | ||
ProjectActualWork | Decimal | ||
ProjectACWP | Decimal | ||
ProjectAuthorName | String | ||
ProjectBCWP | Decimal | ||
ProjectBCWS | Decimal | ||
ProjectBudgetCost | Decimal | ||
ProjectBudgetWork | Decimal | ||
ProjectCalculationsAreStale | Bool | ||
ProjectCalendarDuration | Int | ||
ProjectCategoryName | String | ||
ProjectCompanyName | String | ||
ProjectCost | Decimal | ||
ProjectCostVariance | Decimal | ||
ProjectCPI | Decimal | ||
ProjectCreatedDate | Datetime | ||
ProjectCurrency | String | ||
ProjectCV | Decimal | ||
ProjectCVP | Decimal | ||
ProjectDepartments | String | ||
ProjectDescription | String | ||
ProjectDuration | Decimal | ||
ProjectDurationVariance | Decimal | ||
ProjectEAC | Decimal | ||
ProjectEarlyFinish | Datetime | ||
ProjectEarlyStart | Datetime | ||
ProjectEarnedValueIsStale | Bool | ||
ProjectEnterpriseFeatures | Bool | ||
ProjectFinishDate | Datetime | ||
ProjectFinishVariance | Decimal | ||
ProjectFixedCost | Decimal | ||
ProjectIdentifier | String | ||
ProjectKeywords | String | ||
ProjectLastPublishedDate | Datetime | ||
ProjectLateFinish | Datetime | ||
ProjectLateStart | Datetime | ||
ProjectManagerName | String | ||
ProjectModifiedDate | Datetime | ||
ProjectName | String | ||
ProjectOvertimeCost | Decimal | ||
ProjectOvertimeWork | Decimal | ||
ProjectOwnerId | String | ||
ProjectOwnerName | String | ||
ProjectPercentCompleted | Int | ||
ProjectPercentWorkCompleted | Int | ||
ProjectRegularCost | Decimal | ||
ProjectRegularWork | Decimal | ||
ProjectRemainingCost | Decimal | ||
ProjectRemainingDuration | Decimal | ||
ProjectRemainingOvertimeCost | Decimal | ||
ProjectRemainingOvertimeWork | Decimal | ||
ProjectRemainingRegularCost | Decimal | ||
ProjectRemainingRegularWork | Decimal | ||
ProjectRemainingWork | Decimal | ||
ProjectResourcePlanWork | Decimal | ||
ProjectSPI | Decimal | ||
ProjectStartDate | Datetime | ||
ProjectStartVariance | Decimal | ||
ProjectStatusDate | Datetime | ||
ProjectSubject | String | ||
ProjectSV | Decimal | ||
ProjectSVP | Decimal | ||
ProjectTCPI | Decimal | ||
ProjectTimephased | String | ||
ProjectTitle | String | ||
ProjectType | Int | ||
ProjectVAC | Decimal | ||
ProjectWork | Decimal | ||
ProjectWorkspaceInternalUrl | String | ||
ProjectWorkVariance | Decimal | ||
ResourcePlanUtilizationDate | Datetime | ||
ResourcePlanUtilizationType | Int | ||
WorkflowError | String | ||
WorkflowErrorResponseCode | Int | ||
WorkflowInstanceId | String | ||
WorkflowOwnerId | String | ||
WorkflowOwnerName | String | ||
LinkedAssignmentBaselines | String | ||
LinkedAssignments | String | ||
LinkedDeliverables | String | ||
LinkedDependencies | String | ||
LinkedIssues | String | ||
LinkedRisks | String | ||
LinkedStagesInfo | String | ||
LinkedTasks | String |
Retrieve project workflow stage dataset.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
StageId [KEY] | String | ||
LastModifiedDate | Datetime | ||
LCID | Int | ||
PhaseDescription | String | ||
PhaseName | String | ||
ProjectName | String | ||
StageCompletionDate | Datetime | ||
StageDescription | String | ||
StageEntryDate | Datetime | ||
StageInformation | String | ||
StageLastSubmittedDate | Datetime | ||
StageName | String | ||
StageOrder | Int | ||
StageStateDescription | String | ||
StageStatus | Int | ||
LinkedProject | String |
Retrieve resource constraint scenarios.
Name | Type | References | Description |
ScenarioId [KEY] | String | ||
AllocationThreshold | Decimal | ||
AnalysisId | String | ||
AnalysisName | String | ||
ConstraintType | Int | ||
ConstraintValue | Decimal | ||
CostConstraintScenarioId | String | ||
CostConstraintScenarioName | String | ||
CreatedByResourceId | String | ||
CreatedByResourceName | String | ||
CreatedDate | Datetime | ||
EnforceProjectDependencies | Bool | ||
EnforceSchedulingConstraints | Bool | ||
HiringType | Int | ||
ModifiedByResourceId | String | ||
ModifiedByResourceName | String | ||
ModifiedDate | Datetime | ||
RateTable | Int | ||
ScenarioDescription | String | ||
ScenarioName | String | ||
LinkedAnalysis | String | ||
LinkedCostConstraintScenario | String | ||
LinkedCreatedByResource | String | ||
LinkedModifiedByResource | String | ||
LinkedResourceScenarioProjects | String |
Retrieve resource demand timephased dataset.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
ResourceId [KEY] | String | ||
TimeByDay [KEY] | Datetime | ||
FiscalPeriodId | String | ||
ProjectName | String | ||
ResourceDemand | Decimal | ||
ResourceDemandModifiedDate | Datetime | ||
ResourceName | String | ||
ResourcePlanUtilizationDate | Datetime | ||
ResourcePlanUtilizationType | Int | ||
LinkedProject | String | ||
LinkedResource | String | ||
LinkedTime | String |
Retrieve resources.
Name | Type | References | Description |
ResourceId [KEY] | String | ||
CostType | String | ||
RBS | String | ||
ResourceBaseCalendar | String | ||
ResourceBookingType | Int | ||
ResourceCanLevel | Bool | ||
ResourceCode | String | ||
ResourceCostCenter | String | ||
ResourceCostPerUse | Decimal | ||
ResourceCreatedDate | Datetime | ||
ResourceDepartments | String | ||
ResourceEarliestAvailableFrom | Datetime | ||
ResourceEmailAddress | String | ||
ResourceGroup | String | ||
ResourceHyperlink | String | ||
ResourceHyperlinkHref | String | ||
ResourceInitials | String | ||
ResourceIsActive | Bool | ||
ResourceIsGeneric | Bool | ||
ResourceIsTeam | Bool | ||
ResourceLatestAvailableTo | Datetime | ||
ResourceMaterialLabel | String | ||
ResourceMaxUnits | Decimal | ||
ResourceModifiedDate | Datetime | ||
ResourceName | String | ||
ResourceNTAccount | String | ||
ResourceOvertimeRate | Decimal | ||
ResourceStandardRate | Decimal | ||
ResourceStatusId | String | ||
ResourceStatusName | String | ||
ResourceTimesheetManageId | String | ||
ResourceType | Int | ||
ResourceWorkgroup | Int | ||
TypeDescription | String | ||
TypeName | String | ||
LinkedAssignments | String | ||
LinkedResourceDemandTimephasedInfo | String | ||
LinkedTimephasedInfoDataSet | String |
Retrieve resource scenario projects.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
ScenarioId [KEY] | String | ||
AbsolutePriority | Double | ||
AnalysisId | String | ||
AnalysisName | String | ||
CostConstraintScenarioId | String | ||
CostConstraintScenarioName | String | ||
ForceAliasLookupTableId | String | ||
ForceAliasLookupTableName | String | ||
ForceStatus | Int | ||
HardConstraintValue | Decimal | ||
NewStartDate | Datetime | ||
Priority | Double | ||
ProjectName | String | ||
ResourceCost | Decimal | ||
ResourceWork | Decimal | ||
ScenarioName | String | ||
Status | Int | ||
LinkedAnalysis | String | ||
LinkedCostConstraintScenario | String | ||
LinkedProject | String | ||
LinkedResourceConstraintScenario | String |
Retrieve resource timephased dataset.
Name | Type | References | Description |
ResourceId [KEY] | String | ||
TimeByDay [KEY] | Datetime | ||
BaseCapacity | Decimal | ||
Capacity | Decimal | ||
FiscalPeriodId | String | ||
ResourceModifiedDate | Datetime | ||
ResourceName | String | ||
LinkedResource | String | ||
LinkedTime | String |
Retrieve risks.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
RiskId [KEY] | String | ||
AssignedToResource | String | ||
Category | String | ||
ContingencyPlan | String | ||
Cost | Double | ||
CostExposure | Double | ||
CreateByResource | String | ||
CreatedDate | Datetime | ||
Description | String | ||
DueDate | Datetime | ||
Exposure | Double | ||
Impact | Double | ||
IsFolder | Bool | ||
ItemRelativeUrlPath | String | ||
MitigationPlan | String | ||
ModifiedByResource | String | ||
ModifiedDate | Datetime | ||
NumberOfAttachments | Int | ||
Owner | String | ||
Probability | Double | ||
ProjectName | String | ||
Status | String | ||
Title | String | ||
TriggerDescription | String | ||
TriggerTask | String | ||
LinkedProject | String | ||
LinkedRelatedIssues | String | ||
LinkedSubRisks | String | ||
LinkedTasks | String |
Retrieve risk task associations.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
RelationshipType [KEY] | Int | ||
RiskId [KEY] | String | ||
TaskId [KEY] | String | ||
ProjectName | String | ||
RelatedProjectId | String | ||
RelatedProjectName | String | ||
TaskName | String | ||
Title | String | ||
LinkedProject | String | ||
LinkedRelatedProject | String | ||
LinkedRisk | String | ||
LinkedTask | String |
Retrieve task baselines.
Name | Type | References | Description |
BaselineNumber [KEY] | Int | ||
ProjectId [KEY] | String | ||
TaskId [KEY] | String | ||
ProjectName | String | ||
TaskBaselineBudgetCost | Decimal | ||
TaskBaselineBudgetWork | Decimal | ||
TaskBaselineCost | Decimal | ||
TaskBaselineDeliverableFinishDate | Datetime | ||
TaskBaselineDeliverableStartDate | Datetime | ||
TaskBaselineDuration | Decimal | ||
TaskBaselineDurationString | String | ||
TaskBaselineFinishDate | Datetime | ||
TaskBaselineFinishDateString | String | ||
TaskBaselineFixedCost | Decimal | ||
TaskBaselineModifiedDate | Datetime | ||
TaskBaselineStartDate | Datetime | ||
TaskBaselineStartDateString | String | ||
TaskBaselineWork | Decimal | ||
TaskName | String | ||
LinkedProject | String | ||
LinkedTask | String | ||
LinkedTaskBaselineTimephasedDataSet | String |
Retrieve task baseline timephased dataset.
Name | Type | References | Description |
BaselineNumber [KEY] | Int | ||
ProjectId [KEY] | String | ||
TaskId [KEY] | String | ||
TimeByDay [KEY] | Datetime | ||
FiscalPeriodId | String | ||
ProjectName | String | ||
TaskBaselineBudgetCost | Decimal | ||
TaskBaselineBudgetWork | Decimal | ||
TaskBaselineCost | Decimal | ||
TaskBaselineFixedCost | Decimal | ||
TaskBaselineModifiedDate | Datetime | ||
TaskBaselineWork | Decimal | ||
TaskName | String | ||
LinkedProject | String | ||
LinkedTask | String | ||
LinkedTaskBaselines | String | ||
LinkedTime | String |
Retrieve tasks.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
TaskId [KEY] | String | ||
FlagStatus | Bool | ||
Health | String | ||
ParentTaskId | String | ||
ParentTaskName | String | ||
ProjectName | String | ||
TaskActualCost | Decimal | ||
TaskActualDuration | Decimal | ||
TaskActualFinishDate | Datetime | ||
TaskActualFixedCost | Decimal | ||
TaskActualOvertimeCost | Decimal | ||
TaskActualOvertimeWork | Decimal | ||
TaskActualRegularCost | Decimal | ||
TaskActualRegularWork | Decimal | ||
TaskActualStartDate | Datetime | ||
TaskActualWork | Decimal | ||
TaskACWP | Decimal | ||
TaskBCWP | Decimal | ||
TaskBCWS | Decimal | ||
TaskBudgetCost | Decimal | ||
TaskBudgetWork | Decimal | ||
TaskClientUniqueId | Int | ||
TaskCost | Decimal | ||
TaskCostVariance | Decimal | ||
TaskCPI | Decimal | ||
TaskCreatedDate | Datetime | ||
TaskCreatedRevisionCounter | Int | ||
TaskCV | Decimal | ||
TaskCVP | Decimal | ||
TaskDeadline | Datetime | ||
TaskDeliverableFinishDate | Datetime | ||
TaskDeliverableStartDate | Datetime | ||
TaskDuration | Decimal | ||
TaskDurationIsEstimated | Bool | ||
TaskDurationString | String | ||
TaskDurationVariance | Decimal | ||
TaskEAC | Decimal | ||
TaskEarlyFinish | Datetime | ||
TaskEarlyStart | Datetime | ||
TaskFinishDate | Datetime | ||
TaskFinishDateString | String | ||
TaskFinishVariance | Decimal | ||
TaskFixedCost | Decimal | ||
TaskFixedCostAssignmentId | String | ||
TaskFreeSlack | Decimal | ||
TaskHyperLinkAddress | String | ||
TaskHyperLinkFriendlyName | String | ||
TaskHyperLinkSubAddress | String | ||
TaskIgnoresResourceCalendar | Bool | ||
TaskIndex | Int | ||
TaskIsActive | Bool | ||
TaskIsCritical | Bool | ||
TaskIsEffortDriven | Bool | ||
TaskIsExternal | Bool | ||
TaskIsManuallyScheduled | Bool | ||
TaskIsMarked | Bool | ||
TaskIsMilestone | Bool | ||
TaskIsOverallocated | Bool | ||
TaskIsProjectSummary | Bool | ||
TaskIsRecurring | Bool | ||
TaskIsSummary | Bool | ||
TaskLateFinish | Datetime | ||
TaskLateStart | Datetime | ||
TaskLevelingDelay | Decimal | ||
TaskModifiedDate | Datetime | ||
TaskModifiedRevisionCounter | Int | ||
TaskName | String | ||
TaskOutlineLevel | Int | ||
TaskOutlineNumber | String | ||
TaskOvertimeCost | Decimal | ||
TaskOvertimeWork | Decimal | ||
TaskPercentCompleted | Int | ||
TaskPercentWorkCompleted | Int | ||
TaskPhysicalPercentCompleted | Int | ||
TaskPriority | Int | ||
TaskRegularCost | Decimal | ||
TaskRegularWork | Decimal | ||
TaskRemainingCost | Decimal | ||
TaskRemainingDuration | Decimal | ||
TaskRemainingOvertimeCost | Decimal | ||
TaskRemainingOvertimeWork | Decimal | ||
TaskRemainingRegularCost | Decimal | ||
TaskRemainingRegularWork | Decimal | ||
TaskRemainingWork | Decimal | ||
TaskResourcePlanWork | Decimal | ||
TaskSPI | Decimal | ||
TaskStartDate | Datetime | ||
TaskStartDateString | String | ||
TaskStartVariance | Decimal | ||
TaskStatusManagerUID | String | ||
TaskSV | Decimal | ||
TaskSVP | Decimal | ||
TaskTCPI | Decimal | ||
TaskTotalSlack | Decimal | ||
TaskVAC | Decimal | ||
TaskWBS | String | ||
TaskWork | Decimal | ||
TaskWorkVariance | Decimal | ||
LinkedAssignments | String | ||
LinkedAssignmentsBaselines | String | ||
LinkedAssignmentsBaselineTimephasedData | String | ||
LinkedBaselines | String | ||
LinkedBaselinesTimephasedDataSet | String | ||
LinkedIssues | String | ||
LinkedProject | String | ||
LinkedRisks | String | ||
LinkedTimephasedInfo | String |
Retrieve task timephased dataset.
Name | Type | References | Description |
ProjectId [KEY] | String | ||
TaskId [KEY] | String | ||
TimeByDay [KEY] | Datetime | ||
FiscalPeriodId | String | ||
ProjectName | String | ||
TaskActualCost | Decimal | ||
TaskActualWork | Decimal | ||
TaskBudgetCost | Decimal | ||
TaskBudgetWork | Decimal | ||
TaskCost | Decimal | ||
TaskIsActive | Bool | ||
TaskIsProjectSummary | Bool | ||
TaskModifiedDate | Datetime | ||
TaskName | String | ||
TaskOvertimeWork | Decimal | ||
TaskResourcePlanWork | Decimal | ||
TaskWork | Decimal | ||
LinkedProject | String | ||
LinkedTask | String | ||
LinkedTime | String |
Retrieve timeset.
Name | Type | References | Description |
TimeByDay [KEY] | Datetime | ||
FiscalPeriodId | String | ||
FiscalPeriodModifiedDate | Datetime | ||
FiscalPeriodName | String | ||
FiscalPeriodStart | Datetime | ||
FiscalPeriodYear | Int | ||
FiscalQuarter | Int | ||
TimeDayOfTheMonth | Int | ||
TimeDayOfTheWeek | Int | ||
TimeMonthOfTheYear | Int | ||
TimeQuarter | Int | ||
TimeWeekOfTheYear | Int |
Retrieve timesheet classes.
Name | Type | References | Description |
DepartmentId [KEY] | String | ||
TimesheetClassId [KEY] | String | ||
DepartmentName | String | ||
Description | String | ||
LCID | Int | ||
TimesheetClassName | String | ||
TimesheetClassType | Int |
Retrieve timesheet line actual dataset.
Name | Type | References | Description |
AdjustmentIndex [KEY] | Int | ||
TimeByDay [KEY] | Datetime | ||
TimesheetLineId [KEY] | String | ||
ActualOvertimeWorkBillable | Decimal | ||
ActualOvertimeWorkNonBillable | Decimal | ||
ActualWorkBillable | Decimal | ||
ActualWorkNonBillable | Decimal | ||
Comment | String | ||
CreatedDate | Datetime | ||
LastChangedResourceName | String | ||
PlannedWork | Decimal | ||
ResourceName | String | ||
TimeByDay_DayOfMonth | Int | ||
TimeByDay_DayOfWeek | Int | ||
TimesheetLineModifiedDate | Datetime | ||
LinkedLastChangedByResource | String | ||
LinkedTime | String | ||
LinkedTimesheetLine | String |
Retrieve timesheet lines.
Name | Type | References | Description |
TimesheetLineId [KEY] | String | ||
ActualOvertimeWorkBillable | Decimal | ||
ActualOvertimeWorkNonBillable | Decimal | ||
ActualWorkBillable | Decimal | ||
ActualWorkNonBillable | Decimal | ||
AssignmentId | String | ||
CreatedDate | Datetime | ||
LastSavedWork | Decimal | ||
LCID | Int | ||
ModifiedDate | Datetime | ||
PeriodEndDate | Datetime | ||
PeriodStartDate | Datetime | ||
PlannedWork | Decimal | ||
ProjectId | String | ||
ProjectName | String | ||
TaskHierarchy | String | ||
TaskId | String | ||
TaskName | String | ||
TimesheetApproverResourceId | String | ||
TimesheetApproverResourceName | String | ||
TimesheetClassDescription | String | ||
TimesheetClassId | String | ||
TimesheetClassName | String | ||
TimesheetClassType | Int | ||
TimesheetId | String | ||
TimesheetLineComment | String | ||
TimesheetLineStatus | String | ||
TimesheetLineStatusId | Int | ||
TimesheetName | String | ||
TimesheetOwner | String | ||
TimesheetOwnerId | String | ||
TimesheetPeriodId | String | ||
TimesheetPeriodName | String | ||
TimesheetPeriodStatus | String | ||
TimesheetPeriodStatusId | Int | ||
TimesheetStatus | String | ||
TimesheetStatusId | Int | ||
LinkedActuals | String | ||
LinkedApproverResource | String | ||
LinkedTimesheet | String | ||
LinkedTimesheetClass | String |
Retrieve timesheet periods.
Name | Type | References | Description |
PeriodId [KEY] | String | ||
Description | String | ||
EndDate | Datetime | ||
LCID | Int | ||
PeriodName | String | ||
PeriodStatusId | Int | ||
StartDate | Datetime |
Retrieve timesheets.
Name | Type | References | Description |
TimesheetId [KEY] | String | ||
Comment | String | ||
Description | String | ||
EndDate | Datetime | ||
ModifiedDate | Datetime | ||
PeriodId | String | ||
PeriodName | String | ||
PeriodStatusId | Int | ||
StartDate | Datetime | ||
StatusDescription | String | ||
TimesheetName | String | ||
TimesheetOwner | String | ||
TimesheetOwnerId | String | ||
TimesheetStatusId | Int | ||
LinkedLines | String | ||
LinkedPeriods | String |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
GetOAuthAccessToken | Gets an authentication token from Microsoft Project. |
GetOAuthAuthorizationURL | Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL. |
GetOnlineCookies | Retrieves the cookies for authentication with SharePoint Online. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with Microsoft Project. |
Search | Searches OData using the given URL. |
Gets an authentication token from Microsoft Project.
Name | Type | Description |
Scope | String | A comma-separated list of permissions to request from the user. Please check the Microsoft Project API for a list of available permissions. |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Microsoft Project app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | The verifier returned from Microsoft Project after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Name | Type | Description |
OAuthAccessToken | String | The access token used for communication with Microsoft Project. |
OAuthRefreshToken | String | The OAuth refresh token. This is the same as the access token in the case of Microsoft Project. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Name | Type | Description |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Microsoft Project app settings. |
Scope | String | A comma-separated list of scopes to request from the user. Please check the Microsoft Project API documentation for a list of available permissions. |
State | String | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Name | Type | Description |
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
Retrieves the cookies for authentication with SharePoint Online.
Name | Type | Description |
User | String | Your SharePoint Online username. |
Password | String | Your SharePoint Online password. |
STSURL | String | Your SharePoint Online STSURL. This only needs to be set if your organization uses a local ADFS server for authentication. |
Name | Type | Description |
Cookie | String | The cookies that will need to be entered in the AuthCookie connection string property. |
Refreshes the OAuth access token used for authentication with Microsoft Project.
Name | Type | Description |
OAuthRefreshToken | String | Set this to the token value that expired. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Microsoft Project. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | This is the same as the access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Searches OData using the given URL.
Name | Type | Description |
Url | String | Full URL to use while searching OData. |
Name | Type | Description |
* | String | Output will vary for each entity. |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for Microsoft Project:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries, including batch operations:
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Name | Type | Description |
CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Name | Type | Description |
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Name | Type | Description |
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Projects table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Projects'
Name | Type | Description |
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Name | Type | Description |
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
Describes stored procedure* parameters.
The following query returns information about all of the input parameters for the SelectEntries stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2
Name | Type | Description |
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure* containing the parameter. |
ColumnName | String | The name of the stored procedure* parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
Describes the primary and foreign keys. The following query retrieves the primary key for the Projects table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Projects'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
Describes the foreign keys. The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Name | Type | Description |
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:microsoftproject:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Name | Type | Description |
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
Describes the SELECT query processing that the connector can offload to the data source.
When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.
Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
COUNT | Whether COUNT function is supported. | YES, NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES, NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES, NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES, NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source, Destination, Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE, FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES, NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES, NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE, FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE, FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures* that can be used for generating schema files. |
SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Name | Type | Description |
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Name | Type | Description |
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
The connector maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.
Microsoft Project (OData V3) | Schema |
Edm.Binary | binary |
Edm.Boolean | bool |
Edm.DateTime | datetime |
Edm.Decimal | decimal |
Edm.Double | double |
Edm.Guid | guid |
Edm.Int32 | int |
Edm.String | string |
Edm.TimeOfDay | time |
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
AuthScheme | The scheme used for authentication. Accepted entries are Auto, OAuth, OKTA, ADFS, and OneLogin. |
URL | URL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/. |
User | The Microsoft Project user account used to authenticate. |
Password | The password used to authenticate the user. |
Property | Description |
ProjectId | The ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter. |
AuthCookie | The cookie to use for authenticating to the online SharePoint server. |
Property | Description |
SSOLoginURL | The identity provider's login URL. |
SSODomain | The domain of the user when using single sign-on (SSO). |
SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
Property | Description |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
Scope | The scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read. A list of the available scopes and their aliases can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins?redirectedfrom=MSDN#permission-scope-aliases-and-the-oauthauthorizeaspx-page. |
State | Optional value for representing extra OAuth state information. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | The type of schema to use: ODataV1 or ODataV2. |
Property | Description |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Microsoft Project. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
This section provides a complete list of authentication properties you can configure.
Property | Description |
AuthScheme | The scheme used for authentication. Accepted entries are Auto, OAuth, OKTA, ADFS, and OneLogin. |
URL | URL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/. |
User | The Microsoft Project user account used to authenticate. |
Password | The password used to authenticate the user. |
The scheme used for authentication. Accepted entries are Auto,OAuth,OKTA,ADFS, and OneLogin.
string
"Auto"
This field is used to authenticate to Microsoft Project. Auto is the default option.
URL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/.
string
""
URL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/.
The Microsoft Project user account used to authenticate.
string
""
Together with Password, this field is used to authenticate against the Microsoft Project server.
The password used to authenticate the user.
string
""
The User and Password are together used to authenticate with the server.
This section provides a complete list of connection properties you can configure.
Property | Description |
ProjectId | The ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter. |
AuthCookie | The cookie to use for authenticating to the online SharePoint server. |
The ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter.
string
""
The ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter.
Note: This property is applicable only for the "ODataV2" schema. For the "ODataV1" schema, no table requires the ProjectId to be specified, as the ProjectData endpoints return data for all the available projects.The cookie to use for authenticating to the online SharePoint server.
string
""
After logging in to http://yourdomain.sharepoint.com/TeamSite, the "keep me signed in" setting must be checked in order for the connection to be established.
This section provides a complete list of SSO properties you can configure.
Property | Description |
SSOLoginURL | The identity provider's login URL. |
SSODomain | The domain of the user when using single sign-on (SSO). |
SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
The identity provider's login URL.
string
""
The identity provider's login URL.
The domain of the user when using single sign-on (SSO).
string
""
This property is only applicable when using single sign-on (UseSSO is set to true) and if the domain of the User (e.g. user@mydomain.com) is different than the domain configured within the SSO service (e.g. user@myssodomain.com).
This property may be required when using the ADFS, OneLogin, or OKTA SSO services.
Additional properties required to connect to the identity provider in a semicolon-separated list.
string
""
Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.
SSO configuration is discussed further in .
This section provides a complete list of OAuth properties you can configure.
Property | Description |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
Scope | The scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read. A list of the available scopes and their aliases can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins?redirectedfrom=MSDN#permission-scope-aliases-and-the-oauthauthorizeaspx-page. |
State | Optional value for representing extra OAuth state information. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
string
"OFF"
The following options are available:
The client ID assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
The access token for connecting using OAuth.
string
""
The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
string
""
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
The scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read. A list of the available scopes and their aliases can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins?redirectedfrom=MSDN#permission-scope-aliases-and-the-oauthauthorizeaspx-page.
string
"ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read"
The scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write.
Optional value for representing extra OAuth state information.
string
""
Optional value for representing extra OAuth state information.
The verifier code returned from the OAuth authorization URL.
string
""
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
See to obtain the OAuthVerifier value.
Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set. To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.The OAuth refresh token for the corresponding OAuth access token.
string
""
The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.
The lifetime in seconds of the OAuth AccessToken.
string
""
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
The Unix epoch timestamp in milliseconds when the current Access Token was created.
string
""
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
This section provides a complete list of SSL properties you can configure.
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of schema properties you can configure.
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | The type of schema to use: ODataV1 or ODataV2. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
string
"%APPDATA%\\MicrosoftProject Data Provider\Schema"
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
Note: Given that this connector supports multiple schemas, the structure for Microsoft Project custom schema files is as follows:
If left unspecified, the default location is "%APPDATA%\\MicrosoftProject Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:
Platform | %APPDATA% |
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
string
""
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
string
""
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
The type of schema to use: ODataV1 or ODataV2.
string
"ODataV2"
The schemas available are ODataV2 (MS Project's static schema with full CRUD support) and ODataV1 (MS Project's dynamic schema with read-only capabilities).
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Microsoft Project. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
int
-1
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
These hidden properties are used only in specific use cases.
string
""
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
The maximum number of results to return per page from Microsoft Project.
int
400
The Pagesize property affects the maximum number of results to return per page from Microsoft Project. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the connector throws an exception.
Boolean determining if simple names should be used for tables and columns.
bool
false
Microsoft Project tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the connector easier to use with traditional database tools.
Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.