Project Online: Having problems filtering with strings in OData?


***4/26/2016 - You should no longer need to encode strings as our Cloud BI team have deployed some updates to resolve the issue of filtering with strings - if you don't see it fixed yet tehn it should be very shortly.  Sorry for the inconvenience. ***

*** 11/30/2015 - Quick update - again thanks Alex - the suggested workaround for getting an encoded string only works in Chrome.  Edge, IE and Firefox do not encode the string - and Edge doesn't even fetch the data...  To encode the Url yourself though - you can simply replace spaces with %20 and the ' characters with %27.  ***

Thanks to Alex for the idea for this blog – and for working with our Excel colleagues to come up with this workaround for our customers.  The issue here is that strings used for filtering are working just fine in Excel when pulling OData from Project Online – but the reports created from these Excel files are not refreshing on the web.

It is a very simple report, just selecting 4 fields and filtering based on the project name.  This is the error when I try to refresh in Excel Online:

image

In Excel it refreshes just fine.  The link I am feeding into Excel when creating the connection to OData is the following:

https://brismithpjo.sharepoint.com/sites/pwa/_api/ProjectData/Projects?

$select=ProjectName,ProjectStartDate,ProjectFinishDate,ProjectDuration&

$filter=ProjectName eq 'BriSmithAlertTest654'

I’m selecting the 4 fields, and using a string to filter for ProjectName eq 'BriSmithAlertTest654'

image

The trick here that will resolve the issue and allow Excel Online to refresh without error is to encode the string – and the easiest way is just to copy into a browser.  So taking the string above and pasting into your favorite browser:

image

and then refreshing will change the Url to:

image

and return the results.  That screen shot isn’t easy to read – so breaking out the new encoded Url and splitting it as I did the un-encoded one we can see”:

https://brismithpjo.sharepoint.com/sites/pwa/_api/ProjectData/Projects?

$select=ProjectName,ProjectStartDate,ProjectFinishDate,ProjectDuration

&$filter=ProjectName%20eq%20%27BriSmithAlertTest654%27

Notice the spaces and quote characters are replaced by their ASCII equivalents in the filter section.  You can then use this string as the link for Excel, save to the Reports document library and then the report will refresh with no problems!  I don’t think there are any current plans to fix the issue with the un-encoded strings in Excel Online causing the refresh issue so best to use this approach when filtering with strings.  I understand there may be other reporting options that still don’t like the encoded strings – and if you are finding scenarios that don’t work for you then best open a support incident so we can get it investigated.

Comments (3)

  1. anonymouscommenter says:

    Thank You Brian and Alex its works....

    1. We are still investigating a few scenarios where this isn't as smooth - hopefully some fixes coming.

  2. Steve says:

    Hi Brian, thanks for this post. I'm attempting to pull a subset from the AssignmentTimephasedDataSet.

    From Excel 2013 Connection Properties/Connection string:
    Url="https://[pwa path]/_api/ProjectData/[en-US]/AssignmentTimephasedDataSet?$select=*&$filter=ResourceId%20eq%20%27dcaa2156-5886-e511-80d6-00155da0601b%27&$top=10"

    Results in the following error:
    A binary operator with incompatible types was detected. Found operand types 'Edm.Guid' and 'Edm.string' for operator kind 'Equal'.

    I've also tried treating the ResourceID as a number (with and without encoding "-" as "%2D" as well as "space" as "%20")
    Like so, "ResourceId eq dcaa2156%2D5886%2De511%2D80d6%2D00155da0601b"
    Reference: http://stackoverflow.com/questions/28995757/a-binary-operator-with-incompatible-types-was-detected-found-operand-types-edm

    Any suggestions? I'm out of ideas to try.

    Thanks again!
    Steve

Skip to main content