Excel Services | Are you a user?

Archive for the 'Excel Services' Category

Jul

The Workbook Cannot be Opened–Excel Services SharePoint 2010

Categories: Excel Services, SharePoint Errors and SharePoint Infrastructure

I had recently applied Service Pack 1 to my SharePoint instance and was trying to open a workbook with Excel Services and was getting the error “the workbook cannot be opened.”

The SharePoint logs were filled with this, the main issue being the message “Cannot open database “WSS_Content_SPRX2010” requested by the login. The login failed. Login failed for user ‘RX\spservice’. Continue reading after all the log messages.

07/22/2012 17:02:53.56     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880i    High        System.Data.SqlClient.SqlException: Cannot open database "WSS_Content_SPRX2010" requested by the login. The login failed. Login failed for user ‘RX\spservice’.     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)     at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire,…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.56*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880i    High        … SqlConnection owningObject)     at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)     at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, D…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.56*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880i    High        …bConnectionPool pool, DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     at System.Data.SqlClient.SqlConnection.Open()     at Microsoft.SharePoint.Utilit…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.56*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880i    High        …ies.SqlSession.OpenConnection()    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High           at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)     at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, Boolean retryForDeadLock)     at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command)     at Microsoft.SharePoint.Upgrade.SPDatabaseSequence.GetVersion(SPDatabase database, Guid id, Version defaultVersion, SqlSession session, SPDatabaseSequence sequence)     at Microsoft.SharePoint.Upgrade.SPDatabaseSequence.get_SchemaVersion()     at Microsoft.SharePoint.Upgrade.SPSequence.get_IsBackwardsCompatible()     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.IsBackwardsCompatible(Object o, Boolean bRecurse)     at Microsoft.ShareP…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …oint.Administration.SPPersistedUpgradableObject.get_IsBackwardsCompatible()     at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.ValidateBackwardsCompatibility()     at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)     at Microsoft.SharePoint.SPSite.GetSPRequest()     at Microsoft.SharePoint.SPSite.get_Request()     at Microsoft.SharePoint.SPSite.SetAllowUnsafeUpdates(Boolean allowUnsafeUpdates)     at Microsoft.Office.Excel.Server.MossHost.SharePointHelperMethods.GetSite(String fileLocation, IClaimsIdentity claimsIdentity)     at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity)     at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity)     at …    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2()     at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action)     at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method)     at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo)     at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext)     at Microsoft.Office.Ex…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …cel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLocation trustedLocationSettings, Guid requestSiteId, IExcelServerDocumentContext documentContext)     at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Boolean newWorkbook, Boolean useCollection, Boolean loadedOnDemand)     at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)     at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution()     at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationA…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …sync()     at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareComplete(PrepareAsyncArgs args)     at Microsoft.Office.Excel.Server.AsyncArgsWithCallback`1.OneTimeCallback(T args)     at Microsoft.Office.Excel.Server.CalculationServer.Session.FinalizePrepare(PrepareAsyncArgs args)     at Microsoft.Office.Excel.Server.CalculationServer.Session.HandleTrimmedWorkbookReloading(UserOperation userOperation, PrepareAsyncArgs args)     at Microsoft.Office.Excel.Server.CalculationServer.Session.PrepareAsync(UserOperation userOperation, AsyncHandler`1 callback, Object userState)     at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.ExecuteContainedOperationAsync(Object userState)     at Microsoft.Office.Excel.Server.CalculationServer.Operation…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …s.OperationQueue.QueueOperationAsync(AsyncHandler`1 callback, Object userState, Operation[] operations, QueueOperationFlags flags, Int32 departureTimeout)     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.BeginOpenWorkbook(CommandParameter parameter, OpenWorkbookInParameters inParameters, AsyncCallback callback, Object state)     at AsyncInvokeBeginBeginOpenWorkbook(Object , Object[] , AsyncCallback , Object )     at System.ServiceModel.Dispatcher.AsyncMethodInvoker.InvokeBegin(Object instance, Object[] inputs, A…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …syncCallback callback, Object state)     at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceHostFactory.OperationInvokerExceptionWrapper.InvokeBegin(Object instance, Object[] inputs, AsyncCallback callback, Object state)     at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)     at System.ServiceModel.Dispatcher.ChannelHandler.DispatchAndReleasePump(RequestContext request, Boolean cleanThread, OperationContext currentOperationContext)     at System.ServiceModel.Dispa…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …tcher.ChannelHandler.HandleRequest(RequestContext request, OperationContext currentOperationContext)     at System.ServiceModel.Dispatcher.ChannelHandler.AsyncMessagePump(IAsyncResult result)     at System.ServiceModel.Diagnostics.Utility.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)     at System.ServiceModel.AsyncResult.Complete(Boolean completedSynchronously)     at System.ServiceModel.Channels.SecurityChannelListener`1.ReceiveItemAndVerifySecurityAsyncResult`2.InnerTryReceiveCompletedCallback(IAsyncResult result)     at System.ServiceModel.Diagnostics.Utility.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)     at System.ServiceModel.AsyncResult.Complete(Boolean completedSynchronously)     at System.ServiceModel.Channels.InputQueue`1.AsyncQueueReader.Set(Item item)    …    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        … at System.ServiceModel.Channels.InputQueue`1.EnqueueAndDispatch(Item item, Boolean canDispatchOnThisThread)     at System.ServiceModel.Channels.InputQueue`1.EnqueueAndDispatch(T item, ItemDequeuedCallback dequeuedCallback, Boolean canDispatchOnThisThread)     at System.ServiceModel.Channels.InputQueueChannel`1.EnqueueAndDispatch(TDisposable item, ItemDequeuedCallback dequeuedCallback, Boolean canDispatchOnThisThread)     at System.ServiceModel.Channels.SingletonChannelAcceptor`3.Enqueue(QueueItemType item, ItemDequeuedCallback dequeuedCallback, Boolean canDispatchOnThisThread)     at System.ServiceModel.Channels.SingletonChannelAcceptor`3.Enqueue(QueueItemType item, ItemDequeuedCallback dequeuedCallback)     at System.ServiceModel.Channels.HttpChannelListener.HttpContextReceived(HttpReque…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …stContext context, ItemDequeuedCallback callback)     at System.ServiceModel.Activation.HostedHttpTransportManager.HttpContextReceived(HostedHttpRequestAsyncResult result)     at System.ServiceModel.Activation.HostedHttpRequestAsyncResult.BeginRequest()     at System.ServiceModel.Activation.HostedHttpRequestAsyncResult.OnBeginRequest(Object state)     at System.ServiceModel.PartialTrustHelpers.PartialTrustInvoke(ContextCallback callback, Object state)     at System.ServiceModel.Activation.HostedHttpRequestAsyncResult.OnBeginRequestWithFlow(Object state)     at System.ServiceModel.Channels.IOThreadScheduler.CriticalHelper.WorkItem.Invoke2()     at System.ServiceModel.Channels.IOThreadScheduler.CriticalHelper.WorkItem.Invoke()     at System.ServiceModel.Channels.IOThreadScheduler.CriticalHel…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880k    High        …per.ProcessCallbacks()     at System.ServiceModel.Channels.IOThreadScheduler.CriticalHelper.CompletionCallback(Object state)     at System.ServiceModel.Channels.IOThreadScheduler.CriticalHelper.ScheduledOverlapped.IOCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)     at System.ServiceModel.Diagnostics.Utility.IOCompletionThunk.UnhandledExceptionFrame(UInt32 error, UInt32 bytesRead, NativeOverlapped* nativeOverlapped)     at System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)      61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880j    High        SqlError: ‘Cannot open database "WSS_Content_SPRX2010" requested by the login. The login failed.’    Source: ‘.Net SqlClient Data Provider’ Number: 4060 State: 1 Class: 11 Procedure: ” LineNumber: 65536 Server: ‘rxdb’    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          880j    High        SqlError: ‘Login failed for user ‘RX\spservice’.’    Source: ‘.Net SqlClient Data Provider’ Number: 18456 State: 1 Class: 14 Procedure: ” LineNumber: 65536 Server: ‘rxdb’    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          3760    Critical    SQL Database ‘WSS_Content_SPRX2010’ on SQL Server instance ‘rxdb’ not found. Additional error information from SQL Server is included below. Cannot open database "WSS_Content_SPRX2010" requested by the login. The login failed. Login failed for user ‘RX\spservice’.    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Database                          tzku    High        ConnectionString: ‘Data Source=rxdb;Initial Catalog=WSS_Content_SPRX2010;Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15’    ConnectionState: Closed ConnectionTimeout: 15    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Upgrade                           fbv7    Unexpected    [w3wp] [SPUpgradeSession] [ERROR] [7/22/2012 5:02:53 PM]: IsBackwardsCompatible [SPContentDatabase Name=WSS_Content_SPRX2010] failed.    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Upgrade                           fbv7    Unexpected    [w3wp] [SPUpgradeSession] [ERROR] [7/22/2012 5:02:53 PM]: Exception: Cannot open database "WSS_Content_SPRX2010" requested by the login. The login failed. Login failed for user ‘RX\spservice’.    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57     w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Upgrade                           fbv7    Unexpected    [w3wp] [SPUpgradeSession] [ERROR] [7/22/2012 5:02:53 PM]:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)     at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)     at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(Strin…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Upgrade                           fbv7    Unexpected    …g host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)     at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionFactory…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Upgrade                           fbv7    Unexpected    ….CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     at System.Data.SqlClient.SqlConnection.Open()     at Microsoft.SharePoint.Utilities.SqlSession.OpenConnection()     at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlComman…    61486a15-6606-4f31-b9f1-385c24e303d6
07/22/2012 17:02:53.57*    w3wp.exe (0x0E74)                           0x09C8    SharePoint Foundation             Upgrade                           fbv7    Unexpected    …d command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)     at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, Boolean retryForDeadLock)     at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command)     at Microsoft.SharePoint.Upgrade.SPDatabaseSequence.GetVersion(SPDatabase database, Guid id, Version defaultVersion, SqlSession session, SPDatabaseSequence sequence)     at Microsoft.SharePoint.Upgrade.SPDatabaseSequence.get_SchemaVersion()     at Microsoft.SharePoint.Upgrade.SPSequence.get_IsBackwardsCompatible()     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.IsBackwardsCompatible(Object o, Boolean bRecurse)    61486a15-6606-4f31-b9f1-385c24e303d6

Continue reading here…

Turns out the spservice account was missing on the content database. I’m not sure why this occurred and whether is was related to the SP1 installation (I could’ve sworn it worked before).

Here are some blogs that pointed me in the right direction.

http://stepbistep.net/2012/06/29/the-workbook-cannot-be-opened-excel-services-error-on-sharepoint-2010/

http://blogs.msdn.com/b/jjameson/archive/2010/05/04/the-workbook-cannot-be-opened-error-with-sharepoint-server-2010-and-tfs-2010.aspx

Basically, you can use PowerShell on your Web App on which Excel Services is not working.

$webApp = Get-SPWebApplication “http://YourWebApp”

$webApp.GrantAccessToProcessIdentity(“DOMAIN\ServiceAccount”)

After doing this I noticed by spservice account had been granted access in SQL to the database, as shown in the screenshot.

Jan

Adding multiple graphs (stacked bar and line) to an excel chart

By Russell Wright 6 Comments

Categories: Excel Services

I don’t normally blog on Excel, but since I was working with Excel Services and asked to create some KPIs to display using Excel Services, I thought it had some relationship to SharePoint. So, let me be the first to say I’m not an Excel expert, but I can usually get it to do what I want it to do…after spending the time.

I really like the business part of SharePoint. I like “SharePoint-tizing” business processes and figuring out the business side of things. Let’s face it…SharePoint is a tool just like Excel, Word, PowerPoint, SAP, PeopleSoft, Oracle…and it’s really about the skill you apply to using the tools to provide the maximum business benefit. While “I love technology” (one of my favorite songs from Napoleon Dynamite), I much prefer to figure out how to apply it in real business scenarios.

Publishing KPIs is a great way to get people on the same track and thinking about the same important goals in your business. With Excel Services you can easily do this and, if you have a good back-end data source, you can connect your workbook to the live data. In order to get started and prove out your KPIs, you may want to simply publish some items out of an Excel workbook that someone is manually updating on a regular basis. That’s where we’re coming from in this example.

I have some data (employee headcount) and I want to create a combination graph that shows the prior year’s (2009) ending headcount by group and continue with showing the monthly headcount for each of the groups for the new year (2010). This is what I was shooting for (and finally obtained).

The stacked column is showing the ending headcount from 2009 and the lines are showing the monthly headcount for 2010. Here’s my original data.

Creating the original line graphs is easy. Insert a chart and select the line chart and select the entire table of data and Excel will perform its magic.

That was easy. Total time, 60 seconds. Now we need to use the trick of adding another series to the chart and formatting it as a different graph (chart) type. But first, we need to add a column to hold our data…or create another small table with our year-end stats.

Select the data and press Ctrl-C to copy it to the clipboard.
Click on the graph to select it (very important, so the paste option has the correct context).
Use the “Paste Special…” clipboard feature to paste the new series into the existing graph.

Use the default settings: New series, Columns and click OK.

Notice that the series is added as another line.

Now you can change this to a column graph.

Right-click on the series and select “Change Series Chart Type…”

Select the stacked column chart type and click OK.

Notice you end up with a chart that doesn’t look like what we want it to look like. There is no space in the X axis for the “YE 2009” label and the data from the year end totals is not stacked. WRONG ANSWER! Start over.

Right-click on the series you added and click Delete.

What we need to do is add each group’s year end total as a separate series using the same technique as we did previously.

Select the data for the first group and copy it to the clipboard.
Select the chart by clicking on it.
Select “Paste Special…”

Click OK using the defaults from the Paste Special action.

You’ll notice very little change in the chart. What you should notice, however, is the addition of another “Group A” label in the legend. The reason you don’t see anything on the graph is that the graph is simply a single point and it is being covered up by the other lines.

To select the new “point series” you can click around on the chart where you know it should be located, or you can use the Current Selection tools that are located on the far left of the Chart Tools | Layout and Chart Tools | Format tool ribbons.

You should be able to identify the new “Group A” series and select it from the drop-down.

When you select it from the drop-down, the series will be selected on the chart.

Now you can change the chart type by using “Change Chart Type” in the Chart Tools | Design tool ribbon.

Click on “Change Chart Type” or carefully right-click on the selected series (takes a steady hand).

Select the stacked column chart and click OK.

You should see the first part of your stacked column graph appear.

Now let’s add the next series. It’s basically repeating the above process.

Select the data and copy to the clipboard.
Select the chart.
Select “Paste Special.”
Click OK to add the series.
Select the “point series” using the drop-down in the Current Selection area of the Chart Tools | Layout ribbon.
Change the chart type by using “Change Chart Type” in the Chart Tools | Design tool ribbon.

You should see another bar get “stacked.”

Repeat the process for the final series. Now that the stacked column graph as been added to the chart, it’s time to do a little clean up.

First, let’s make a space for the “YE 2009” label on the X axis. We’ll do this by adding a new column to our original data and leaving the data blank.

Next, we’ll expand our selection for the original data.

Right-click on the graph and click Select Data…

Click the Edit button to edit the horizontal axis labels.

Notice that the range that is selected only includes the original 12 months.

Re-select the range to include “YE 2009” and click OK.

Notice that the “YE 2009” label is added to the chart.

Select one of the original series and click the Edit button.

Select the series values to include the blank “YE 2009” column and click OK.
Repeat this process for each series (Group A, Group B, Group C and Total).
Click OK to close the dialog.

Your graph should resemble this.

You can delete the redundant legend entries, if you like, by clicking on the legend entry until a single one is selected and pressing the delete key.

Now, if you want, you can select each of the series and format them manually to change their colors to be consistent between the line and column graphs, if they are not already.

The final product.

That was rather long-winded, but I hope it has helped you as much as it has helped me to document what I did, because it might be months or years before I do it again!

Search

Asif Rehmani’s SharePoint Videos

Click to access a wealth of SharePoint videos

Are you a user?

Archive for the 'Excel Services' Category

The Workbook Cannot be Opened–Excel Services SharePoint 2010

Adding multiple graphs (stacked bar and line) to an excel chart

Search

Asif Rehmani’s SharePoint Videos

SharePoint Rx

Recent Posts

Categories

Just Sharing

Posts by Date