Data Presentation Techniques for SharePoint Lists

RELATED: "Manage Your SharePoint Content" and "SharePoint 2010 Development Tools for Visual Studio 2010."

Windows SharePoint Services (WSS) 3.0 and Microsoft Office SharePoint Server 2007 provide a plethora of features that are used in varying degrees from one implementation of Microsoft SharePoint technologies to the next. One feature pervasive across every implementation is the SharePoint list. The list infrastructure is necessarily robust in SharePoint and, out of the box, meets most user requirements for create, read, update, and delete (CRUD) operations. In addition, views, alerts, and syndication provide the majority of list-presentation user requirements. Where list presentation and transformation fall short, Microsoft and a number of ISVs have stepped in to fill the gaps. In this article, we'll explore different methods for retrieving and displaying SharePoint list data, including techniques that use the SharePoint integration services layer and other Microsoft products for creating reports and transforming, joining, and rolling up list data from multiple SharePoint lists. We'll also mention a few third-party solutions for list presentation.

Out-of-the-Box Approaches

You can accomplish a lot in SharePoint by creating views from your SharePoint lists. For example, you can filter columns, group data, perform basic field calculations, and perform field-lookup operations across multiple lists in the same SharePoint web. This capability is well documented and covers a subset of the powerful and simple-to-configure data presentation features in SharePoint.

Even with this built-in list-presentation capability, often you'll reach a point where you need to take other approaches for presenting SharePoint list data. For example, you'll need to find a way to read data from multiple lists in a report format or perform conditional formatting of list data (i.e., change a field to red when a predefined date has passed).

To demonstrate list-presentation techniques in a consistent way, we focus primarily on inventory lists in the Inventory Tracking template. This template is part of Microsoft's Fantastic 40 SharePoint application templates, which you can investigate and download at the WSS 3.0 application templates page.

SharePoint Designer Approaches

The Data View control provides an easy way to sort, filter, group, and format multiple SharePoint lists. SharePoint Designer (SPD), which is now available for free at office.microsoft.com/en-us/sharepointdesigner/default.aspx, provides WYSIWYG access to the Data View. Using the Data View, you can display SharePoint lists that contain the same data or related data, from the same site or different sites.

To display data from multiple lists in the Data View, the first task is to link the data sources. Depending on the data you want to display, you'll either merge or join the lists. You'll opt to merge lists containing the same data, whereas you'll want to join lists containing related data. For example, if you want to display a list of suppliers and the products they provide, you join the lists, using the supplier ID as the key field. Figure 1 shows an example of joined lists. If you're building a report displaying inventory lists from multiple SharePoint sites, you can merge or roll up the lists in SPD. The list rollup then enables you to sort, group, and filter the combined data, as Figure 2 shows.

Figure 1: SPD joined list

Figure 2: SPD merged list

Once the data sources are linked, you'll use the linked data as the source for your Data View. For merged lists, simply select the data/columns you want displayed and add them to the Data View. Joined lists take a little more setup to configure. The first step is to add the data/columns you want to display as the parent grouping. For example, when building the supplier/products report, you can show the supplier name and primary contact from the supplier list and the products each supplier provides from the products list. The two lists are joined on a common field, supplier or supplier ID, in this case.

The Data View includes many options to sort, group, filter, and conditionally format report data. SPD also allows for simple report-page formatting. The relatively simple interface was designed to enable information workers and power users to build self-service solutions, thereby freeing development staff for other work.

SSRS Data Extensions

For simple SharePoint list-reporting needs, the XML data provider SQL Server Reporting Services (SSRS) extension will fit the bill. This component can call web service methods to retrieve data. Therefore, you can call web services through the SharePoint integration services layer. The most common web service to call for this purpose is lists.asmx and specifically the GetListItems method.

You can learn about how to use this approach by reading the MSDN blog "Reporting Services and WSS Lists." In this blog, the author mutaz (blog alias) explains how to configure SSRS to use the GetListItems method. He also lists other SSRS data extensions that are worth investigating. For this article, we focus on the XML data source. Please be sure to review mutaz's example before continuing. The open source extension (developed by Teun Duynstee) and third-party extension (Enesys RS Data Extension) listed in the blog are viable options that you should consider for surfacing more capabilities than available in the XML data provider for example, being able to join lists or easily pull back versioned fields.

Figure 3 shows how you can configure the XML query for the data set to retrieve Inventory list data from a web. In this case, the data source points directly to the web containing the Inventory list, like this: http://local.dev/CompanyA/_vti_bin/lists.asmx. The SharePoint web, CompanyA, resides immediately below the site, local.dev.

Where mutaz stops with the XML data provider, Charles Emes picks up in his blog "Creating a SQL Server Reporting Services Report from a SharePoint List." In it, he focuses on how to improve the XML query that you pass to SharePoint. For example, you can use a GUID for the list name that you pass to GetListItems and include the GUID of a view containing the fields you want in your report. Using GUIDs ensures that list name changes won't break your reports. In addition, by explicitly including a GUID for a view, you ensure that the query retrieves the fields that you defined for the list. If you don't specify a view GUID, the lists.asmx web service returns the field metadata of the default view. Figure 4 shows the improvements to our example based on Charles' suggestions.

There are other options that you can specify in your query. Be sure to review the article, "Connecting SQL Reporting Services to a SharePoint List," by David Wise. David describes effective methods for troubleshooting SSRS SharePoint list reporting, and he provides some important details about using GUIDs for the list and view names. In summary, GUIDs are the best option, but they do make deployment from one environment to the next more difficult because each SharePoint instance will have unique GUIDs for each list and view.

