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

Hello everyone. I'm Gus Collazo, a member of the speakTECH Data Services team. Welcome to my first blog entry for speakTECH. In the future I'll focus my blog entries primarily on SQL Server 2008, PerformancePoint Server, and SharePoint. For my first entry though, since I'm working on a project focused on Excel Services, I'll focus on creating database-driven Excel charts for SharePoint.

This is part 1 of a 2-part blog where I'll demonstrate how to use Excel Services in SharePoint to create a dashboard with a database-driven chart. We first need to create an Excel 2007 workbook that retrieves data from our database.

First, run Management Studio and connect to the AdventureWorks database (on SQL Server 2005). Use the following script to create a stored procedure that retrieves the total sales amounts for the company on a monthly basis for the last 12 months of available data.

CREATE PROCEDURE dbo.GetMonthlySalesForLast12Months
AS
DECLARE @MaxDate DATETIME
DECLARE @MaxMonthYear DATETIME
DECLARE @MinMonthYear DATETIME
DECLARE @MonthlySales TABLE (MonthYear DATETIME, SalesAmount NUMERIC(38, 6))

SELECT @MaxDate = MAX(ModifiedDate) FROM Sales.SalesOrderDetail
SELECT @MaxMonthYear = DATEADD(SECOND, -1, CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(@MaxDate)) + '/01/' + CONVERT(VARCHAR, YEAR(@MaxDate))))
SELECT @MinMonthYear = DATEADD(MONTH, -11, @MaxMonthYear)

INSERT INTO @MonthlySales (MonthYear, SalesAmount)

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(ModifiedDate)) + '/01/' + CONVERT(VARCHAR, YEAR(ModifiedDate))), SUM(LineTotal)
FROM Sales.SalesOrderDetail
WHERE ModifiedDate BETWEEN @MinMonthYear AND @MaxMonthYear
GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(ModifiedDate)) + '/01/' + CONVERT(VARCHAR, YEAR(ModifiedDate)))

SELECT CONVERT(VARCHAR, YEAR(MonthYear)) + CASE WHEN MONTH(MonthYear) < 10 THEN ' 0' ELSE ' ' END + CONVERT(VARCHAR, MONTH(MonthYear)) AS MonthYearStr, SalesAmount
FROM @MonthlySales
ORDER BY MonthYear ASC

Next, we need to create a Microsoft Query file that calls our stored procedure. Run the program "msqry32.exe". You'll have to search for this file on your computer, but can usually find it in the "C:\Program Files\Microsoft Office\Office12" folder. Click the "New Query" button to the far left on the toolbar. With "<New Data Sources>" selected, click the OK button. Give the new data source a name such as dsSales. Select "SQL Server" for your database driver. Next, click the "Connect..." button and connect to your SQL Server. Make sure you click the "Options >>" button and select the AdventureWorks database. Once back on the "Create New Data Source" dialogbox, check the "Save my user ID and password in the data source definition" checkbox and click the OK button. There is no need to set the default table.

Once back on the "Choose Data Source" dialogbox, click the OK button with the "Use the Query Wizard to create/edit queries" checkbox checked. We'll be entering our own SQL code, so we just need to move through the wizard as quickly as possible. Select any table and click the ">" button, then the "Next" button. Click "Next" again and again. Check the "View data or edit query in Microsoft Query" option and then click the Finish button. Now click the SQL button on the toolbar of Microsoft Query. Replace the current SQL statement with this text:

exec AdventureWorks.dbo.GetMonthlySalesForLast12Months

Click the OK button and when prompted to continue anyway, click OK again. You should see Microsoft Query display the sales amounts for the last 12 months of available data.



Click the Save button on the toolbar and save the query to a .DQY file. Finally, exit the Microsoft Query application.



We'll now create a workbook in Excel 2007 to use this query file. Run Excel 2007 and then click the "Insert" tab of the ribbon. Click the "PivotTable" button on the far left. Select the "Use an external data source" option and click the "Choose Connection" button.



Although the "Existing Connections" dialogbox displays what seems to be your connection, it's actually not the DQY file you saved. Ignore the listing and click on the "Browse for More" button on the bottom left.



Find your .DQY file and open it. Back on the "Create PivotTable" dialogbox, click the OK button.

Once the pivot table is added to the workbook, the right side will display the PivotTable Field List. Drag the "MonthYear" field from the field list down to the Row Labels section on the right. Next, drag the SalesAmount field down to the Values section.



That's it for Part 1 of this blog entry. At this point, you should have a pivot table with total monthly sales for the last 12 months of available data. Next time, we'll look at how to publish a chart of this data into a SharePoint dashboard for all the world to see!