none
Online Reports for Project Online RRS feed

  • Question

  • Hello,

    I have 2 clients with Project Server Online. 1 has Power BI and one doesn't.

    Both are interested in creating reports based on the project server which:

    1. Will be presented online

    2. Can be refreshed either online or aitomatically (scheduled)

    I created a report in Excel 2013 with OData feed to Power Query. That report can be presented in Excel Online but can't be refreshed - it needs to be refreshed in "Edit in Excel" mode. I read that Excel Online doesn't support refreshing OData connections. This is very inconvenient.

    Is there a way to go around this? Would I get better if I connect the OData to PowerPivot rather than Power Query or load the file to Power BI rather than a regular library?

    Thanks,

    Barak   


    Thursday, May 21, 2015 4:51 AM

All replies

  • Hi Barak,

    You can Schedule a background refresh with PowerBI. Note that your clients need only one unique PowerBI licence in order to do that.

    See the procedure:

    http://ppmblog.org/2014/04/20/background-refresh-your-project-online-reports/


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Thursday, May 21, 2015 5:38 AM
    Moderator
  • Hello,

    Excel Online does refresh Project Online OData Excel based report, you need to enable this feature here: https://support.office.com/en-gb/article/Grant-reporting-access-in-Project-Online-4f125cf5-a752-4ce6-b9e9-5a2eb6ace9e2

    The exception to this is if the Excel Workbook using Power Query, this has a different type of connection string and cant currently be refreshed in the browser on demand. These types of workbooks can be scheduled for data refresh if you have PowerBI available, see the link here: https://support.office.com/en-sg/article/Scheduled-Refresh-FAQ-89d80720-9df7-48e7-8602-da609ebfcea3

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, May 21, 2015 10:13 AM
    Moderator
  • Hi Paul and Guillaume and thanks for your replies.

    I tried Pauls' method to deal with Power Query.

    I managed to create the gareway and get the connection string but failed with the credentials.

    In the Excel 2013, when trying to connect Power Query to Project Online Odata, the only security method that worked for me was "Organization". Basic, anonymous and Windows didn't work for me.

    When registering the data source to the gateway, organization credentials are not an option and the rest still don't work.

    BTW, is there a place to assign permisions to the OData feed?

    Regards,

    Barak

    Thursday, May 21, 2015 8:01 PM
  • The ProjectData (OData feed) is access controlled. If using the Project Permission mode the users will need to be in a security group that has the "Access Project Server Reporting Service" global permission. if in the SharePoint Permission mode the users will need to be in either the Administrators, Portfolio Managers, Portfolio Viewers groups to access the OData API. Paul

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, May 21, 2015 8:09 PM
    Moderator
  • Hi Paul,

    The credentials I give to the data source are mine. I am a member of the administrators group (project server model) which has the permission to access project reporting service.

    Should I create an "applicative user" for the gateway?

    Can I change the authentication mode of the OData to be something other than Organization?

    Should I architect things in another way e.g. use OData as source to Excel table or Power Pivot instead of Power Query and then run Power Query on table?

    Should I break it to 2 Excels? One will have OData datasource connected to data model (Power Pivot) and the other will have an SSAS (Power Pivot) datasource connected to Power Query

    Barak


    • Edited by Barak _ Bester Saturday, May 23, 2015 8:48 AM Cosidering another idea
    Saturday, May 23, 2015 8:32 AM