How do I create DVWP showing multiple lists with latest item from each list?

Question

We have a MOSS site which is using a DVWP to pull in from multiple subsites' list items to display as a calculated field KPI set (just as Christophe has suggested on his forum page).

This works great, except I need to be able to display the latest entry from each list and when it changes, dsplay the new entry (i.e. the entry for April 2010 will now be replaced by May 2010)

I have used a month title field rather than the title of the project as the list already has a title. It would be much easier to show a screenshot, but unfortunately this site is not publicly available.

<WebPartPages:DataFormWebPart runat="server" ShowWithSampleData="False" AllowRemove="False" AllowHide="False" SuppressWebPartChrome="False" PartImageLarge="" ViewContentTypeId="" ExportMode="All" Dir="Default" MissingAssembly="Cannot import this Web Part." AllowClose="False" ID="g_6ed8e42c_55e1_4a69_99e9_552c0ecf4ee4" DataSourceID="" IsIncludedFilter="d479ca9b-c8a0-4766-a046-4637ed474719,7d19c1c4-8b55-4aca-ac7e-987ec9f9c7c6,1dc235a5-f0eb-4e70-a409-f622934a2b2b,a5180607-6903-48b4-9da1-8f9f889c43ee;;;;" DetailLink="" AllowEdit="True" UseSQLDataSourcePaging="True" HelpMode="Modeless" IsIncluded="True" Description="" NoDefaultStyle="TRUE" FrameState="Normal" AllowConnect="True" ViewFlag="0" AllowZoneChange="False" AllowMinimize="False" Title="Emirates Project Monitoring" PartOrder="3" AuthorizationFilter="d479ca9b-c8a0-4766-a046-4637ed474719,7d19c1c4-8b55-4aca-ac7e-987ec9f9c7c6,1dc235a5-f0eb-4e70-a409-f622934a2b2b,a5180607-6903-48b4-9da1-8f9f889c43ee;;;;" PageSize="4" FrameType="TitleBarOnly" HelpLink="" PartImageSmall="" ConnectionID="00000000-0000-0000-0000-000000000000" ExportControlledProperties="True" ChromeType="TitleOnly" IsVisible="True" __MarkupType="vsattributemarkup" __WebPartId="{9C2BC532-FE6F-44CA-A828-B82F21289637}" __AllowXSLTEditing="true" WebPart="true" Height="" Width=""><ParameterBindings>
                   <ParameterBinding Name="dvt_apos" Location="Postback;Connection"/>
                   <ParameterBinding Name="UserID" Location="CAMLVariable" DefaultValue="CurrentUserName"/>
                   <ParameterBinding Name="Today" Location="CAMLVariable" DefaultValue="CurrentDate"/>
               </ParameterBindings>
<DataFields>


