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 sharing your comment! If your comment doesn't appear right away, please be patient as it may take a few minutes to publish or may require moderation.
  • Hi,

    I have a dataviewwebpart with filtering, sorting and grouping enabled for a list. The list has some category columns that are used in filter dropdowns. It seems the filter values in the dropdowns are populated based on the number of records returned in the list. as a consequence some columns like category have repeating values in the them,i.e duplicate entries. It would be nice if the dropdown values like category have only unique values in them. So given the above scenario how should i  modify the xslt for filter columns to achieve the above objective? Is it possible?

    Example:

    List records returned in the dataviewwebpart:

    Field1                  Field2                  Category

    ---------------------------------------------------------

    Test1                   value1                  Category1

    Test2                   value2                  category1

    Test3                   value3                  category1

    Test4                   value4                  category2

    Test5                   value5                  category2

    Filter values that are displayed for category at present:    

    Category dropdown   ->  Category1

           Category1

           Category1

           Category2

           Category2

    But what is required in the category dropdown:

    Category dropdown ->  Category1

         Category2

    If anyone has a solution please help me.

    Thank you so much.

  • I'm using the DataView web part (http://schemas.microsoft.com/WebParts/v2/DataView), with a Database connection to Sql, calling a stored procedure, with no parameters.

    When I turn on "Enable sorting and filtering on column headers (basic table layout only)", the setting is not working. The custom query renders the sorting/filtering drop down links on the column headings, yet neither works.

    I've done a view source and the fields are set correctly, eg:

    'dvt_sortfield={FirstName};dvt_sortdir={' + 'descending' + '}');"  // where FirstName is one of my stored proc field names

    Am I missing something???

    There is no mention of this setting when using SharePoint Designer help - only "Sort and Group", which are not set in my example, and would not provide the ability for the user to click on column headings.

    Thanks

  • Is there a way to update the page title of an aspx page in SharePoint based on a value in the Data Form Web Part. I have a Data Form Web Part which shows a single item from a list based on the ID passed on the query string. I would like to use the title field of this list item to set the page title for the current aspx page. Is there a way to pass values from the Data Form Web Part to the page?

  • The fix for data views to access lists across sites seems top work EXCEPT that

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

    only works for subsites of the root and NOT subsites of the subsites.

    The variant

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

    will clearly work but is not maintainable code due to the coded guid

    Any advice? None of this seems documented on msdn? Are they "secret" parameters?

  • Hi all,

    I am new to the sharepoint 2007 and requried help on SPDataSource.

    My requriments

    1 )Access list data from 2 diffent sit collection and show it on the page.

        How it can be done?

    2) I am not able to use CrossList as a DataSourceMode to access the list from same site collection.

       can any one provied sample on CrossList ?

      (I have achive this by creating linking Source).

    Please help me.

    Thanking

  • Hi all,

    I am trying to bind the "Type" field from teh document library to my custom SPGridview. I am using SPDatasource in list mode. Can anyone suggest me how to display the corresponding image field according to the type of document.

    I want to mimic the same behavior as MOSS doucument library. I was able to bind all other field but when i bind the Type field i only get  1 and 0.

                               thanks

  • I’m trying to sort and group people in the DVWP and it doesn’t seem to work.  It’s because the people (or is it person?) data type contains all of the chrome for rendering presense, etc. and so sorting an grouping don’t work as expected.  This is such a common behavior that people would want to do that I have to believe that someone has already come up with a suitable workaround.  

    I saw that you said to trim the extraneous HTML, and I did that, but it didn't solve the sorting and grouping problem ... BTW, filtering is also broken for person data types.  

    Is there a workaround, or something that I'm missing?  Thanks, Dave.

  • Do you have an example using the asp:SqlDataSource against a stored proc with parameters? This worked in 2003 and I cannot get it to work in 2007. SPD does not save the parameter values I enter.

  • Hi all

    Thabk you for the great article.

    I am using a DataFormWebPart, and I put filter on ut using query. My web part perform filtering on three fields:Title,DocNo and ORG by "And" Connective between them. I pass the user's entries to the <Query></Query> using three parameters.But when one of the entries is empty the result data is nothing.I tried to use <Switch> in <View> element but it seems that <Switch> is not allowed in <View>.

    What do you suggest I should do? (I mean some way except usimg object model and no to hamdle this problem programmaticaly)

    Thanks alot.

  • Dremillard,

    Have you ever figured out a solution to this? I am trying to achieve the same thing but having no luck with it.

    Also the filter dropdowns are screwy too

    Thanks

  • Dataview webpart is a great tool, but it has one major flaw. Aggregating data from the whole site collection is a basic function in CQWP, but in dataview you have to write to code to do the same. Why can't there just be equal settings like in CQWP?

  • Chad,

    No, I never figured it out ... or found any reference to a solution.  It would be nice to get a reply.  Eray, are you still there?  

    Dave.

  • Is there a solution for the Filter drop down not showing unique enteries?

    I have a multivalue lookup field in a dataview and want to send the information to another dataview which has the lookup list.  I want to filter the lookup list based on the value coming from the multivalue column?

    Murad.

  • Kumar and Murad,

    I was having the same issue where the filter would apply to the exact string of the column.  So if you had a multi select column with say values a, b, c filtering by 'a' would filter out this item.  The xsl variable dvt_filterval is what contains the value.  By making some changes to the assignments of dvt_FilteredRowsText, dvt_FilteredRows, and dvt_FilteredRowsAttr I was able to make it so that if the column contained dvt_filterval within the column it would not filter it out.  Here are the changes:

    <xsl:variable name="dvt_FilteredRowsText" select="$Rows[contains(.,$dvt_filterval) or ($dvt_filtertype='date' and substring-before($dvt_filterval,'T') = substring-before(.,'T'))]" />

    <xsl:variable name="dvt_FilteredRows" select="$Rows[contains(normalize-space(*[name()=$dvt_filterfield]), $dvt_filterval) or ($dvt_filtertype='date' and substring-before($dvt_filterval,'T') = substring-before(normalize-space(*[name()=$dvt_filterfield]),'T'))]" />

    <xsl:variable name="dvt_FilteredRowsAttr" select="$Rows[contains(normalize-space(@*[name()=$dvt_FieldNameNoAtSign]), $dvt_filterval) or ($dvt_filtertype='date' and substring-before($dvt_filterval,'T') = substring-before(normalize-space(@*[name()=$dvt_FieldNameNoAtSign]),'T'))]" />

  • To make it so that filter fields populated with unique values it took quite a bit of custom xslt.  The multi select columns I used were Lookup types that used existing lists.  So to be able to populate those values into a drop down you first have to add them in as data sources so that they are available in the xml.  Then to make sure they don't show up in the DataFormWebPart display you have to filter them out of the Rows variable.  My list's name was Products so the Rows assignment looks as follows:

    <xsl:variable name="Rows" select="/dsQueryResponse/Rows[@source !='Products']/Row" />

    Then create a new variable with the Rows you want visable in the drop down

    <xsl:variable name="Products" select="/dsQueryResponse/Rows[@source = 'Products']/Row" />

    The template dvt.filterfield is what populates the filter fields so you will have to pass the new variable to this filter instead of Rows.  You'll also have to custimize which field in the list the drop down is populating on.  If this field is named different then the fields in the lists being displayed in the DataFormWebPart you want the filter to populate on you'll have to write a custom filterfield template which is what I did.  My custom filter field has an extra field in it called targetfieldname so that it knows what field to apply the filter to since it is different then field populating the drop down.  Title is the field being used to populate the drop down in my case.  Here is the call to the custom template:

    <xsl:call-template name="dvt.customfilterfield">

        <xsl:with-param name="fieldname">@Title</xsl:with-param>

        <xsl:with-param name="fieldtitle">Products</xsl:with-param>

        <xsl:with-param name="targetfieldname">@Products</xsl:with-param>

        <xsl:with-param name="Rows" select="$Products" />

        <xsl:with-param name="fieldtype">text</xsl:with-param>

    </xsl:call-template>

    And here is the template being called:

    <xsl:template name="dvt.customfilterfield">

        <xsl:param name="fieldname" />

        <xsl:param name="fieldtitle" />

        <xsl:param name="targetfieldname" />

        <xsl:param name="Rows" />

        <xsl:param name="fieldtype" />                    

        <xsl:variable name="dvt_FieldNameNoAtSign" select="substring-after($fieldname, '@')" />                    

        <xsl:variable name="filtertype">

             <xsl:if test="starts-with($fieldtype,'date')">date</xsl:if>

        </xsl:variable>

        <xsl:variable name="sorttype">

        <xsl:choose>

            <xsl:when test="starts-with($fieldtype,'number')">number</xsl:when>

            <xsl:otherwise>text</xsl:otherwise>

        </xsl:choose>

        </xsl:variable>

                        <xsl:variable name="clientText">&apos; + this.options[this.selectedIndex].value + &apos;</xsl:variable>

                        <xsl:if test="not(contains($fieldname, '/')) and not(contains($fieldname, '['))" ddwrt:cf_ignore="1">

                         <xsl:value-of select="$fieldtitle" />

                          <xsl:text disable-output-escaping="yes">:&lt;br&gt;</xsl:text>

                         <select name="{$fieldtitle}_filterval">

                           <xsl:attribute name="onchange">

                             javascript: <xsl:value-of select="ddwrt:GenFireServerEvent(concat('NotUTF8;dvt_filterfield={@',$fieldtitle,'};dvt_filtertype={',$filtertype,'};dvt_adhocmode={false()};dvt_filterval={',$clientText,'}'))" />;

                           </xsl:attribute>

                           <option value="##dvt_empty##">

                             <xsl:if test="$dvt_filterfield=$fieldname and $dvt_filterval=''">

                               <xsl:attribute name="selected">true</xsl:attribute>

                             </xsl:if>

                             (Empty)

                           </option>

                           <option value="##dvt_all##">

                             <xsl:choose>

                               <xsl:when test="not($dvt_filterfield)">

                                  <xsl:attribute name="selected">true</xsl:attribute>

                               </xsl:when>

                               <xsl:when test="$dvt_filterfield!=$fieldname">

                                  <xsl:attribute name="selected">true</xsl:attribute>

                               </xsl:when>

                             </xsl:choose>

                             (All)

                           </option>

                           <xsl:value-of select="ddwrt:NameChanged('',1)" />

                           <xsl:choose>

                              <xsl:when test="starts-with($fieldname, '@')">

                                <xsl:variable name="dvt_Rows">

                                  <xsl:for-each select="$Rows">

                                    <xsl:sort select="@*[name()=$fieldname]" order="ascending" data-type="{$sorttype}" />

                                   <xsl:copy-of select="." />

                                 </xsl:for-each>

                               </xsl:variable>

                                <xsl:for-each select="msxsl:node-set($dvt_Rows)/*[not(@*[name()=$dvt_FieldNameNoAtSign]=preceding-sibling::*[1]/@*[name()=$dvt_FieldNameNoAtSign])]/@*[name()=$dvt_FieldNameNoAtSign]">

                                  <xsl:sort data-type="{$sorttype}" />

                                  <xsl:call-template name="dvt.filteroption">

                                    <xsl:with-param name="name" select="$targetfieldname" />

                                    <xsl:with-param name="value" select="." />

                                    <xsl:with-param name="type" select="$fieldtype" />

                                  </xsl:call-template>

                               </xsl:for-each>

                             </xsl:when>

                              <xsl:when test="$fieldname = '.'">

                                <xsl:variable name="dvt_Rows">

                                  <xsl:for-each select="$Rows">

                                    <xsl:sort select="." order="ascending" data-type="{$sorttype}" />

                                   <xsl:copy-of select="." />

                                 </xsl:for-each>

                               </xsl:variable>

                                <xsl:for-each select="msxsl:node-set($dvt_Rows)/*[not(.=preceding-sibling::*[1])]">

                                  <xsl:sort data-type="{$sorttype}" />

                                  <xsl:call-template name="dvt.filteroption">

                                    <xsl:with-param name="name" select="$targetfieldname" />

                                    <xsl:with-param name="value" select="." />

                                    <xsl:with-param name="type" select="$fieldtype" />

                                  </xsl:call-template>

                               </xsl:for-each>

                             </xsl:when>

                              <xsl:otherwise>

                                <xsl:variable name="dvt_Rows">

                                  <xsl:for-each select="$Rows">

                                    <xsl:sort select="*[name()=$fieldname]" order="ascending" data-type="{$sorttype}" />

                                   <xsl:copy-of select="." />

                                 </xsl:for-each>

                               </xsl:variable>

                                <xsl:for-each select="msxsl:node-set($dvt_Rows)/*[not(*[name()=$fieldname]=preceding-sibling::*[1]/*[name()=$fieldname])]/*[name()=$fieldname]">

                                  <xsl:sort data-type="{$sorttype}" />

                                  <xsl:call-template name="dvt.filteroption">

                                    <xsl:with-param name="name" select="$targetfieldname" />

                                    <xsl:with-param name="value" select="." />

                                    <xsl:with-param name="type" select="$fieldtype" />

                                  </xsl:call-template>

                               </xsl:for-each>

                             </xsl:otherwise>

                           </xsl:choose>

                           <xsl:value-of select="ddwrt:NameChanged('',1)" />

                          </select>

                       </xsl:if>

                     </xsl:template>

    Hope this helps

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