odata uri help

[Anonymous]

I have a large report to monitor actuals in Project Online timesheets and MS Project data. Due to the various joins required in the tables to provide the information I need to visualise in the report, refreshing the data sometimes causes errors. To rectify this, I'm attempting to incorporate filters on the OData queries before the data is brought into the query editor. I'm having trouble with the following one though (I've replaced our tenant name below with <tenantname>). Essentially, I want to bring in the following data:

Table: Resources
Columns: ResourceId, ResourceName
Filters:
Role does not equal "Ex-RD" or "NPI"
TypeName = "Work Resource" (or ResourceType = "2")

I've tried the following:

https://<tenantname>.sharepoint.com/sites/pwa/_api/projectdata/Resources?$select=ResourceId,ResourceName&$filter=ResourceIsGeneric eq 'FALSE' and ResourceIsActive eq 'TRUE' and Role ne 'Ex-RD' or 'NPI' and TypeName eq 'Work Resource'

The above causes this error (the same error is produced with variations of the filtering but the last word is 'and' / 'or'):

I've tried the following variations:

...$filter=ResourceIsGeneric eq FALSE and ResourceIsActive eq TRUE and Role ne 'Ex-RD' or 'NPI' and ResourceType eq 2

...$filter=ResourceIsGeneric ne 'TRUE' and ResourceIsActive ne 'FALSE' and Role ne 'Ex-RD' or 'NPI' and TypeName eq 'WorkResource'

...$filter=ResourceIsGeneric ne TRUE and ResourceIsActive ne FALSE and Role ne 'Ex-RD' or 'NPI' and TypeName eq 'WorkResource'

This one does work:

...$filter=Role ne 'Ex-RD' and Role ne 'NPI' and TypeName eq 'Work Resource'

But as soon as I add one of the boolean fields (ResourceIsActive/ResourceIsGeneric), or try to combine the two Role filters together, I get the error again.

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

These two articles could help. odata-filter-tips-tricks and odata-version-2-0/uri-conventions.

It seems the Boolean type are treated as true and false, but not as strings. You can try this one:

https://<tenantname>.sharepoint.com/sites/pwa/_api/projectdata/Resources?$select=ResourceId,ResourceName&$filter=ResourceIsGeneric eq false and ResourceIsActive eq true and Role ne 'Ex-RD' and Role ne 'NPI' and TypeName eq 'Work Resource'

Best Regards!

Dale