Database-Driven Excel Charting in SharePoint (Part Deux!) by Gus Collazo

Welcome back to part 2 of my blog on Database-Driven Excel Charting in SharePoint. If you followed the steps from the first part of this blog, you should have an Excel 2007 workbook with a pivot table calling a stored procedure in SQL Server. In this blog, we'll publish this workbook to Excel and configure SharePoint to allow the workbook to continue querying the database.

First, we'll take the pivot table we created thus far and create a chart over it. Select the data within the pivot table.

Click the Insert tab in the Office ribbon and click the 2-D Line charting option.

Close the PivotChart Filter Pane that appears and move the chart so it covers the pivot table. That's it for now in Excel. Now let's turn our attention to SharePoint.

Next we'll need a database connection library. If we don't already have one, we can create one within SharePoint. You do this by connecting to your SharePoint site via your browser and clicking the View All Site Content link on the left. Click the Create button at the top of the subsequent page and then select Data Connection Library under the Libraries section.

Provide a name for the new data connection library and click the Create button. SharePoint will take you to the new library. Copy the hyperlink from the address bar and strip off the "/Forms/AllItems.aspx" portion at the end. The resulting URL will be used in the following steps.

Our Excel file will use this data connection library to house the connection of the pivot table for SharePoint to use to refresh the chart. Before we can use the data connection library though, we must add it to the trusted data connection libraries of Excel Services. To do this, launch SharePoint 3.0 Central Administration. Click on your Shared Services Provider link on the left, then select Trusted data connection libraries in the Excel Services Settings section on the right.

On the subsequent page, click the Add Trusted Data Connection Library button at the top. In the Address field, paste the hyperlink that you copied earlier without the "/Forms/AllItems.aspx" portion at the end. Click OK.

Now we can return to the Excel workbook. Click on the Data tab in the Ribbon. Click on the Connections button.

Select the data connection and click the Properties button to the right of it. Once the Properties window opens, set how often you wish the data to refresh.

Next, click on the Definition tab in the Properties window. Check the Always use connection file and Save password checkboxes. Click the Export Connection File button and paste the URL you copied earlier for the data connection library into the File Name textbox. Click Save and the folder will change to reflect the contents of the library. Click Save once more to save the connection to the library.

Enter any descriptive text you'd like for this connection and then click OK. Finally, click OK on the Connections Properties window.

Close the Workbook Connections window and save your workbook.

At this point if you were to alter the GetMonthlySalesForLast12Months stored procedure you would notice something interesting. Let's say you alter the procedure by changing this line...

... to this ...

If you still have both your Excel 2007 workbook open and the web page open displaying the chart, you'll notice that the Excel workbook will refresh within 1 minute while the SharePoint page will not. This is because a setting in SharePoint is overriding your file-level setting to refresh every minute.

You need to change the SharePoint setting as follows.

In the SharePoint 3.0 Central Administration site, click on your Shared Services Provider link on the left again, then select Trusted file locations in the Excel Services Settings section on the right. Click the hyperlink of the folder where you deployed the workbook. Scroll down to the External Data section and update 4 settings. Uncheck the Warn on Refresh and Stop When Refresh on Open Fails checkboxes. Unchecking the second checkbox will allow the spreadsheet to still open within dashboards and use cached data if the database is unavailable for any reason. Finally, update the numeric values for Automatic refresh and Manual refresh. A value like 60 should suffice. It's these last 2 settings that caused the tricky refresh issue. By providing values for the folder in SharePoint that are consistent with the values you set for your workbooks, you can avoid this seemingly strange behavior!

That's about it. You should now see your Excel workbook with database-driven chart in the browser view within SharePoint.

You can test out the database-driven chart by altering the GetMonthlySalesForLast12Months stored procedure to return different numbers. You should notice the chart within a minute or two refresh itself to display the new data in the chart!

There's much more that can be done. You can clean up the workbook by removing gridlines and tweaking the display of the chart. You can create named ranges in the workbook and publish only the named range containing the chart. You can create a dashboard where a web part points to this named range and thus your dashboard can have database-driven charts from several Excel workbooks. This is all extra-credit though and I leave it to my trusted reader to venture forth and try for him or herself. Of course, if anyone is interested, just let me know and I'll add a third part to this blog entry and take this sample further.

In March I'm flying out to Seattle to get a 3-day hands-on demo from Microsoft on SQL Server 2008! Watch my blog in March for all the goodies I learn about SQL Server 2008. Until then, thanks for reading!

--Gus