Displaying Columns in a Crosslist DVWP

I’ve alluded to this in the past, but I had a client situation in the last few days that brought it top of mind again. One of the quirks of a Crosslist DVWP is that you *must* specify all columns you would like to display in the CAML for the Data Source. You can add any column in the XSL you want, but if they aren’t explicitly specified in the CAML, you will get blank results.

Here’s an example. Say you want to gather the latest announcements from across the Site Collection for display on the Home Page of your Intranet. You’re displaying the latest 5 announcements, but you realize that annoucements that are expired are still showing up. So, you add a filter to the row select, as below, and you’re not seeing what you’d expect.

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row&#91;
    (ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd') >=
    ddwrt:FormatDateTime(string(@Expires), 1033, 'yyyyMMdd')) or
    string-length(@Expires) = 0
  ]"/>

You’ll need to add an explicit FieldRef for the Expires column in your CAML, as below.  (I’ve added carriage returns and white space into the CAML above for readability, but you can’t have them in your DVWP.)

<SharePointWebControls:SPDataSource
  runat="server"
  DataSourceMode="CrossList"
  SelectCommand="
    <View>
      <Webs Scope='SiteCollection'></Webs>
      <Lists ServerTemplate='104'></Lists>
      <ViewFields>
        <FieldRef Name='ID'/>
        <FieldRef Name='Title'/>
        <FieldRef Name='Body'/>
        <FieldRef Name='FileDirRef'/>
        <FieldRef Name='Created'/>
        <FieldRef Name='Author'/>
        <FieldRef Name='Expires'/>
        <FieldRef Name='PermMask'/>
      </ViewFields>
    </View>"
  UseInternalName="true"
  ID="dataformwebpart5">
</SharePointWebControls:SPDataSource>

Once you add the FieldRef to the CAML, the Expires column values are available for use in your filter.

Keep in mind that you need to be careful what you add into your <Viewfields> section. If you add a FieldRef for a column which doesn’t occur in *all* of the items which you want to retrieve, you will, in effect, be adding a filter to the CAML. Only items which have all of the columns specified in the FieldRefs will be returned.

31 Comments

  1. Marc,

    I have created a crosslit dataview according to your example and have included the ViewFields and appropriate FieldRefs. I have one document library in my SC that has two custom columns derived from a Content Type. If I put one of the fields in the select command I receive results. If I put the other one in there, I do not. Each of these fields have values within the library itself. Any ideas as to why this is happening?

    Reply
  2. Marc,

    I think I figured out my problem. The first column that will display is a Lookup and the one that doesn’t is a LookupMulti. Evidently, you can’t query LookupMulti columns in a crosslist query.

    Reply
    • Chris:

      Sorry for the delayed response to your question. I was on Martha’s Vineyard with (gasp!) no real Internet connection other than my iPhone. As much as I love my iPhone, it’s just not condusive to writing longer stuff, at least for me.

      Glad you figured out your question.

      M.

      Reply
  3. Hi
    Hi marc
    I am great fan of your article, I am regularly following your articles, based on your middle tier architecture approach, i am developing new internet facing portal, now i am struck into
    serious issues, i hope you are right person to anwer the question.

    We are using sharepoint 2010 for internet facing sites for our big client in healthcare domain . I am using XSLT list view webpart in sharepoint designer 2010 for retrieving contents from sharepoint list & displaying it by using XSLT List view webpart in publishing sites.

    We are having lot of list in rootsite collection, If i am creating pages by using XSLt list view webpart in root site collection I can access the list,View and Display in Pages by XSLT List view webpart.

    Now the requirement is i have to access the list in roosite collection(parent site) from subsites. In Sharepoint designer 2010, while i am inserting XSLT list view webpart, i could not find datasource to access the list that exist in rootsite collection(Parent site) from subsite.

    Below is the the code i am using for accessing the list& view from parent site & creating the pages in parent site by using xslt list view webpart,now i wants to access parent site list from subsite by using XSLT list view webpat in subsite pages.

    Pls let me know what i have to change in the code level

    30

    0

    Table

    In sharepoint designer 2010, there is no datasource to access library in parent site from subsite. Is the soap Service connection is the only way to access the crosssite list or XsLT view having the option to do crosssite list access.

    I would appreciate early response for this one.

    Thanks
    Ferdinant.

    Reply
  4. Mark, if you have a minute, i hope that you might be able to answer a few questions i have about your code.
    1. Does it matter if the Webs Scope statement is inside of the View node? i have seen other articles that tell me to put the Webs before the View.
    2. Is the Lists node required?
    3. On line 24, what does this ID refer to? in my pages sometimes it seems to reference a webpart and other times the name of the currrent data source and i’m confused.
    Thanks in advance

    Reply
    • Dean:

      1. Does it matter if the Webs Scope statement is inside of the View node? i have seen other articles that tell me to put the Webs before the View.
      ==> I just tried moving the Webs node outside the View node and it worked fine. I’d always refer to the CAML schemas directly on stuff like this, if possible, because there’s lots of bad info out there. When in doubt, try it!
      2. Is the Lists node required?
      ==> No, it isn’t required. In this case, I’m filtering for the ServerTemplate=”104″, which is the Announcments template. If you didn’t want to do an analogous filter, you wouldn’t need this node.
      3. On line 24, what does this ID refer to? in my pages sometimes it seems to reference a webpart and other times the name of the currrent data source and i’m confused.
      ==> I assume you mean line 20? That ID is generated by SharePoint Designer when you create the DataSource. There’s no need to change it and it probably isn’t a good idea to do so unless you’re trying to do something specific. I think it is just a sequentially numbered id. I checked a page where I have 2 DVWPs and the second one has the id set to dataformwebpart2.

      M.

      Reply
  5. Marc, this is EXACTLY what I needed to solve this perplexing issue. Thank you so much for taking the time to post it! BTW, love your work on endusersharepoint.com (nothingbutsharepoing.com).

    Reply
  6. Using this crosslist method for DVWP to retrieve list data in a root site from a sub site, I’m having a problem getting the correct server template ID for the CAML. The lists I’m accessing are custom lists not based on any specific template. So, what is the ID for the CAML then on my custom lists?

    Reply
  7. Hi marc,

    i have added the the FieldRef to the CAML, and now
    after a refresh in SPD ruslts are showing but when
    I try it on browser… their is a line in the table but
    the data is not showing..

    can u help?? do u have any idea?

    Reply
    • ran:

      It’s hard to say without seeing your code and your list structures. Make sure that the columns that you are displaying in the DVWP match those you are requesting in the ViewFields section. Since you are seeing empty rows, you know that the items are being returned, but not the values for the columns you want.

      M.

      Reply
      • hi Marc,

        To be exact, even when i should see 2 rows (= 2 docs)
        i see only one blank row..
        could it be that when on CrossList mode
        the query returns a diffrent type of data structure??

        Reply
        • No, the data structure is still the flat items you’d expect. It sounds like the filtering you’ve got in place is not getting the items you expect. Keep in mind that adding fields to the ViewFields is in effect a filter; any items which don’t have those fields will not be returned, as I note above.

          M.

          Reply
          • hi again,

            I have eventually decided to do it
            in code by a usercontrol, using SPSiteDataQuery..

            it works fine, but the DataTable I’m getting from query has only string columns..
            is there a way to get the actual columns from the query??

            (btw, many thanks for all your help :)

            Reply

Have a thought or opinion?