How can I display data from three joined SharePint Lists using a single Data Form Web Part

Sun, 16 Mar 2008 23:37:06 Z

Hi,

I would like to display three joined SharePoint Lists (i.e. namely Projects, ProjectPhases and Project_Reports) in one Data Form Web Part. Although I was able to dsplay data from the parent list (which is the Projects List), I am having trouble in displaying corresponding data from the child lists (i.e namely, ProjectPhases and Project_Reports lists).

The parent list, Projects, is associated to the child lists by it's Project Name (being known as @Title). The child lists have a lookup field called Project which uses Project Name fom the Projects list (SharePoint labels it as @Project).

I am using the following two lines f XPATH code to extract child lists data based on the parent lists association. It looks like here perhaps where I am struggling:

<xsl:variable name="dvt_ParentRow" select="current()" />
<xsl:variable name="FilteredProjectPhases" select="../../../ProjectPhases/Rows/Row[@Project=$dvt_ParentRow/@Title]" />
<xsl:variable name="FilteredProjectReports" select="../../../Project_Reports/Rows/Row[@Project=$dvt_ParentRow/@Title]" />

Here is the whole code that I have in my Data Form Web Part:

<WebPartPagesataFormWebPart runat="server" AllowConnect="True" PartImageSmall="" MissingAssembly="Cannot import this Web Part." IsIncludedFilter="" ExportMode="All" ID="g_6138da2a_7fe8_43ec_9bc8_8c2f27ea954b" AllowRemove="True" ListName="{A1FA9FF5-8383-49EA-9E74-C843A5A993B1}" FrameState="Normal" ConnectionID="00000000-0000-0000-0000-000000000000" DetailLink="" ExportControlledProperties="True" IsIncluded="True" IsVisible="True" AllowEdit="True" Dir="Default" SuppressWebPartChrome="False" ViewContentTypeId="" PageSize="-1" FrameType="Standard" DataSourceID="" UseSQLDataSourcePaging="True" AllowHide="True" AllowMinimize="True" ShowWithSampleData="False" ChromeType="TitleAndBorder" HelpMode="Modeless" ViewFlag="0" Title="Project Status Report" HelpLink="" Description="" PartOrder="3" AllowZoneChange="True" PartImageLarge="" NoDefaultStyle="TRUE" __MarkupType="vsattributemarkup" __WebPartId="{6138DA2A-7FE8-43EC-9BC8-8C2F27EA954B}" __AllowXSLTEditing="true" WebPart="true" Height="" Width="">
<DataSources>
<SharePoint:AggregateDataSource runat="server" RowsName="" RootName="" SeparateRoot="true" IsSynchronous="" ID="Projects_Phases_Reports1"><Sources><SharePointPDataSource runat="server" SelectCommand="<View></View>" DataSourceMode="List" UseInternalName="True" InsertCommand="" DeleteCommand="" UpdateCommand="">
<SelectParameters>
 <asparameter DefaultValue="{A1FA9FF5-8383-49EA-9E74-C843A5A993B1}" Name="ListID"></asparameter>
</SelectParameters>
</SharePointPDataSource><SharePointPDataSource runat="server" SelectCommand="<View></View>" DataSourceMode="List" UseInternalName="True" InsertCommand="" DeleteCommand="" UpdateCommand="">
<SelectParameters>
 <asparameter DefaultValue="{E7475672-1A30-4B46-98A9-31C4E7144A15}" Name="ListID"></asparameter>
</SelectParameters>
</SharePointPDataSource><SharePointPDataSource runat="server" SelectCommand="<View></View>" DataSourceMode="List" UseInternalName="True" InsertCommand="" DeleteCommand="" UpdateCommand="">
<SelectParameters>
 <asparameter DefaultValue="{7EDA1734-D360-480E-9FED-988E74DAAFEA}" Name="ListID"></asparameter>
</SelectParameters>
</SharePointPDataSource>

</Sources>
<Aggregate>
<concat name="data source">
 <datasource name="Projects" id="0" Type="SPList"/>
 <datasource name="ProjectPhases" id="1" Type="SPList"/>
 <datasource name="Project_Reports" id="2" Type="SPList"/>
</concat></Aggregate>
</SharePoint:AggregateDataSource>
</DataSources>