@Title,Month;@Project_x0020_Status,Staffing;@Project_calculated_risk,Staffing_Calc_Risk;@Project_indicators,Staffing_KPI;@ContentType,Content Type;@Reporting,Reporting;@Staffing_x0020_Comments0,Staffing Comments;@Reporting_Calc_Risk,Reporting_Calc_Risk;@Reporting_Indicator,Reporting_KPI;@Reporting_x0020_Comments,Reporting Comments;@Planning,Planning;@Planning_calc_risk,Planning_calc_risk;@Planning_Indicator,Planning_KPI;@Planning_x0020_Comments,Planning Comments;@Logistics,Logistics;@Logistics_Calc_Risk,Logistics_Calc_Risk;@Logistics_Indicator,Logistics_KPI;@Logistics_x0020_Comments,Logistics Comments;@Project_x0020_Scope,Project Scope;@Scope_Calc_Risk,Scope_Calc_Risk;@Scope_Indicator,Scope_KPI;@Scope_x0020_Comments,Scope Comments;@Client_x0020_Relationship,Client Relationship;@Relationship_Calc_Risk,Relationship_Calc_Risk;@Relationship_Indicator,Relationship_KPI;@Client_x0020_Relationship_x0020_,Client Relationship Comments;@Project_x0020_Budget,Project Budget;@Budget_Calc_Risk,Budget_Calc_Risk;@Budget_Indicator,Budget_KPI;@Budget_x0020_Comments,Budget Comments;@Quality_x0020_of_x0020_Delivery,Quality of Delivery;@QOD_Calc_Risk,QOD_Calc_Risk;@QOD_Indicator,QualityOfDelivery_KPI;@Quality_x0020_of_x0020_delivery_,Quality of delivery Comments;@Project_x0020_Progress,Project Progress;@Progress_Calc_risk,Progress_Calc_risk;@Progress_Indicator,Progress_KPI;@Project_x0020_Progress_x0020_Com,Project Progress Comments;@Evidence_x0020_of_x0020_Impact,Evidence of Impact;@EoI_Calc_Risk,EoI_Calc_Risk;@EoI_Indicator,EvidOfImpact_KPI;@Evidence_x0020_of_x0020_Impact_x,Evidence of Impact Comments;@Project_x0020_Location,Project Location;@Target_x0020_Audiences,Target Audiences;@Progress,Progress;@Progress_x0025_,Progress%;@Project_Progress,Project_Progress;@Proj_Name_AD,Proj_Name_AD;@Month_created,Month_created;@ID,ID;@Modified,Modified;@Created,Created;@Author,Created By;@Editor,Modified By;@_UIVersionString,Version;@Attachments,Attachments;@File_x0020_Type,File Type;@FileLeafRef,Name (for use in forms);@FileDirRef,Path;@FSObjType,Item Type;@_HasCopyDestinations,Has Copy Destinations;@_CopySource,Copy Source;@ContentTypeId,Content Type ID;@_ModerationStatus,Approval Status;@_UIVersion,UI Version;@Created_x0020_Date,Created;@FileRef,URL Path;@Project_title,Project_title;</DataFields>
<DataSources>
<SharePoint:AggregateDataSource runat="server" IsSynchronous="" SeparateRoot="" RootName="" RowsName=""><Sources><SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="&lt;View&gt;&lt;/View&gt;" UseInternalName="True"><SelectParameters>
<WebPartPages:DataFormParameter ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{704551EA-1271-40C5-B183-994920F2F74E}" Name="ListID"></WebPartPages:DataFormParameter>
</SelectParameters>
<UpdateParameters>
<WebPartPages:DataFormParameter ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{704551EA-1271-40C5-B183-994920F2F74E}" Name="ListID"></WebPartPages:DataFormParameter>
</UpdateParameters>
<InsertParameters>
<WebPartPages:DataFormParameter ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{704551EA-1271-40C5-B183-994920F2F74E}" Name="ListID"></WebPartPages:DataFormParameter>
</InsertParameters>
<DeleteParameters>
<WebPartPages:DataFormParameter ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{704551EA-1271-40C5-B183-994920F2F74E}" Name="ListID"></WebPartPages:DataFormParameter>
</DeleteParameters>
</SharePoint:SPDataSource><SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="&lt;View&gt;&lt;/View&gt;" UseInternalName="True"><SelectParameters>
<asp:Parameter DefaultValue="{20369E43-6899-4603-A964-5003C65DB60C}" Name="ListID"></asp:Parameter>
</SelectParameters>
<UpdateParameters>
<asp:Parameter DefaultValue="{20369E43-6899-4603-A964-5003C65DB60C}" Name="ListID"></asp:Parameter>
</UpdateParameters>
<InsertParameters>
<asp:Parameter DefaultValue="{20369E43-6899-4603-A964-5003C65DB60C}" Name="ListID"></asp:Parameter>
</InsertParameters>
<DeleteParameters>
<asp:Parameter DefaultValue="{20369E43-6899-4603-A964-5003C65DB60C}" Name="ListID"></asp:Parameter>
</DeleteParameters>
</SharePoint:SPDataSource><SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="&lt;View&gt;&lt;/View&gt;" UseInternalName="True"><SelectParameters>
<asp:Parameter DefaultValue="{4B3F56EF-2747-4560-965E-D0FD903C4239}" Name="ListID"></asp:Parameter>
</SelectParameters>
<UpdateParameters>
<asp:Parameter DefaultValue="{4B3F56EF-2747-4560-965E-D0FD903C4239}" Name="ListID"></asp:Parameter>
</UpdateParameters>
<InsertParameters>
<asp:Parameter DefaultValue="{4B3F56EF-2747-4560-965E-D0FD903C4239}" Name="ListID"></asp:Parameter>
</InsertParameters>
<DeleteParameters>
<asp:Parameter DefaultValue="{4B3F56EF-2747-4560-965E-D0FD903C4239}" Name="ListID"></asp:Parameter>
</DeleteParameters>
</SharePoint:SPDataSource><SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="&lt;View&gt;&lt;/View&gt;" UseInternalName="True"><SelectParameters>
<asp:Parameter DefaultValue="{F5562A17-D4ED-48D5-B319-EC0239D5DD56}" Name="ListID"></asp:Parameter>
</SelectParameters>
<UpdateParameters>
<asp:Parameter DefaultValue="{F5562A17-D4ED-48D5-B319-EC0239D5DD56}" Name="ListID"></asp:Parameter>
</UpdateParameters>
<InsertParameters>
<asp:Parameter DefaultValue="{F5562A17-D4ED-48D5-B319-EC0239D5DD56}" Name="ListID"></asp:Parameter>
</InsertParameters>
<DeleteParameters>
<asp:Parameter DefaultValue="{F5562A17-D4ED-48D5-B319-EC0239D5DD56}" Name="ListID"></asp:Parameter>
</DeleteParameters>
</SharePoint:SPDataSource>
</Sources><Aggregate><concat name="data source"><datasource name="PPP2_Project_Monitoring" id="0" Type="SPList"/><datasource name="Cycle_3_Project_Monitoring" id="1" Type="SPList"/><datasource name="PPP_Project_Monitoring" id="2" Type="SPList"/><datasource name="PPP3_Project_Monitoring" id="3" Type="SPList"/></concat></Aggregate>
</SharePoint:AggregateDataSource>
</DataSources>
<Xsl>






