SPDataSource and Rollups with the Data View

Hello -- my name is Eray Chou. I’m a Program Manager on the SharePoint Designer team. I focus mostly on data and app building features, and last release I worked on the Data View. During the last year or so, I also led development for the Application Templates for Windows SharePoint Services 3.0. I’m looking forward to posting tips and tricks about using the Data View (the Swiss Army Knife of web parts) and JavaScript (Duct Tape on the web). As MacGyver taught us – you accomplish some pretty cool things with a Swiss Army Knife and Duct Tape. For this post, I’ll give a brief overview of the SPDataSource control, as well as cover a pretty popular question: How do I create a view that rolls up data from across my site collection?

Data View vs. Data Form

Before we go deeper, you may have noticed that many of us use “Data View” and “Data Form” rather interchangeably. In Windows SharePoint Services v2, we shipped a web part called the DataViewWebPart (DVWP). This web part uses XSLT to transform data from Data Retrieval Services to HTML. In Windows SharePoint Services v3, we shipped a web part called the DataFormWebPart (DFWP). The DataFormWebPart still uses XSLT, but now uses ASP.Net 2.0 Data Source Controls for data access. In addition to “view” functionality, the DataFormWebPart also introduced “form” functionality to write back to various data sources. We use Data View generically to refer to the feature set and all of SharePoint Designer’s UI still uses the term Data View. Because this blog is SPD and Windows SharePoint Services v3 centric, we always mean DataFormWebPart (DFWP) under the covers unless otherwise specified.

Data Source Controls and SPDataSource

As noted, in Windows SharePoint Services, we redesigned Data Views to be an ASP.Net data bound control that uses ASP.Net 2.0 Data Source Controls for data access. To support this improvement, we also shipped a number of Data Source Controls for different data source types like the SPDataSource for SharePoint list data, XmlUrlDataSource for RSS/REST, and SoapDataSource for SOAP Web Services. Developers can also implement their own data source controls for other data source types. As long as a data source control implements the IDataSource or IHierarchicalDataSource, you can use the Data View to display data from that control.

Most of the Data View’s List-related features use the SPDataSource in List or ListItem mode. In these modes, SPDataSource returns multiple or single rows of list data respectively. However, in addition to these modes, the SPDataSource has a number of other modes that aren’t directly exposed in the UI: ListofLists, Webs, and CrossList mode. ListsofLists and Webs mode show the collection of lists and the collection of subwebs for a given Web site.

Here's a sample of SPDataSource:

<SharePoint:SPDataSource runat="server"

DataSourceMode="List"

SelectCommand="<View></View>"

id="DataSrc1" >

<SelectParameters>

<WebPartPages:DataFormParameter Name="ListID"

ParameterKey="ListID"

PropertyName="ParameterValues"

DefaultValue="AAAAAAAA-BBBB-CCCC-DDDD-EEEEFFFF0000" />

</SelectParameters>

</SharePoint:SPDataSource>

The key parts of the markup include:

  • DataSourceMode – type of WSS data to query. The valid modes are List, ListItem, ListOfLists, Webs, and CrossList.
  • SelectCommand – actual query for that data type. Some modes don’t require a query at all. In List mode, the SelectCommand roughly maps to the SPQuery object and accepts Collaborative Application Markup Language (CAML) fragments to specify things like the set of return fields, filtering, and sort order. The example does not specify any query, so the SPDataSource will return all fields and all items.
  • SelectParameter – each command expects a number of different parameters. These parameters can refer to the parameter bindings collection in the Data View, as well as take most ASP.Net types of parameters like Control and QueryString. The example only specifies a ListID GUID, so the SPDataSource will return data from the list with the corresponding ListID.

For more information on Collaborative Application Markup Language's query syntax, check out these links:

SPDataSource Parameteres

When in List or ListItem mode, the SelectParameters collection expects a number of well known parameter names to determine the appropriate list to bind to:

  • ListID – GUID that corresponds to a List’s ListID.
  • ListName – Display name for a List.
  • ListItemId – ID for a single item in ListItem mode
  • WebUrl – Url to the web. When not specified, SPDataSource uses the current web to resolve the previous parameters. You can also use this parameter to access lists in other Web sites in the same Site Collection assuming you have permissions to that list.

