Project Server 2010 - Configure Excel Data Connections

I have seen quite frequent postings related to the excel services configuration, but the good thing is that Microsoft is supporting the Project Server 2010 the way they never done it before. Details postings related to every issue can be found on TechNet, MVP blogs etc. to help users.

Few days back I came across this issue for which I was unable to find any specific solution on Google. It was straight forward though, but still worth posting.

You will face this if you create a excel report using one of the cubes and try to open the report in your browser and refresh the data. There are some very useful articles in TechNet available to configure the excel services to work with Project Server data. Following the steps explained in that article will let you open the built-in reports and data connections.

But when you configure your cube to be build, the system will save the Cubes on a predefine location i.e.: within Business Intelligence -> Data Connection, a new folder will be created <server name-cube name> to save the cube. And when you use cube to create report in excel and publish to the web, the system will decline to authorize the data connection when refresh because the cube location is not defined in the Trusted Data Connection library.

The only location you define following the article is the OOB connection library. Any new data connection you create, you also have to define in the trusted data connection library to enable to refresh the data.

Following are the screen shots to explain the issue.

Fig-1 shows the error:

“The data connection path in the workbook points to a file on the local drive or is an invalid URL…..”

Fig -2: shows the folder system created to store the Cubes. You will find the difference between the URLs if you verify this location with the location you have saved in the trusted data connection library.

Fig -3: If you click on the folder, you will see all the cubes created. The modified date shows the latest Cube built date.

Fig -4: Define the path of the cube folder in the trusted connection.

Fig-1

Fig -2

Fig -3

Fig -4

Conclusion: the solution is pretty simple, for every custom data connection – you have to define the location in the trusted connection library.

Posted in: Project Server 2010 - Excel Services Configurations