none
OData and PowerPivot removes separator on actual billable hours

    Question

  • Happy new year guru's!

    Here's a nice brainteaser to start 2016 with :). I use Excel to import a OData string from Project Online. The TimesheetLineActualData Entity in OData has a Property called "ActualWorkBillable".

    If I look at the feed in IE 11 the hours show up like this 8.000000 (for 8 hours of work) and 1.500000 (for 1,5 hours of work or 1.5 if you are not Dutch I guess).

    When I write full hours any excel report shows up fine. But in the case of 1,5 hours it shows up as 15 in the datamodel. Even worse, if I have 8,25 hours it shows up as 825 in the datamodel.

    Is there a way to correct this?

    Monday, January 4, 2016 10:17 AM

Answers

  • Good news Erik - the fix for this has been produced by the Excel and SQL teams working together and will ship in the MSI updates in the August 2016 Public update 0 due to ship 2nd August.  Thanks Howie and Ash for following up!

    Best regards,

    Brian.


    Best regards,

    Brian Smith

    Senior Escalation Engineer, Project Support

    Blog: https://blogs.msdn.microsoft.com/brismith  Twitter:   LinkedIn:   Facebook:

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    • Marked as answer by Erik van Hurck Thursday, September 29, 2016 12:40 PM
    Sunday, July 24, 2016 4:55 PM
    Owner