<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,Project Name;@Project_x0020_Number,Project Number;@Project_x0020_Description,Project Description;@Start_x0020_Date,Actual Project Start Date;@End_x0020_Date,Planned Project Finish Date;@Project_x0020_Finish_x0020_Date_,Actual Project Finish Date;@Project_x0020_Manager,Project Manager;@Manager_x0020_Phone,Manager Phone;@Project_x0020_Sponsor,Project Sponsor;@Sponsor_x0020_Phone,Sponsor Phone;@Accountability,Domain;@Department,Department;@Status,Project Status;@Budget,Project Budget;@Budgeted_x0020_Days,Scheduled days;@Is_x0020_On_x0020_Operational_x0,Is On Operational Pan;@ID,ID;@ContentType,Content Type;@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;@Phase_x0020_Name,Phase Name;@Project,Project;@Planned_x0020_Start_x0020_Date,Planned Start Date;@Actual_x0020_Start_x0020_Date,Actual Start Date;@Planned_x0020_Finish_x0020_Date,Planned Finish Date;@Actual_x0020_Finish_x0020_Date,Actual Finish Date;@For_x0020_the_x0020_Period_x0020,For the Period Ending;@Current_x0020_Project_x0020_Stat,Current Project Status;@Project_x0020_Status,Detail Project Status;@Budget_x0020_Status,Current Budget Status;@Schedule,Current Schedule Status;@External_x0020_Dependencies,External Dependencies;@Display_x0020_Latest,Publish Status Report;</DataFields>
<Xsl>
<xsltylesheet xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlnsdwrt="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="urnchemas-microsoft-com:xslt" xmlnsharePoint="Microsoft.SharePoint.WebControls" xmlnsdwrt2="urn:frontpage:internal">
<xslutput method="html" indent="no"/>
<xslecimal-format NaN=""/>
<xslaram name="dvt_apos">'</xslaram>
<xsl:variable name="dvt_1_automode">0</xsl:variable>
<xsl:template match="/">
 <xsl:call-template name="dvt_1"/>
</xsl:template>
<xsl:template name="dvt_1">
 <xslaram name="ParentPath"/>
 <xsl:variable name="dvt_StyleName">Table</xsl:variable>
 <xsl:variable name="Rows" select="/dsQueryResponse/Projects/Rows/Row"/>

 <table border="0" width="100%" cellpadding="2" cellspacing="0">
 <xsl:call-template name="dvt_1.body">
 <xsl:with-param name="ParentPath" select="$ParentPath"/>
 <xsl:with-param name="Rows" select="$Rows"/>
 </xsl:call-template>
 </table>
</xsl:template>
<xsl:template name="dvt_1.body">
 <xslaram name="Rows"/>
 <xslaram name="ParentPath"/>
 <xsl:for-each select="$Rows">
 <xsl:call-template name="dvt_1.rowview">
 <xsl:with-param name="ParentPath" select="$ParentPath"/>
 </xsl:call-template>
 </xsl:for-each>
</xsl:template>

<xsl:template name="dvt_1.rowview">
 <xslaram name="ParentPath"/>
 <xsl:variable name="dvt_ParentRow" select="current()" />
 <xsl:variable name="FilteredProjectPhases" select="../../../ProjectPhases/Rows/Row[@Project=$dvt_ParentRow/@Title]" />
 <xsl:variable name="FilteredProjectReports" select="../../../Project_Reports/Rows/Row[@Project=$dvt_ParentRow/@Title]" />
 <tr>
 <td>
 <table border="0" cellspacing="0" width="100%">
 <tr>
 <td width="25%" class="ms-vb">
 Project Name:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Title" />
 </td>
 </tr>

 <tr>
 <td width="25%" class="ms-vb">
 Project Description:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Project_x0020_Description" disable-output-escaping="yes" />
 </td>
 </tr>

 <tr>
 <td width="25%" class="ms-vb">
 Domain:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Accountability" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 Is On Operational Plan:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Is_x0020_On_x0020_Operational_x0" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 Project Sponsor:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Project_x0020_Sponsor" disable-output-escaping="yes" />
 </td>
 </tr><tr>
 <td width="25%" class="ms-vb">
 Sponsor Phone:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Sponsor_x0020_Phone" />
 </td>
 </tr>

<tr>
 <td width="25%" class="ms-vb">
 Project Manager:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Project_x0020_Manager" disable-output-escaping="yes" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 Manager Phone:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="@Manager_x0020_Phone" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 Phase Name:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="$FilteredProjectPhases[@Phase_x0020_Name]" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 For the Period Ending:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="$FilteredProjectReports[@For_x0020_the_x0020_Period_x0020]" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 Project Status Summary:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="$FilteredProjectReports[@Current_x0020_Project_x0020_Stat]" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 Budget Status:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="$FilteredProjectReports[@Budget_x0020_Status]" />
 </td>
 </tr>
 <tr>
 <td width="25%" class="ms-vb">
 Schedule Status:
 </td>
 <td width="75%" class="ms-vb">
 <xsl:value-of select="$FilteredProjectReports[@Schedule]" />
 </td>
 </tr>

 <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
 <tr>
 <td colspan="99" class="ms-vb">
 
 </td>
 </tr>
 </xsl:if>
 </table>
 </td>
 </tr>
</xsl:template>
</xsltylesheet>
</Xsl>
</WebPartPagesataFormWebPart>

How can I display data from three joined SharePint Lists using a single Data Form Web Part

Fri, 04 Apr 2008 00:16:21 Z

With a little bit of manipultaion of the XSLT script, which SPD 2007 spits, and by using this example http://office.microsoft.com/en-us/sharepointdesigner/HA100991441033.aspx?pid=CH100667641033 I was able to figure it out.

Thanks