Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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>
share|improve this question
    
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
add comment

2 Answers

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>
share|improve this answer
    
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
    
Is the code above entered from the XSL Editor? –  Anonymous May 4 '10 at 22:45
    
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
show 2 more comments

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>
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.