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

Question

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="&lt;View&gt;&lt;/View&gt;" DataSourceMode="List" UseInternalName="True" InsertCommand="" DeleteCommand="" UpdateCommand="">
 <SelectParameters>
  <asparameter DefaultValue="{A1FA9FF5-8383-49EA-9E74-C843A5A993B1}" Name="ListID"></asparameter>
 </SelectParameters>
</SharePointPDataSource><SharePointPDataSource runat="server" SelectCommand="&lt;View&gt;&lt;/View&gt;" DataSourceMode="List" UseInternalName="True" InsertCommand="" DeleteCommand="" UpdateCommand="">
 <SelectParameters>
  <asparameter DefaultValue="{E7475672-1A30-4B46-98A9-31C4E7144A15}" Name="ListID"></asparameter>
 </SelectParameters>
</SharePointPDataSource><SharePointPDataSource runat="server" SelectCommand="&lt;View&gt;&lt;/View&gt;" 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">
       <b>Project Name:</b>
      </td>
      <td width="75%" class="ms-vb">
       <xsl:value-of select="@Title" />
      </td>
     </tr>
     
     <tr>
      <td width="25%" class="ms-vb">
       <b>Project Description:</b>
      </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">
       <b>Domain:</b>
      </td>
      <td width="75%" class="ms-vb">
       <xsl:value-of select="@Accountability" />
      </td>
     </tr>
     <tr>
      <td width="25%" class="ms-vb">
       <b>Is On Operational Plan:</b>
      </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">
       <b>Project Sponsor:</b>
      </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">
       <b>Sponsor Phone:</b>
      </td>
      <td width="75%" class="ms-vb">
       <xsl:value-of select="@Sponsor_x0020_Phone" />
      </td>
     </tr>

     <tr>
      <td width="25%" class="ms-vb">
       <b>Project Manager:</b>
      </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">
       <b>Manager Phone:</b>
      </td>
      <td width="75%" class="ms-vb">
       <xsl:value-of select="@Manager_x0020_Phone" />
      </td>
     </tr>
     <tr>
      <td width="25%" class="ms-vb">
       <b>Phase Name:</b>
      </td>
      <td width="75%" class="ms-vb">
       <xsl:value-of select="$FilteredProjectPhases[@Phase_x0020_Name]" />
      </td>
     </tr>
     <tr>
      <td width="25%" class="ms-vb">
       <b>For the Period Ending:</b>
      </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">
       <b>Project Status Summary:</b>
      </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">
       <b>Budget Status:</b>
      </td>
      <td width="75%" class="ms-vb">
       <xsl:value-of select="$FilteredProjectReports[@Budget_x0020_Status]" />
      </td>
     </tr>
     <tr>
      <td width="25%" class="ms-vb">
       <b>Schedule Status:</b>
      </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">
        <span ddwrt:amkeyfield="ID" ddwrt:amkeyvalue="ddwrt:EscapeDelims(string(@ID))" ddwrt:ammode="view" />
       </td>
      </tr>
     </xsl:if>
    </table>
   </td>
  </tr>
 </xsl:template>
</xsltylesheet> 
</Xsl>
</WebPartPagesataFormWebPart>

Answer 1

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