Consuming Business Data Catalog Entities in Excel Services
One of the coolest (only?) SharePoint external data features is the Business Data Catalog (BDC).
The BDC is brand new with SharePoint 2007, and is not yet fully integrated into Excel and Excel Services... though, if you've read one or two other entries in this blog you've probably figured out where I'm going with this :o)
Here's a pretty simple UDF that will take a BDC instance name, entity name, and will return the data associated with it.
Without further delay... just call the UDF below in an array formula in your workbook as usual (e.g. "{=getBDCData("SharedServices1", "AdventureWorksDWInstance", "Product")}")
PreReq: I used the Adventure Works DW BDC sample from https://msdn2.microsoft.com/en-us/library/ms494876.aspx ... you'll need to set up the BDC yourself as well to use this UDF.
PS: Sorry about the horrible formatting... It's a pain refactoring to get the code to look half-way decent on this blog.
/// <summary>
/// Gets data from the entity of the specified BDC instance.
/// </summary>
/// <param name="SSPName">Shared Services Provider Name</param>
/// <param name="BDCInstanceName">BDC Instance Name</param>
/// <param name="entityName">Entity Name</param>
/// <returns>Results array</returns>
[UdfMethod(IsVolatile = true,
ReturnsPersonalInformation = true)]
public object[,] getBDCData( string SSPName,
string BDCInstanceName,
string entityName)
{
object[,] results = null;
SPSite site = null;
try
{
// Impersonate the current user viewing EWR.
// (See previous Blog post on user impersonation)
using (WindowsImpersonationContext wiContext =
impersonateUser())
{
site = new SPSite(serverName);
try
{
// Need to set this so we can grab instance
SqlSessionProvider.
Instance().
SetThreadLocalSharedResourceProviderToUse(
Microsoft.Office.Server.ServerContext.
GetContext(site));
}
catch (System.Exception ex)
{
// Ignore the exception if a provider
// is already set.
}
// Get the sample Adventure Works instance
// For XML of this instance see:
// https://msdn2.microsoft.com/en-us/
// library/ms494876.aspx
LobSystemInstance advWorks =
ApplicationRegistry.
GetLobSystemInstanceByName(
BDCInstanceName);
// Get the entities
NamedEntityDictionary entities =
advWorks.GetEntities();
// Get requested entity
Entity prodEntity = entities[entityName];
// Get entity Filters
FilterCollection fc =
prodEntity.GetFinderFilters();
// Enumerate results
IEntityInstanceEnumerator
prodEntityInstEnumerator =
prodEntity.
FindFiltered(fc, advWorks);
// Convert it to a DataTable
EntityDataTableAdapter dataAdapter =
new EntityDataTableAdapter(
prodEntityInstEnumerator);
System.Data.DataTable resultTable =
dataAdapter.EntitiesAsDataTable;
// Define dimensions of results array
results = new object[ resultTable.Rows.Count,
resultTable.Columns.Count];
// Get the data from the DataTable into object[,]
int i = 0;
foreach (System.Data.DataRow currRow in
resultTable.Rows)
{
int j = 0;
foreach (object currField in
currRow.ItemArray)
{
results[i, j] = currField.ToString();
j++;
}
i++;
}
}
}
catch (System.Exception ex)
{
results = new object[1, 1];
results[0, 0] = ex.Message;
}
finally
{
if (site != null)
site.Dispose();
SqlSessionProvider.
Instance().
ResetThreadLocalSharedResourceProviderToUse();
}
return results;
}