Published
Mar 06 2019 02:12 PM
1,552
Views
First published on TECHNET on Apr 26, 2016
***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:
In Excel it refreshes just fine. The link I am feeding into Excel when creating the connection to OData is the following:
I’m selecting the 4 fields, and using a string to filter for ProjectName eq 'BriSmithAlertTest654'
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:
and then refreshing will change the Url to:
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”:
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.
***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:
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'
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:
and then refreshing will change the Url to:
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.