Create an Excel's User Defined Function (UDF) to display web service data

Scenario

You have built a Project Server Interface (PSI) extension to query the Reporting database and you would like to display the data in your favorite reporting tool: Excel.

The solution is to create an Excel Services User Defined Function that will call the custom PSI Extension created and return an array of data.

Code

Before you start check out this code sample on how to do it: http://msdn2.microsoft.com/en-us/library/bb428649.aspx, as well as this blog from Shahar Prish: http://blogs.msdn.com/cumgranosalis/ .

In Visual Studio 2008 create a C# class for .NET 2.0, add a web reference to the web service (PSI Extension created) and start coding!

Note the UDF specific class attributes:

    [UdfClass]
    public class RetrievePSData
    {
        wsReporting.Reporting reporting = new wsReporting.Reporting();

        [UdfMethod]
        public string[,] Reporting(string method)
        {
            // Initialize variables
            DataSet ds = null;
            string msg = string.Empty;
            reporting.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // Retrieve web service data based on method
            try
            {
                switch (method.ToUpper())
                {
                    case "GETACTUALWORK":
                        ds = reporting.GetActualWork();
                        break;

and now construct the array by looping over the web service datatable:

            // Populate result object
            string[,] result = new string[dt.Rows.Count+1, dt.Columns.Count];

            // Add header column name first
            int i = 0;
            foreach (DataColumn column in dt.Columns)
            {
                result[0, i] = column.ColumnName;
                i++;
            }

            // Build array of data
            int r = 1;
            foreach(DataRow row in dt.Rows)
            {
                
                 for (int c = 0; c < dt.Columns.Count; c++)
                {
                    result[r, c] = row[c].ToString();
                }
                r++;
            }

Full code sample is attached at the bottom of this blog post.

To debug the code attach w3wp.exe process after it's deployed.

Deployment

In your Office Server farm, go to the Shared Service Provider and select: Excel Services -> User-Defined Functions ->

Add User-Defined Function Assembly (file path of C:\ODC\ExcelPSUDF\bin\Debug\Microsoft.EPM.ExcelPSUDF.dll for instance)
Add a Trusted File Location (http://w2k3:82/Excel%20Services/ for instance), and ensure you check Allow User-Defined Functions

Testing

Create an Excel spreadsheet in the trusted file location defined earlier
Enter the UDF as an array function:
- Choose the range in the spreadsheet you think it will return and type the formula (without clicking the mouse - the range needs to be selected)
- Ctrl-Shift-Enter the formula
- Spreadsheet should look like this before rending it via Excel Services (note {=Reporting("GetTimePeriods")} ):