SPDataSource and Rollups with the Data View

SPDataSource and Rollups with the Data View

Rate This

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: http://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: http://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

  • Thanks for some great info!!!

    I have a question about accessing web services across Web Applications using the SoapDataSource.

    Unless the web application is using Kerberos authentication I can't get this to work.

    I get an error saying:

    http://server/_vti_bin/webpartpages.asmx and http://server:85/_vti_bin/lists.asmx">http://server:85/_vti_bin/lists.asmx are not in the same web application.  Their domains cannot be compatible.

    The scenario I tested is as follows:

    1) Create a DataFormWeb part using SharePoint Designer and put it on web application http://server:80

    Consume data from the Lists.asmx Web Service that resides on Web Application http://server:85

    WSS won't allow this, if I look in the WSS log file I se entries like this:

    http://server/_vti_bin/webpartpages.asmx and http://server:85/_vti_bin/lists.asmx">http://server:85/_vti_bin/lists.asmx are not in the same web application.  Their domains cannot be compatible.

    I belive this is logged from SPSite.ValidateDomainCompatibility.

    Can you verify that I'm not supposed to be able to call across Web Applications by design.

    Is there some setting that I can change to allow this?

    Thanks in advance

    /Jonas Nilsson

  • In WSS v2 the XSL, Parameters, and Data Source were exposed in the Data View properties web part editor directly from the web interface.  

    In the WSS v3 implementation of the Data View (form?) Web Part the Data Source Editor is missing (from the web based web part editor).  This is a real disappointment.  How might a user best edit the Data Source of a Data View Web Part without opening up SPD?

    Thanks for the great post!

  • Eray,

    Great article - I hope to test it out shortly.  Since you identified yourself as leading the development for the Application Templates for Windows SharePoint Services 3.0, I’m sure you might be interested in my issue below.  I have posted it on several MS/other web sites and received not response...

    To Whom It May Concern:

    Could someone help me or direct me to the right resource to investigate this issue regarding a SharePoint 2003 Application Template...

    While working in the SharePoint 2007 environment and using the Application Template "Budgeting and Tracking Multiple Projects", I was adding "% Complete" to the "Project Milestones" list in order to add a Gantt View.  When trying to ADD a new Milestone, only the default columns appear and "% Complete" does not appear.  This defeats the purpose of a milestone list to show progress.  I have tried this in 2 environments and have concluded this is either a major BUG in the "Project Milestone" list or the "Budgeting and Tracking Multiple Projects" template.  How do issues like these get resolved and/or identified to Microsoft?

    Thank-you for your help,

    Rod

  • I have an interesting observation regarding the DataFormWebPart in WSS 3.0.  

    I have been using the web part successfully creating online reports on an issues list.  When I use the "Group By" feature on fields such as Issue Status, I can get a nice total using an xsl function such as count($nodeset) or sub counts with count($nodeset[@Status='Resolved']).  However when I "Group by" the field "Created By" I ALWAYS get 0 for count($nodeset).  This makes it impossible to report on activity based on the Author of an Issue.  Is this a bug?  Can anyone suggest a workaround?

    Thanks in advance for any help on this matter!

  • Great article, thanks this clears up a lot.

    Iis there any way to create the datasource (pointing to a list) without using the GUID of the datasource? Such as using the name or url. We would like to use the Data View webpart in a template that would point to different lists, and a GUID makes it rather difficult for anyone but developers to set. I found a reference to an undocumented ListName parameter but am having trouble getting it to work.

    Another idea I had was using the CrossList to fake out Sharepoint and somehow get it to look at only one List.

    This is a huge issue for us and no one seems to have the answer.

    thanks

    Chris

    chrisb76@yahoo.com

  • Jonas –  Yes, you’re not supposed to call across Web Applications; unfortunately, there isn’t a setting to change this.  

    Tim G. – You won’t be able to edit the DataSource of a DataFormWebPart without opening SPD, is there a particular reason why you want to avoid opening up SPD? =)  If you’re concerned about customizing a page, you could edit the page in SPD, and then select the option to “Reset to site definition”.

    Rod – The easiest way to get this to show up is:

      1.) Go to list settings --> Advanced Settings and select “allow management of content types

      2.) Click on the content type, and then select “Add from existing site or list columns”

    This behavior is caused by the hidden=”true” setting on the content type definition feature.  

    As for your general question on how these issues get resolved or identified -- the Application Templates for WSS are a supported product so you can go through Microsoft’s Support Services: http://www.microsoft.com/services/microsoftservices/srv_support.mspx

    James – For People fields, the SPDataSource returns the entire HTML blob necessary to render as a hyperlink with presence information.  The presence information is actually unique on a per-item basis even if it refers to the same person.  The generic workaround for these sorts of issues is to do the grouping comparisons using the substring function to trim after the person’s name – for example something like this:  substring-before(@Author,'&lt;/A&gt;').  

    Chris – Yes, in addition to using a ListId, you can also specify a ListName which takes the displayname of a list:  

    <SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="<View></View>" id="Announcements1">

     <SelectParameters>

       <WebPartPages:DataFormParameter Name="ListName" ParameterKey="ListName" PropertyName="ParameterValues" DefaultValue="Announcements"/>

     </SelectParameters>

    </SharePoint:SPDataSource>

    Also make sure you have a matching entry in the parameter bindings section:

    <ParameterBinding Name="ListName" Location="None" DefaultValue="Announcements"/>

    It’s also worth noting that we do support ListId fixup in STPs as long as the GUID is wrapped in curly braces:

       DefaultValue="{E7C01128-5BAD-4331-BE8E-EABC5CBE7542}"

    There’s a known issue that we’re investigating that SPD doesn’t always spit out curly braces around the GUIDs.  You’ll want to double check that the ListID is correctly wrapped in the SPDataSource’s parameter collection, the DFWP’s parameter bindings, and if you inserted the DFWP into a web part zone, there may also be a ListName attribute on the DataFormWebPart tag.  

    thanks,

    -- eray

  • Thanks I got that working and can change the list it points to in the Parameters section when I select "Modify Shared web part" (obviously changing to a list of the same Content Type).

    One last thing, do you know any way to put in relative URLs? :) It seems that's the million dollar question. Our client department wants to store a list on a parent site (the template for this site will have the list) and put this in a meeting workspace template so it will automatically show up. Sharepoint does not seem to like using ".."  There must be some template call somewhere I imagine. Otherwise our second choice is creating a web part using a GridView or SPGridView, since I could easily just parse the url in there.

    thanks

  • Chris – There are a lot of ways to do relative urls:

    Server Relative URL:

        <asp:Parameter Name="WebUrl" DefaultValue="/subweb"/>

    Site Collection Relative:

    This is in context of the site collection root for the opened web.  For example, assume my site collection root is:  http://server/sites/sitecollection/

    This parameter opens this web: http://server/sites/sitecollection/subweb2

        <asp:Parameter Name="WebUrl" DefaultValue="subweb2"/>

    Site Collection Root itself:

        <asp:Parameter Name="WebUrl" DefaultValue="{sitecollectionroot}"/>

    Alternatively you can also use WebID.

        <asp:Parameter Name="WebId" DefaultValue="SomeGuid"/>

    These parameters are compatible with both the ListId and ListName parameters.

    Now about that million dollars....  :)

    thanks,

    -- eray

  • Is it possible to insert a list item using the DataFormWebPart from another site in the collection? I tried setting the WebURL parameters, but I get an unspecified error when I view the page and the controls do not preview in SPD. I created the custom form in the correct site, then copied the code to a page in a site above where the list exists. Is there another way? I can get it to work using the data source control and binding to asp.net controls.

  • Its great that you can specify the ListName as the SelectParameter is SPDataSources. By default SPD created the data sources using the Guid, and these break down when exported to a different server. Using the ListName rather than the Guid ensures that the DataSource still works when it is exported.

    Thanks

  • I'm no developer, let me make that clear :) - But I'm seeing some odd things happening while using SPD and trying to make data form views.

    1. If I use the ListID parameter, which uses the GUID of the list, I continually get errors telling me that the list no longer exists. After clicking OK I'm still able to make the changes I need and the view (sort of) works. These problems go away if I manually change the code to use ListName instead of ListID.

    2. SPD is tossing in a total of 4 separate sets of <WebPartPages:DataFormParameter> which makes for a lot of extra changes when I use ListName in place of ListID. Also, if I change the parameters using the UI (Common Data View Tasks -> Parameters) not all of those DataFormParameter sets is updated and I have to go back and manually fix them.

    3. Sometimes when applying Sorts and Groups the code is generated incorrectly and the connection is broken. Also, some of my colums don't show up in the list of available fields until I build the expression manually. Yet, those same fields show up fine in other screens, such as "edit columns".

    Any thoughts on this? It seems to me these weird things shouldn't be happening... They're really starting to tick me off as well. :(

  • Eray,

    is it possible to create a dynamic datasource by having parameters in the SelectCommand? Example a create a datasource whose CAML is:

    SelectCommand="<View>

    <Query>

    <Where>

    <Eq>

    <FieldRef Name='Title'/>

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

    </Eq>

    </Where>

    </Query>

    </View>"

    This parameter would then be updated via some SharePoint control. This would allow us to create views dynamically on any SharePoint data, rather than be limited to fixed text in the CAML. I've been looking high and low for something like this, but I haven't come across anything similar yet.

    An example of its usage is would for creating filters on Lookup columns. I have a habit of creating Categorization Lists with just a Title, and then use a Lookup column on the Categorization list. Thus users can add an item to the list and more categorization items are created (or change items etc...)

    A typical example is I create a Status List with the following item Work In Progress, Advanced, Completed. I then use a Lookup to the Status List in Document Libraries or other lists.

    If I had a facility of passing parameters similar to the above, I would not have to create a new view each time that a new item is added to the list but have a single DataSource which takes parameters dynamically from a dropdown.

    Thanks

  • Hi Eray,

    I assume you haven't got your million dollars yet :p

    From what I have read above, it seems like you can:

    1 - access lists across sites in the same site collection.

    2 - not access lists across web applications because we're not supposed to do it.

    My question is:

    Can you access lists in the same web application but across different site collections?

    I don't have a million dollars but I would express much gratification :)

    Dave

  • Hi,

    I am also using Budgeting and Tracking Multiple Projects template. We have a requirement to create a subsite for each project and some how rollup all the tasks,  issues for all the projects on the top level site so that the management team can see a real dashboard view of all the projects and related tasks. Each of the project team members can enter their tasks and issues on the project subsite.

    How do I achieve this functionality using Dataview webpart?

    Thanks

  • My question:

     I have been using the 'Job Requisition and Interview Management' application template in my WSS 3.0 environment. I have been customizing the site according to our needs. But, on the latest development front, we had a requirement where I should be dynamically fill out a Multiline text box with values based on the Job Title i select.

     For (e.g) In the NewForm.aspx for filing a new requisition a user can select the Job Title he wants to post. All the details pertaining to a given Job Title are already stored in a seperate custom list. Now, when the user selects a Job Title, automatically based on the selection made, the Job Description and Work Experience multiline text boxes should be prepopulated with some defined text which is stored in the custom list. All this happen in the same NewForm.aspx page.

     I have been looking out at multiple places since many weeks, but till now could not get a solution. I am posting here to expect some inputs so that i will be able to solve the problem. Any help is really appreciated.

    thanks,

    -Kiran M

Page 1 of 8 (112 items) 12345»
Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post