Although the XML data provider doesn't support list rollups, you can create a report containing several web service calls (one for each list data set), then use the SSRS designer to lay out the report, as shown in Figure 5. The report retrieves the Title and Site URL values of each web by calling the GetWebCollection method of the SharePoint Webs Web Service (webs.asmx) and the Inventory list data by calling the GetListItems method, as shown in Figures 3 and 4. The data source of each list contains the URI to the parent web of each list.

Figure 5: SSRS combined list

There is, however, an alternative to creating a data source for each call to GetListItems, by using a parameter not shown in Figure 4: webID. By specifying the GUID of a web containing the list, you can use the same data source for multiple lists. For example, the URL to the data source could be this: http://local.dev/_vti_bin/lists.asmx. Notice that the specific web (CompanyA) no longer appears in the web service call. Then, each data set in your report could use this single data source as long as you include the GUID of the web containing the list. The XML element inside of each data set would look something like this:

\{GUID of the Web containing the list\}

Adding SSIS to the Mix

If building reports off of SharePoint lists becomes popular in your organization or in your consulting work, you're likely to encounter situations where SSRS configured with the XML data provider is not enough. In this case, the open source and third-party extensions mentioned previously are excellent options for real-time reporting. In addition, SPD might meet your needs (see the SPD Approaches section in this article). Another interesting approach is to add SQL Server Integration Services (SSIS) to your reporting solution.

SSRS and SSIS can be a powerful platform combination to report, transform, join, and roll up SharePoint list data. The key connection components that make this possible are the SSRS SQL Server data source component and the SharePoint data connection objects for SSIS, which you can download at http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=17652. The SharePoint data connection objects were built by the Microsoft SSIS team. Although all these connection components are available for SQL Server 2005, if at all possible, work with the SQL Server 2008 components. There are a number of improvements for SQL Server 2008 that make the upgrade worthwhile. For details about the improvements, download "SSIS & SSRS Improvements in SQL Server 2008."

In the combined SSIS and SSRS solution, you use SSIS to retrieve SharePoint list data and store it in one or more data tables. For example, you can write data from multiple lists to one table. Figure 6 shows an SSIS control flow that uses a SQL Server task to truncate a single table in a SQL Server 2008 database and then call two data flow components that bulk-load list data into a single table. Figure 7 shows one of the two data flows responsible for bulk-loading data from a source SharePoint list into a database table.

Figure 6: SSIS control flow

Figure 7: SSIS data flow

The SharePoint List Source component is the custom script component written by the SSIS team. With as few as two property settings (SiteListName and SiteUrl), this data source component can pull data from a SharePoint list. There is a lot more to this component, so be sure to read the information the SSIS team provides on the CodePlex site listed earlier in this section. The derived column object is important for massaging some of the column data to remove characters appended to choice list data. For example, the AssetType SharePoint field in the Inventory list is a lookup column. Figure 8 shows the AssetType choice column before and after being processed with the following code in the derived column object:

TRIM(SUBSTRING(AssetType,FINDSTRING(AssetType,";#",1) + 2,LEN(AssetType)))

After derived columns are processed, data conversion is often necessary to convert the data types into a form acceptable for loading into SQL Server. The SSIS data type conversion options vary depending on the data types you choose for your SQL Server column data. For example, if you select nvarchar(255) for the Title column in your data table, you can convert the Title column from the SharePoint list to an SSIS Unicode String \[DT_WSTR\] for a successful bulk insert operation. For information about mappings between SSIS and SQL Server data types, see Brian Knight's blog "SSIS Connection Manager Data Types Mapping to SQL Server Data Types."

The next step is to run the SSIS package and later schedule your package to run regularly with the SQL Server Agent. Running the package returns a single table that will look something like Figure 9. The last step is to create an SSRS report. The data source for this report is SQL Server, and with a minor amount of configuration, you can create a report list rollup like the one shown in Figure 10. List rollup is one of many options you have once the data has been loaded into SQL Server. For example, you can perform complex joins in SQL Server, then use SSRS to report on the combined data.

Figure 9: SQL Server inventory data table

Figure 10: SSRS list rollup

An alternative to using the SSIS data components for SharePoint is to write your own custom SSIS script task. Anup Kafle describes how to do this in "Integrating SharePoint Data with SQL Server."

Other Approaches

There are many other ways to present SharePoint list data. For example, you could write your own solution using managed code and the SPList class in the SharePoint object model. However, this is likely to take the most effort to write and maintain. In addition, it's not likely to meet varying business requirements as easily as the platform solutions outlined earlier in this article. Another option is to consider using some of the excellent Web Parts written by Microsoft partners that design components to roll up SharePoint data, such as Bamboo Solutions and CorasWorks.

Review Your Choices

This article has investigated some of the many options available to you for presenting SharePoint list data. Review Figure 11 for a non-exhaustive summary of advantages and disadvantages associated with each approach. Although there are many more ways to present list data than we could fit into this article, you should now have a good idea of your options. By combining this information with the many excellent resources mentioned, you'll be well equipped to meet the list-data presentation needs of your users.

Ethan Wilansky ([email protected]) is a contributing editor for Windows IT Pro and a director in FTI Consulting's Technology Practice, where he focuses on creating custom SharePoint solutions. He's a Microsoft Directory Services MVP.

Stefan Kowalewski is a senior consultant on a SharePoint development team. He provides technical expertise and brings proven agile experience to custom SharePoint development efforts.

Figure 3: XML query in SSRS

http://schemas.microsoft.com/sharepoint/soap/GetListItems

Inventory

Figure 4: Improved XML query in SSRS

http://schemas.microsoft.com/sharepoint/soap/GetListItems

\{F756A34A-88DB-462C-81A1-ADE1D45982CD\}

\{EE6DF3B5-9760-4B49-9551-DE1BFDB69916\}

9999

*

Figure 8: AssetType data before and after processing in the derived column component

Figure 11: SharePoint list data presentation solution comparisons