In the previous example, the ListID parameter was a static value and just used the value specified in DefaultValue. Parameters can also be dynamic and used inside a query, as demonstrated in the next example:

<SharePoint:SPDataSource runat="server"

DataSourceMode="List"

SelectCommand="<View>

<Query>

<Where>

<Eq>

<FieldRef Name='ContentType'/>

<Value Type='Text'>{ContentTypeName}</Value>

</Eq>

</Where>

</Query>

</View>"

id="DataSrc1" >

<SelectParameters>

<asp:QueryStringParameter Name="ContentTypeName"

QueryStringField="CType"

DefaultValue="Announcements"/>

<WebPartPages:DataFormParameter Name="ListID"

ParameterKey="ListID"

PropertyName="ParameterValues"

DefaultValue="AAAAAAAA-BBBB-CCCC-DDDD-EEEEFFFF0000"/>

</SelectParameters>

</SharePoint:SPDataSource>

In this example, the SelectCommand specifies a filter for items of a specific content type. The SPDataSource uses curly braces to specify substitution parameters: {parametername} . Notice that the QueryStringParameter is named “ContentTypeName” and within the SelectCommand the Where clause is comparing a field value to “{ContentTypeName}”. At runtime, the value of ContentTypeName parameter is pulled from a QueryString variable called CType, and its value is substituted into the SelectCommand.

CrossList Mode

One of the most powerful new OM calls in Windows SharePoint Services v3 is the GetSiteData method on the SPWeb object. This method makes it possible to query for items across multiple lists in multiple Web sites in the same site collection. This is most often used for queries like, “show all tasks that are due today”, “show all documents created by me in the site collection”, or “show all announcements in the site collection”. The SPDataSource exposes this functionality when in CrossList mode.

<SharePoint:SPDataSource runat="server"

DataSourceMode="CrossList"

SelectCommand="<Webs Scope='Recursive'></Webs>

<Lists ServerTemplate='104' BaseType='0'></Lists>

<View>

<ViewFields>

<FieldRef Name='ID'/>

<FieldRef Name='ContentType'/>

<FieldRef Name='Title'/>

</ViewFields>

</View>"

id="DataSrc1" >

</SharePoint:SPDataSource>

The SelectCommand for SPDataSource in CrossList mode roughly maps to the SPSiteDataQuery object, much like how the SelectCommand in List mode roughly maps to the SPQuery object. The new pieces of syntax are the Lists and Webs elements.

  • Webs element – specifies the scope of the query. “Recursive” scope covers all web descended from the current Web site. When using this element with SPDataSource, use a closing element tag (</Webs>) as opposed to single element notation (<Webs />)
  • Lists element – limits the query to lists with a specific ServerTemplate or BaseType. In the example above, the element limits the query to Announcements lists (ServerTemplate='104'). See the description of the Type attribute for a list of ServerTemplate numbers: https://msdn2.microsoft.com/en-us/library/ms462947.aspx
  • Lists sub-elements
  1. <List ID="GUID"> - supports asking for specific lists by ListID.
  2. <WithIndex FieldId="GuidOfField" Type="Text" Value="" /> - limits the query to lists with indexed fields.

Create a view that shows all announcements in the site collection

Using SPD, the easiest way to create a Data View that uses a CrossList query is to design much of the view while in List mode, switch into code view, and add the additional elements by hand. Here are the steps to create a simple view that shows all announcements in the site collection.

  1. Open a Windows SharePoint Service v3 Team Site in SharePoint Designer.
  2. Insert a view on a the Announcements list with the Title, Body, and Modified fields.
    For more information on how to insert a Data View, see the articles here: https://office.microsoft.com/en-us/sharepointdesigner/CH100667641033.aspx
  3. At the top of the Data Source Details task pane, click “Announcements…” to open the data source properties for the current view.
  4. Click on Fields and remove all fields except ID, Title, Body, and Modified.
  5. Switch to code view, and search for DataSourceMode
  6. Change the value from List to CrossList
  7. Next search for selectcommand
  8. In the select command, type <Webs Scope='Recursive'></Webs> immediately after the opening quote, and leave the rest of the selectcommand as it. You should see something like this: selectcommand="<Webs Scope='Recursive'></Webs>&lt;View&gt; ...”
  9. Save the page and browse to it (F12).

Hope you enjoyed my first post.

Thanks,
-- eray