<xsl:stylesheet xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" xmlns:agg="http://schemas.microsoft.com/sharepoint/aggregatesource" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
    <xsl:output method="html" indent="no"/>
    <xsl:decimal-format NaN=""/>
    <xsl:param name="dvt_apos">'</xsl:param>
    <xsl:param name="Today">CurrentDate</xsl:param>
    <xsl:variable name="dvt_1_automode">0</xsl:variable>

    <xsl:template match="/" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" xmlns:agg="http://schemas.microsoft.com/sharepoint/aggregatesource" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:SharePoint="Microsoft.SharePoint.WebControls">
        <xsl:call-template name="dvt_1"/>
    </xsl:template>

    <xsl:template name="dvt_1">
        <xsl:variable name="dvt_StyleName">Table</xsl:variable>
        <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
        <xsl:variable name="RowLimit" select="4" />



        <table border="0" width="100%" cellpadding="2" cellspacing="0">
            <tr valign="top">
                <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
                    <th class="ms-vh" width="1%" nowrap="nowrap"></th>
                </xsl:if>
                <th class="ms-vh" nowrap="">Proj_Name_AD</th>
                <th class="ms-vh" nowrap="">Progress%</th>
                <th class="ms-vh" nowrap="">Planning_KPI</th>
                <th class="ms-vh" nowrap="">Scope_KPI</th>
                <th class="ms-vh" nowrap="">Budget_KPI</th>
                <th class="ms-vh" nowrap="">Progress_KPI</th>
                <th class="ms-vh" nowrap="">Staffing_KPI</th>
                <th class="ms-vh" nowrap="">Reporting_KPI</th>
                <th class="ms-vh" nowrap="">Logistics_KPI</th>
                <th class="ms-vh" nowrap="">Relationship_KPI</th>
                <th class="ms-vh" nowrap="">EvidOfImpact_KPI</th>
            </tr>
            <xsl:call-template name="dvt_1.body">
                <xsl:with-param name="Rows" select="$Rows"/>
                <xsl:with-param name="FirstRow" select="1" />
                <xsl:with-param name="LastRow" select="$RowLimit" />
            </xsl:call-template>
        </table>

    </xsl:template>
    <xsl:template name="dvt_1.body">
        <xsl:param name="Rows"/>
        <xsl:param name="FirstRow" />
        <xsl:param name="LastRow" />
        <xsl:for-each select="$Rows">
            <xsl:sort select="ddwrt:UrlDirName(string(@FileRef))" order="ascending"/><xsl:sort select="@DateColumn" order="descending"/>
            <xsl:variable name="dvt_KeepItemsTogether" select="false()" />
            <xsl:variable name="dvt_HideGroupDetail" select="false()" />
            <xsl:if test="(position() &gt;= $FirstRow and position() &lt;= $LastRow) or $dvt_KeepItemsTogether">
                            <xsl:if test="not($dvt_HideGroupDetail)" ddwrt:cf_ignore="1">
                                            <xsl:call-template name="dvt_1.rowview" />
                            </xsl:if>
            </xsl:if>
        </xsl:for-each>

    </xsl:template>
    <xsl:template name="dvt_1.rowview">
        <tr>
            <xsl:if test="position() mod 2 = 1">
                <xsl:attribute name="class">ms-alternating</xsl:attribute>
            </xsl:if>
            <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
                <td class="ms-vb" width="1%" nowrap="nowrap">
                    <span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view"></span>
                </td>
            </xsl:if>
            <td class="ms-vb">
                <xsl:value-of select="@Proj_Name_AD"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Progress_x0025_" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Planning_Indicator" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Scope_Indicator" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Budget_Indicator" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Progress_Indicator" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Project_indicators" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Reporting_Indicator" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Logistics_Indicator" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@Relationship_Indicator" disable-output-escaping="yes"/>
            </td>
            <td class="ms-vb">
                <xsl:value-of select="@EoI_Indicator" disable-output-escaping="yes"/>
            </td>
        </tr>
    </xsl:template></xsl:stylesheet>    </Xsl>
