Excel Services & SharePoint 2010 = Confusion!

Over the past few weeks I have received some calls regarding issues with Excel Services and namely, rendering from the browser. Let’s not forget that Excel Services in SharePoint is a whole lot more than the ability to be able to publish and open a workbook without requiring a native client. Business Intelligence scenario’s which allow complex workbooks to connect with external resources and update dynamically, are now becoming fundamental in the corporate workplace and Microsoft have invested a lot of time and effort ensuring Excel Services is still a major reason for investing in SharePoint. (and there’s more offerings to come in SharePoint 2013)

The Excel Services SharePoint 2010 Service Application in its most basic, simplistic and perhaps convention role, allows users to open a workbook in a browser – no great shakes there. However, it doesn’t work OOTB, and therefore in this blog I will show you what to look for if you receive errors when you choose the ‘open in browser’ option.

  • Create ‘Excel Services’ Service Application (ESA)

This can be achieved via Central Admin or Powershell. Remember to start the service once created. If you don’t have an ESA provisioned, you will receive the following error when you attempt to render from a browser:

 

And if you haven’t started the ‘Excel Calculation Services’ from within ‘Manage Services on Server’, you receive the following:

 

  • Grant IIS App Identity SQL access to Content Database

As a best practice, the IIS application pool identity of your Excel Services Application should be a domain/service account. If this is the case (and it should be), this service account needs to be granted SQL ‘dbo’ permissions against the Content Database of your Site Collection(s). This setting is not applied automatically even when you provision Excel Services and determine which proxy group it becomes a member of. If you miss this step, you will see the following error:

Which corresponds to the following in the ULS logs:

Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSessionException: The workbook cannot be opened. at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     

Excel Services should now be your default option for open Excel workbooks, although this can be changed on a per Document Library basis if there is a requirement to use a local Excel client instead.

Document Library > Library Settings > Advanced Settings > Default open behaviour for browser-enabled documents: – ‘Open in the client application’


The screenshot above is the default OOTB option, although there is a SharePoint Feature which changes the third option ‘Use the server default (Open in the browser), to ‘Use the server default (Open in the client application) Once activated, all Document Libraries will open in the Client unless directed otherwise



  • And don’t get confused with the Office Web Apps suite!

Remember, if you want to use the Office ribbon to compliment browser rendering, you will have to purchase an ‘Office Web Apps’ license and provision as a convention Service Application:

 

 

 

 

 

Advertisements

The road to SharePoint 2013 MCSE

This week I took my first steps towards gaining the reinvented SharePoint 2013 MCSE by taking (and passing thankfully) the ‘Upgrading Your Skills to MCSA Server 2012’ exam. (70-417).

Microsoft have completely revamped the certification process and personally, I believe this is an astute decision. In order to gain your SharePoint 2013 MCSE, you first need to become a MCSA in Server 2012. So, what MS are essentially saying is that in addition to understanding the application layer, you now need to have an equal appreciation of the infrastructure it’s running on.

There are two routes towards gaining your MCSA 2012, one of which is a handy shortcut, if, like me, you already hold an MCITP (SharePoint 2010).

Route 1 (New to MCSA or hold an MCTS)

Prepare yourself for the long-haul! The Server 2012 MCSA consists of three exams and around 15 days of instructor led tuition (if you have the time and budget). The exams are as follows:

  • 70-410 Installing and Configuring Windows Server 2012
  • 70-411 Administering Windows Server 2012
  • 70-412    Configuring Advanced Windows Server 2012 Services

Route 2 (Upgrade your MCITP or MCSA Server 2008)

This is the shortcut route I mentioned earlier which allows you to earn your MCSA Server 2012 by virtue of sitting ONE exam. And you don’t have to hold the SharePoint 2010 MCITP, the same principle applies if you are certified in Lync, Exchange amongst others. The full list is available from the following link:

http://www.microsoft.com/learning/en/us/mcsa-windows-server-certification.aspx#fbid=aXGmuk-0q5u

Two SharePoint exams then have to be passed (70-331) and (70-332)

http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-331#fbid=aXGmuk-0q5u

http://www.microsoft.com/learning/en/us/Exam.aspx?ID=70-332#fbid=aXGmuk-0q5u

The pass mark for exam number ’70-417′ is 700 out of 1000 – I managed to scramble up to 833. My training consisted of 5-day instructor led tuition and then around 10 weeks of follow-up study using a number or low-spec Server 2012 machines in a test environment. The course itself covered all of the new features and functionality in Server 2012 and highlighted exactly what has changed since Server 2008.

As I don’t come from and Server Infrastructure background, I found the course very demanding as around 50% of the content was a completely alien to me – hence why it has taken me so long to sit the exam since attending the course. All of the other delegates were infrastructure bods with a solid understanding of Server 2008 (most held the Server 2008 MCSA). And they were happily sitting the exam on Day 5 of the course!

Additional learning resources I used consisted of the following:

http://www.amazon.co.uk/Exam-Ref-70-417-Upgrading-Windows/dp/0735673047/ref=sr_1_1?ie=UTF8&qid=1365091310&sr=8-1&keywords=70-417

http://www.microsoftvirtualacademy.com/ (search for ‘Server 2012’)

I will follow-up this blog post with a series of articles titled ‘How I passed Exam 40-417″. Hopefully the tips I can pass on can be beneficially to others who hope to sit this exam.