none
Unable to edit sample reports in Excel RRS feed

  • Question

  • Hello,

    I'm able to view sample reports from Business Intelligence Center when I browse from the PWA homepage, but when I hit "Open in Excel" I get the following error: "To open this workbook, your computer must have a version of Microsoft Excel installed and your web browser must support opening files directly from Excel in your browser".  If I go back to the sample reports page and use the drop down beside any sample report and select "Edit in Excel" and "Open", I get the following error: "Microsoft Excel cannot access the file at ....ResourceCapacity.odc" followed by "Problems Obtaining Data" followed by "[DBNETLIB][ConnectionOpen(Connect()).]SQLServer does not exist or access denied."  Same thing happens if I try to manually create a new data source through Excel.  This happens for all sample reports, templates and data connections.

    The weird part is that if I log onto the server (using the same AD credentials that I used for my laptop), open up PWA in a browser, go to Business Intelligence Center and try to edit any sample report, template or data connection in Excel, I have no issues.  Wouldn't that mean that Excel Services and Secure Store have been set up properly?

    Wondering why no machine outside of the server can edit the reports etc...Any ideas?

    Thanks,

    André

    Monday, March 30, 2015 3:52 AM

All replies

  • Andre,

    This is by design.

    In Excel 2010/2013, the only way that the client can talk to the database is via an OLEDB connection. This requires direct read-only access and does not use Excel Services/Secure Store. If you are viewing the report via the web, then Secure Store/Excel Services is used.

    Because of the direct database access issue, you must be authenticated to the network before you can edit the reports. You would need to VPN into your network first. My chalk talk on this may be helpful. http://www.bing.com/videos/watch/video/project-server-reporting-overview/10tpfox7m

    Hope this helps.

    Treb Gatte, Project MVP | Blog | Twitter | YouTube Channel

    Monday, March 30, 2015 5:49 AM
    Moderator
  • Hi André,

    Are your machine and the DB server part of the same domain? If they are, can you reach the DB server in any way (try to define a data connection on your machine pointing to the db server)?

    It may be a matter of using the full qualified name instead of just the server name (e.g. DBServer.domain instead of just DBserver) or try updating the hosts file on your machine. Other things to look at are: firewall rules - check if the correct ports are open -, and the network settings

    Hope this helps

    Paul

    Monday, March 30, 2015 10:51 AM
  • Thanks Treb, I'll definitely check out your video!

    André

    Monday, March 30, 2015 11:49 AM
  • Hi Paul,

    Yes they're part of the same domain and I'm not able to reach the DB server by defining a data connection in Excel using the FQDN.  I've done this before with other environments without issue but for some reason I can't get it to work for this environment.

    This environment has been tricky as there's been many network security obstacles and I keep having to get the firewall rules altered.

    Any clue what ports need to be open in order to connect to the DB server?

    Thanks!

    André

    Monday, March 30, 2015 11:52 AM
  • Hi André,

    If you are using the default SQL instance then it should be 1433 - but it may be different. If it is a named instance then it is usually a different port number.

    Here it is how you can find the correct port number:

    http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/

    If you just open specific ports through firewall I would also suggest using manually specifying the ports instead of the dynamic ones

    Hope this helps

    Paul


    Monday, March 30, 2015 12:55 PM
  • Andre,

    Do you have Datareader rights to the database? That would also prevent you from accessing the data.

    Treb Gatte, Project MVP | Blog | Twitter | YouTube Channel

    Monday, March 30, 2015 4:37 PM
    Moderator
  • My ID has full rights to most of the databases on that server.  I"m assuming the important one is the Reporting database?

    Thanks,

    André

    Monday, March 30, 2015 8:37 PM
  • UPDATE:

    From Excel 2007 on my machine (I'm not able to get a newer version from the client) I'm able to now successfully create a data connection to SQL server and create pivot tables using whichever database I like.  The weird part is that I can't create a data connection using Analysis services.

    If I jump on the server and use Excel 2010, I'm able to successfully create data connections for both SQL Server and Analysis Services.

    I'm confused as to why I can't connect to Analysis Services via Excel 2007 on my machine but I have no issues connecting to SQL Server?

    Thoughts?

    Thanks!

    Andre

    Thursday, July 2, 2015 2:24 PM