none
Open Project Server 2010 Excel reports using excel RRS feed

  • Question

  • Hi All,

    In project server 2010, users are not able to open the excel reports in excel (Open in excel option in web Page) they are able to view in web mode through the configured SSO.When the report is opened in excel it is asking to select the server and then showing login failed for the user account.Could you please tell us whether it is mandatory for the users AD account to have read access in PWA reporting db to able to Open in excel.Is there any way we can take advantage of the SSO for this also.Also please let us know whether the users would be able to open the report in excel over the internet.Sharepoint Servers are connected to the internet, but the DB servers are not.


    Thanks, Lovely

    Wednesday, November 21, 2012 2:06 PM

All replies

  • Hi there,

    Have you sent up the Report Authors AD group as per this TechNet article? That pop up you are describing sounds like either your client machine cant find the SQL Server or your account doesnt have data reader access to the Reporting database. To set up the reporting access see:

    Groups required:

    http://technet.microsoft.com/en-us/library/cc197607(v=office.14).aspx See the "Accounts and groups for business intelligence"

    Add Report Authors group to the Reporting Database:

    http://technet.microsoft.com/en-us/library/ee662106(v=office.14).aspx#section1

    Populating the AD groups:

    http://technet.microsoft.com/en-us/library/ee662106(v=office.14).aspx#PopulateADGroups

    Excel will not use the secure store service (SSS), it will take the logged on user credentials. As Excel uses a direct SQL connection you would not want this to work externally over the Internet as this would be a major security issue for your SQL server.

    Hope that helps

    Paul


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

    Wednesday, November 21, 2012 8:31 PM
    Moderator
  • Hello Rose, yes this can be a bit of a training issue for clients. They see the 'open in excel' button and dutifully click on it. What happens then is like when you open an excel file from your desktop that has a data connection. At this point it is not running under secure store but tries to connect to the database specified in the connection string using your logged in credentials.

    Have them download a snapshot instead.

    As stated you can set up people in the report authors AD group. This would give them data reader rights.

    One warning from my experience however is that then this person can now write and execute queries against your reporting database. If the queries are malformed they can bring the database server down to its knees. If the reporting server is the same server as where the other project server databases are then the person running queries can affect the performance and experience of ALL other users on the server. Not good.

    good luck!


    Ray Letts Arbutus Solutions

    Wednesday, November 21, 2012 10:30 PM