All replies

  • Hi Erik,

    It looks like a glitch with the regional settings. Your system seems to look for a point as a separator whereas you have a comma. Have you tried changing the regional settings on your machine?


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCC |

    Monday, January 4, 2016 10:42 AM
    Moderator
  • Hi Guillaume,

    Good to see you up and about. However this doesn't change anything on my side. I have set everything to US settings and refreshed the data but still the information comes in as not separated.

    And even if it did, I should be able to provide a Dutch client with a Dutch report on a Dutch Project Online on a Dutch system right?

    Erik

    Monday, January 4, 2016 10:49 AM
  • Indeed Erick. I think the key point is to align the settings between your PO instance and your machine. But since you've tried it and it didn't work, it seems not to be the issue...

    Maybe someone playing a lot with Odata will jump in (I particularly think about Paul) and tell us more about it.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCC |

    Monday, January 4, 2016 10:55 AM
    Moderator
  • Thanks for the confirmation, let's hope Paul hasn't won any New Year's Lottery (sorry Paul, we need you).
    Monday, January 4, 2016 10:57 AM
  • Hi Erik,

    I have just tried and can not reproduce (sitting in Austria, so using same regional formatting). Also using my VM with regional settings US, everything is working as expected.

    Moreover, I switched my regional settings in Project Online to Dutch and also my client settings, everything in different combinations, but I am not able to reproduce.

    Feel free to contact me offline (see profile - HP - Kontakt), so I can provide a temporary password to my tenant. So you could try if it is tenant or client dependant.

    Groetjes
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Monday, January 4, 2016 11:16 AM
    Moderator
  • Groetjes? haha nice,

    Barbara, what Excel version are you using?

    I am testing now on multiple machines, and seeing different results with the different Excel's.

    On a Excel 2013 Dutch machine I get the correct values

    On a Excel 2013 PowerQuery machine I get the correct values

    On an earlier created (in Excel2016) version of the report I get the corect values.

    On two Excel 2016 (Office 365 streamed version) Dutch / English, newly created reports, I get the incorrect values but when I use PowerQuery in stead of loading the data directly I get the correct values.

    So my current conclusion is:

    Excel Office 365 changed during the year switch.

    -------------------------

    Edition:

    I have a Excel file with a normal data source (data, from other sources, OData). And when I view the Excel in Online (browser) I start with an sheet that has a Work value 2666667 for a value that should read 2,67 (rounded up to 2 decimals).  But when I refresh the data online it creates the correct value. But when I do the same, with the same file but using the Excel (365) client it produces the 2666667 value.


    Monday, January 4, 2016 1:50 PM
  • Hi,

    there we are ... Click2Run of Excel is causing issue. I am only working with MSI versions (I want to know, when I get a new version :)).

    Perhaps your question will be answered faster in https://social.technet.microsoft.com/Forums/en-US/home?forum=officeitpro. I assume, attention of Excel guys is more important than from Project people?

    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Monday, January 4, 2016 2:22 PM
    Moderator
  • Thanks Barbara, I have posted the issue on the forum you suggested. Hope they will be able to provide an answer:

    https://social.technet.microsoft.com/Forums/en-US/ac07578f-089e-4347-86f6-f1ff34e48337/odata-and-powerpivot-removes-separator-on-actual-billable-hours-project-online-datasource?forum=officeitpro

    Kind regards,

    Erik

    Monday, January 4, 2016 2:30 PM
  • Hi Erik,

    I have just got a confirmation of another person using Excel C2R, seeing the same issue. Since you have no response on your other post, I suggest to open a support ticket in Project Online or O365 to get Microsoft's attention on this issue.

    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Wednesday, January 6, 2016 8:25 AM
    Moderator
  • Hi Barbara,

    Done, thanks for taking the time to look into this situation. I'll let you know if there is any news.

    Erik

    Update 11-01-16: it got reported as a bug. Hope they fix it soon.

    Update 15-01-16: I updated my Click 2 run version to version 16.6001.1054. Sad to say it still has the bug.

    Update 19-02-2016: I updated my Click 2 run version to version 16.6001.1061. Again no change in the bug. Will keep you posted on any progress.
    • Edited by Erik van Hurck Friday, February 19, 2016 8:37 AM New information.
    Thursday, January 7, 2016 7:53 AM
  • I'm seeing this behavior also, but only in excel 2016, excel 2013 no problems when refreshing odata.
    And not only in ActualBillable (WerkelijkeHoeveelheidWerk) but also in WerkelijkeKosten (sorry, don't know the native name).

    Edit: Just created an odata connection in excel 2016 to our projectdata.
    Incorrect data
    Saved file.
    Opened on other pc with excel 2013.
    Still incorrect.
    Refreshed feed.
    Data correct.

    Looks like the handling of the data, not the data itself.

    • Edited by Bronstijn Friday, January 15, 2016 10:49 PM
    Friday, January 15, 2016 10:24 PM
  • Hi Bronstijn,

    This seems to be consistent with the other replies. The tool Excel Click 2 Run (2016 office 365 version) doesn't respond correctly to the data. I have reported this to Microsoft and they did escalate it to their engineers, however, almost 4 weeks later there still isn't any change in the case.

    Let's keep an eye out for this bug and try using older software in the mean while ;-).

    PS: The issue doesn't show up when you use PowerQuerry, but that data connection will not allow in browser refresh haha.

    kind regards,

    Erik

    Thursday, February 4, 2016 8:12 AM
  • Just another little update:

    The information still showes up wrong on the Excel client report. But when I upload it to SharePoint Online, and look at the data on the browser and refresh the data it show's up correct there.

    Conclussion:

    Excel click 2 run still has the bug.

    Excel Online has a workaround that provides the correct values.

    This beeing said, I believe I've noticed this before... But I want to have Microsoft look at this post, because I want Excel Click 2 Run to be fixed :(.

    Monday, February 8, 2016 4:03 PM
  • Excel 2016 with the February update: 16.0.6366.2068

    Same behaviour, anyone with more prospective news or observations?

    Thursday, February 11, 2016 1:43 PM
  • Hi Erik!

    We're working with odata between Navision and Excel and have the same Problem. With Excel 2013 the numbers are shown up correctly but with 2016 the comma Position is getting crazy.

    Did you found a solution or workaround meanwhile?

    king regards,
    Dominik

    Wednesday, April 6, 2016 6:37 AM
  • Dominik,

    It's still a sad, sad world if you are using Excel C2R... There has been no feedback from Microsoft after I made the call and they confirmed it as a bug. I would advice you to use a Excel 2016 MSI installment instead of the streamed version, just like Barbara said in her response.

    Maybe if you could raise a call with Microsoft as well it would get some more priority?

    Kind regards,

    Erik

    Wednesday, April 6, 2016 6:41 AM
  • We're using a VL of Office 2016 and its installed from an ISO. So i'm not sure but i think this is'nt a C2R Verison?

    King regards,
    Dominik

    Wednesday, April 6, 2016 6:51 AM
  • Ping me offline with the case number you have open Erik - I'll see if I can find out what's happening with the bug - then I'll respond back to this thread.

    Best regards,

    Brian.


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Friday, April 15, 2016 8:32 PM
    Owner
  • Thanks Brian, I'm hoping for some good news :)
    Monday, April 18, 2016 6:53 AM
  • Another 6 weeks past and we can still only refresh data in excel online...
    Them is sure taking their time ;)
    Thursday, June 2, 2016 9:17 PM
  • Indeed Bronstijn,

    This is a case that has now been open for half a year. I've mailed Brian again based on the request he has done above.

    The progress he has made: het contacted his manager and there has been communication between the Project and Excel guru's. However, there hasn't been any contact after that.

    This is starting to annoy not only me, but my clients are also viewing this post...

    Friday, June 3, 2016 8:01 AM
  • Same problem here in Denmark. We're using comma as decimal separator, and Excel 2016 can't handle it. Really no fix after 6 months?
    Thursday, June 16, 2016 2:49 PM
  • Indeed, no answer at all. No news and no communication since the last chat with Brian. The Excel Click 2 Run still has the issue with "no English" speaking countries it seams.

    I'll keep an eye out for any change, hope everyone will do the same. I know I'll grab a beer and a party hat once it's fixed! We are really up the wall here with clients that are saying "I can't work with this, we are moving back to Office MSI's or on prem installations".

    Monday, June 20, 2016 6:05 PM
  • Today Microsoft Launched another big update for Excel. It's now on version 16.0.6741.2048 on my machine.

    But apart from the great new features (uploading to PowerBI yeah baby!) this issue is still a huge pain in the ...

    Microsoft PLEASE I'm on my knees here

    Friday, June 24, 2016 10:09 AM
  • Hi Guys,

    I've added the bug to the Excel UserVoice website. Please vote on my item to get more attention to Microsoft to fix this. Here is the link

    Thanks in advance,

    Erik

    Friday, July 8, 2016 6:49 AM
  • Good news Erik - the fix for this has been produced by the Excel and SQL teams working together and will ship in the MSI updates in the August 2016 Public update 0 due to ship 2nd August.  Thanks Howie and Ash for following up!

    Best regards,

    Brian.


    Best regards,

    Brian Smith

    Senior Escalation Engineer, Project Support

    Blog: https://blogs.msdn.microsoft.com/brismith  Twitter:   LinkedIn:   Facebook:

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    • Marked as answer by Erik van Hurck Thursday, September 29, 2016 12:40 PM
    Sunday, July 24, 2016 4:55 PM
    Owner
  • Awesome! That is great news, I'll make sure to mark the respons as answered once it's been released and provides the desired solution :)

    Thanks again for taking your time to look into this and escalating.

    Wednesday, July 27, 2016 2:43 PM
  • It's 8 august now, have seen a small change in the version number of Excel. I'm on 16.06741.2056

    But the issue remains. Now maybe this is due to a different role-out cycle in the EMEA region. Can any of the other responders reply with their results?

    Kind regards,

    Erik

    Monday, August 8, 2016 6:50 AM
  • Hi Eric,

    our clients have been on 16.0.7070.2033 for a while now, this updated arrived mid/end July, and it does not contain the August fixes for MS Project (Subproject Links, 2 bugs), so it's unllikely to contain the August fixes for Excel.

    Note that I did get some tentative feedback from 1 customer: They said they deployed the MSI patches for August, the error persists in existing Excel reports when refreshed, but the decimal data is correct when creating a new report (well, "report" in the sense of opening Excel and pulling the OData).

    Does that help you a bit in the meantime?

    Kind regards,
    Adrian

    Monday, August 8, 2016 12:38 PM
  • That looks like the deferred channel Erik - see https://technet.microsoft.com/en-us/office/mt465751.aspx.

    If you want to get the fixes earliest then Current channel is where you need to be.  I don't see an update on that page for the August release yet, but my Excel (which may be pre-release as it is internal - is 16.0.7227.1000.  7070 looks like the July release for the current channel.

    Best regards,

    Brian


    Best regards,

    Brian Smith

    Senior Escalation Engineer, Project Support

    Blog: https://blogs.msdn.microsoft.com/brismith  Twitter:   LinkedIn:   Facebook:

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Monday, August 8, 2016 1:51 PM
    Owner