</WebPartPages:DataFormWebPart>

Thanks as discussed I'm in need of what goes in 'do stuff'? Does it mean I need to hide the contents of the list to display only one item or do I need a command that just says display that row? — Anonymous, Jun 16 '10 at 2:55
Take the <tr> and everything within it and put it in the "do stuff" spot. This will set it up so that you only emit one item per site, the first one where the NewSite value exists. — Marc D Anderson, Jun 17 '10 at 12:40

Marc D Anderson · Accepted Answer · 2010-06-16 02:35:06Z

We already talked about sorting by date descending and taking only the first item, but the added wrinkle here is that you have multiple lists. So if you want to get the latest item per list, you still will want to do the sorting, but sort by site, and then by date (descending). Then you can use the ddwrt:NameChanged function to determine, for each item, if it is the first item per site. Check my blog post here for a little more on ddwrt:NameChanged.

UPDATE: You can do the sort on the list's root URL rather than its name; this will be unique per list. So, for instance:

/sitename/Lists/ListA
/sitename/Lists/ListB

If the lists are in different sites, but have the same name, the root URL will still be different:

/sitename1/Lists/ListA
/sitename2/Lists/ListA

Use the ddwrt:UrlDirName function to get at the root URL based on the @FileRef column value.

<xsl:sort select="ddwrt:UrlDirName(string(@FileRef))" order="ascending"/>
<xsl:sort select="@DateColumn" order="descending"/>

For the ddwrt:NameChanged logic, you'd put something like this in the dvt_1.rowview template:

<xsl:variable name="NewSite" select="ddwrt:NameChanged(string(ddwrt:UrlDirName(string(@FileRef)), 0)"/>
<xsl:if test="string-length($NewSite) &gt; 0">
  ... do stuff ...
</xsl:if>

Thanks Mark so what happens if the title is the same for each project? ie. we are using April 2010 and then May 2010 for each list item... — Anonymous, May 4 '10 at 3:13
I would work on this in SharePpoint Designer, as it's much easier. That "XSL Editor" is just a big white box. — Marc D Anderson, May 6 '10 at 1:16
Thanks Marc whereabouts do I insert the code? I've tried doing it through directly entering in the code for the DVWP but it fails to display. I did a quick check through your blogs but may have missed it. — Anonymous, May 10 '10 at 23:46
The xsl:sort goes in the xsl:for-each, which is usually in dvt_1.body. Take a look at this post: mdasblog.wordpress.com/2010/02/16/… — Marc D Anderson, May 12 '10 at 12:24

Ed Dumas · Accepted Answer · 2011-10-18 20:47:13Z

I finally got this figured out. For some reason, whenever I tried to use ddwrt:UrlDirName it caused a problem when the HTML was produced for the browser. The URLs of the sub sites where not being passed correctly. It worked fine in SPD preview (design mode) but wouldn't display the same results in the browser. I ended changing the XSL sort expression and the definition of my NewSite variable. (I had to use the "substring-after" function to fix my FileDirRef string. They were all preceded by a number, a semicolon and a hash, e.g. 2;#siteA/Lists/StatusReports.) Studying Marc Anderson's eBook about DVWP XSL tags was very helpful. I highly recommend adding it to your reference library - you can buy it here.

<xsl:template name="dvt_1.body">
<xsl:param name="Rows"/>
<xsl:param name="FirstRow" />
<xsl:param name="LastRow" />
    <xsl:for-each select="$Rows">
        <xsl:sort select="substring-after(@FileDirRef, '#')" order="ascending" />
        <xsl:sort select="@WeekEnding" order="descending" />

and for the ddwrt:NameChanged function

<xsl:template name="dvt_1.rowview">     
    <xsl:variable name="NewSite" select="ddwrt:NameChanged(substring-after(@FileDirRef, '#'), 0)"/>
    <xsl:if test="string-length($NewSite) &gt; 0">
        <tr>

Stack Exchange Network

current community

your communities

more stack exchange communities

How do I create DVWP showing multiple lists with latest item from each list?

2 Answers 2

Your Answer

Hot Network Questions

How do I create DVWP showing multiple lists with latest item from each list?

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions