In many scenarios, we SharePoint developers want to take two different lists and join them and present a single set of data. For instance, one list contains customer information and the other list contains products with serial numbers and customer-related specific details like connection information and versions. There is a lookup field in the product list that selects the customer the product belongs to. So far so good.
Now, some silly manager would like to see a single report of all customers, including company name, city and state, and all associated products with sale date, version and serial number. Why would anyone want to see this information? I don't know but some managers actually want to pull meaningful reports from SharePoint.
How does a SharePoint guy get this information to that manager? Well, if you're familiar with SQL server, you're probably thinking simply grab the two tables and join the two and you're off and running. Unfortunately this is not directly supported by Microsoft. Here's what I've done in the past and I hope this does help someone, at some point.
1. First option is to work with the SQL table directly. This is an unsupported method by Microsoft. They want you to use their SharePoint API (and I discuss an option using this) to access any and all data in SharePoint. I highly recommend staying out of SharePoint's SQL. It is trecherous and full of peril. One wrong update and your entire SharePoint site will be crippled.
So, for the adventerous few, I'll continue. In the SharePoint content database there is a table named dbo.AllUserData. This table contains all data for all lists. Documents are stored in dbo.AllDocs. The List information is located in dbo.AllLists. Let's open up AllUserData. Real quick explanation of some of the fields, most are self-explanatory
tp_ID is the ID of the list item in the list. That's the SPListItem.ID value.
tp_ListId is the GUID of the List the item belongs to. Related to AllLists table.
nvarchar1,nvarchar2, ntext9, ntext10,int4, int5 and so on. These are the fields that are the columns you make in SharePoint. In most cases nvarchar1 is the Title column.
The actual definition of what columns match to what fields is located in the AllLists table, in field tp_Fields. A small sampling of the definition looks like:
<FieldRef Name="Title" ColName="nvarchar1"/>
<FieldRef Name="Location" ColName="nvarchar3"/>
<FieldRef Name="EventDate" ColName="datetime1"/>
<FieldRef Name="EndDate" ColName="datetime2"/>
<FieldRef Name="Description" ColName="ntext2"/>
As you can see, that defines nvarchar1 is the Title column, EndDate is datetime2 in table AllUserData.
If you want to pull reports directly from SQL, you need to handle this field definition and you'll need to know what columns are what fields. If you're good enough you can write a function to parse the XML so you can easily find out what SQL field is what SharePoint column. Then with all of this information, create your views and procs with your joins. Fair warning though: if your end users change columns, you may have to rewrite your views to support the new columns.
2. Using the SharePoint API. This is a rather clunky way of doing what should be a simple task, but it works for the most part. Basically, what you need to do is take your first customers then loop through the SPListItems in the SPList.Items object. As you loop through, for each SPListItem, take the SPListItem.ID and then search against the Products table customer field. This new list will return all products assigned to this customer. Now take the SPListItemCollection returned from the products table and loop through each SPListItem, and store the data you want into a DataSet (for easier management). After you're done looping, databind the dataset to a gridview or datalist. Sounds like a pain, eh? Here's a sampling of what I've done. It works, but it's more code than I believe there should be:
using System;
using System.Collections;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.Utilities;
public class CustomerProductSample
{
public static DataSet GetList()
{
try
{
DataSet dsResults = CreateDataset(); //i wont go into how to create a
dataset, pretty straight forward
//use this dataset to store the final results
using (SPSite siteFull = new SPSite(SPContext.Current.Site.ID))
{
SPWeb web = siteFull.OpenWeb(SPContext.Current.Web.ID);
SPList listCustomers = web.GetList(web.Url + "/Lists/Customers");
SPList listProducts = web.GetList(web.Url + "/lists/Products");
foreach (SPListItem liCustomer in listCustomers.Items)
{ //loop through all customers, if you wanted a certain subset of
customers, use a SPQuery
//now get the customer ID and search against the Product table
int customerID = liCustomer.ID;
SPQuery qryProducts = new SPQuery();
qryProducts.Query = "<Where><Eq><FieldRef Name=\"Customer\"
LookupId=\"True\" /><Value Type=\"Lookup\">" + customerID + "</Value><Eq></Where>";
SPListItemCollection colProducts = listProducts.GetItems
(qryProducts);
//the above will return all products where the lookup value is
this customer
if (colProducts.Count > 0)
{ //important to handle customers with no products
foreach (SPListItem liProduct in colProducts)
{
DataRow newResult = dsResults.Tables[0].NewRow();
//create a new dataset row and load it up
newResult["Customer"] = liCustomer["Name"].ToString();
newResult["City"] = liCustomer["City"].ToString();
newResult["ProdName"] = liProduct["Name"].ToString();
newResult["DateSold"] = liProduct["Sale Date"].ToString();
newResult["Serial"] = liProduct["Serial Number"].ToString();
dsResults.Tables[0].Rows.Add(newResult);
}
}
}
}
return dsResults;
}
catch { }
}
}
3. Use Microsoft Access. If your resulting data set doesn't have to be in SharePoint, another option is to use Access 2003 or newer (2007 has better functionality with SharePoint). You can import the lists and based on Lookups create views with multiple columns from multiple lists. This is a method we have used for the sake of just pulling reports.
4. Third Party products. There are a few third party products out there that will create these views for you. If you find one that works, let me know and I'll post it here! Simply comment to this post. Thanks!
Fair warning though: if your end users change columns, you may have to rewrite your views to support the new columns.
Using the SharePoint API will work, but it's a lot of code and can cause a performance hit on the database server depending on how much data is being processed. I have a similar report for another scenario, with about 75 customers and potentially 400-600 products and it takes SharePoint about 10-13 seconds to return the dataset. Not terrible, but SQL can be faster.
I hope this helps someone. This can be a real annoying task in SharePoint. If you require further assistance, feel free to contact me at dlozzi@delphi-ts.com.
If you know a better way to accomplish this nonsense, please share!!