As a consultant for Microsoft Project, often we have to give demos to potentials clients to show the amazing things that Microsoft Project and Microsoft Project Server can do for them. The Project Server Demo image (2010 Information Worker Demonstration and Evaluation Virtual Machine) created by Microsoft is excellent in terms of showing important features with rich demo data.
- Open Project Web Access site https://project.contnso.com/pwa
- Click on Business Intelligence and then Corporate Dashboard (from Quick Launch)
- Select Any report from KPI, Summary Dashboard, Project Status, Timesheet Dashboard
- Two reports “Forecasted Cost” and “Risks and Issue Count” will display data
- However report on the right had side “Project Cost Per Department” and “Resource Availability by Role” will always display “Loading”
I was able to reproduce this issue every time I built a new Hyper V machine using setup files from below (For Project Server we need only Virtual machine “a”)
This issue is not specific to these customized reports. It can be reproduce using sample reports as well. Below is the screenshot of the Summary Dashboard Report
Following are the screenshots of the issue
If you enable Internet Explorer Developer Tools by pressing F12 key, the following error is displayed on the Script tab. (Make sure to refresh Excel report after launching developer tools)
A similar error is displayed on the Summary Dashboard report
After enabling verbose ULS logging for Excel Calculation Services, you will see error similar to the one below:
Exception ExcelService.PostProcessRequest: Excel Server Exception: System.TypeInitializationException: The type initializer for 'Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp' threw an exception. ---> System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401) at Microsoft.Office.Server.Diagnostics.ULS.SendWatsonOnExceptionTag(UInt32 tagID, ULSCatBase categoryID, String output, Boolean fRethrowException, TryBlock tryBlock, CatchBlock catchBlock, FinallyBlock finallyBlock) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp..ctor() at Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp..cctor() --- End of inner exception stack trace e.Excel.Server.CalculationServer.ExcelServiceBase.SafeSetContext() at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceBase.BeginProcessOperation(CommandParameter parameter, WebMethodBehaviorAttribute webMethodBehavior, WebMethodType webMethodType, AsyncCallback callback, Object state, UserOperation operation) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceSoap.BeginGetHealthScore(AsyncCallback callback, Object asyncState) at AsyncInvokeBeginBeginGetHealthScore(Object , Object , AsyncCallback , Object ) at System.ServiceModel.Dispatcher.AsyncMethodInvoker.InvokeBegin(Object instance, Object inputs, AsyncCallback callback, Object state) System.TypeInitializationException: The type initializer for 'Microsoft.Office.Excel.Server.CalculationServer
ExcelServerApp' threw an exception. ---> System.IO.FileLoadException
Existing Excel Service Calculation Service application is corrupt
Fixing this issue is a three stage process
A. Create root (/) site collection for PWA site (which is currently not present)
B. Provision new excel service application, Change the association with web application
C. Re-configure the excel services settings for new excel service application
A: Create root site collection for PWA site
- Navigate to Application Management and click Create New site collection
- Click on the dropdown and then Change Web Application
- Select Project
- Enter Title and make sure that root site (/) is selected for https://project.contoso.com . You may also select any template
B: Second step , After creating root site collection, we need to create new Excel Service Calculation Service application.
- From Central admin click on Application Management from left pane and then Manage Service Applications located under Service Applications section
- Click New from ribbon menu and select Excel Services Application
- Enter name for new excel service application and also make sure to create new application pool
- Once new Excel calculation service application is created we need to change the association and set as “default”
- Click Application Management from left pane and then “Configure Service Application Association”
- Click Default Application Proxy group
- Clear the check box for existing Excel Service application and check newly created Excel Services Application 2
- Also click Set as default for Excel Services Application 2
- New setting will look like this
C. Third step is to re-configure trusted file locations and Trusted Data connection libraries settings for new excel service application.
- From Central admin, click Application management, Manage Service Application and then Excel Services Applications 2 or the one which you have created earlier.
- Click Trusted File Location
- Click Add Trusted file location
- In the Address box type https://project.contoso.com/PWA/ProjectBICenter
- In the Trust Children section, confirm that the Children trusted check box is selected
- In the External Data section
Allow External Data section, select the Trusted data connection libraries and embedded option.
In the Warn on Refresh section, clear the Refresh warning enabled check box
- Leave the remaining options at their default value, and then click OK.
- To set up trusted data connection libraries, In Central Administration, in the Application Management, click Manage Service Applications
- Click the Excel Services service application 2
- Click Trusted Data Connection Libraries.
- Click Add Trusted Data Connection Library
- In the Address box , paste following URL and click ok
https://project.contoso.com/PWA/ProjectBICenter/Data Connections for PerformancePoint/English (United States)/
- Once again click Add Trusted Data Connection Library and past following URL in the Address box
https://project.contoso.com/PWA/ProjectBICenter/Data Connections for PerformancePoint/English (United States)/DEMO2010A - ProjectServerDemo/
- Click OK
- Next steps is to refresh secure store configuration by accepting existing settings.
- In Central Administration ,Click Application Management , then click Manage Services Applications
- Click Secure Store Service and then ProjectServerApplication
- Click next to accept existing settings.
- Click OK
- From Secure Store home page , check “ProjectServerApplication” and from the ribbon menu click Set credentials
- Enter user name Contoso\administrator and password for the same
- Whenever we make changes to Excel Services or Secure store, restarting Excel Calculations and Secure Store service helps to force the changes immediately
- From Central admin , click System Settings and Manage Services on Server
The result is Dashboard pages that load properly:
Once both the services are restarted validate the result by accessing Summary dashboard report and other reports which we discussed