Friday, September 28, 2012

Merge two lists using a DataView web part

For a survey I created, I needed to merge some user information (e.g. department, Division, etc.) from another list into the survey results, and get those results to display nicely in Excel. A straight DataView data source join got me 80% of the way there, but it was far from ideal, throwing the results of the joined table into a single column at the end of the first table.  I wanted to go one step further and make the html  and the look and feel be ONE table.  So I did the following:

1.  Created a new page and added a DataView web part using SharePoint Designer. 

2.  Followed this article to join my data sources and add all of the fields I wanted:   http://office.microsoft.com/en-us/sharepoint-designer-help/display-data-from-multiple-sources-in-a-single-data-view-HA010099144.aspx 

3.  For each column, move the column titles that get repeated in the joined dataview.  These are held in the dvt_2, 3, 4, etc. templates. 

         <th class="ms-vh" nowrap="nowrap">Department</th>

Paste the columns into the dvt_1 template along with the first table headers: 

           <th class="ms-vh mpi">Department</th>

(I removed the nowrap css since they were survey questions and quite long)

4.  For each column you added, add another column in the dvt_1.rowview template:  
            <td class="ms-vb">
                <xsl:call-template name="dvt_3" />              
            </td>
            .....
           <td class="ms-vb">
                <xsl:call-template name="dvt_9" />                                                                   
            </td>

5.    Immediately after the dvt_2.empty template, copy all the dvt_2 templates and paste it, then change all of the _2 to _3.  Should look like:

<xsl:variable name="dvt_3_automode">0</xsl:variable>
    <xsl:template name="dvt_3">
        <xsl:variable name="dvt_StyleName">Table</xsl:variable>
        <xsl:variable name="dvt_ParentRow" select="current()" />
        <xsl:variable name="Rows" select="../../../Hiring_Manager_Survey/Rows/Row[@Author.title=$dvt_ParentRow/@Title]" />
        <xsl:variable name="dvt_RowCount" select="count($Rows)" />
        <xsl:variable name="dvt_IsEmpty" select="$dvt_RowCount = 0" />
        <xsl:choose>
            <xsl:when test="$dvt_IsEmpty">
                <xsl:call-template name="dvt_3.empty" />
            </xsl:when>
            <xsl:otherwise>
                <table border="0" width="100%" cellpadding="2" cellspacing="0">
                    <xsl:call-template name="dvt_3.body">
                        <xsl:with-param name="Rows" select="$Rows" />
                        <xsl:with-param name="dvt_ParentRow" select="$dvt_ParentRow" />
                    </xsl:call-template>
                </table>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>
    <xsl:template name="dvt_3.body">
        <xsl:param name="Rows" />
        <xsl:param name="dvt_ParentRow" />
        <xsl:for-each select="$Rows">
            <xsl:call-template name="dvt_3.rowview" />
        </xsl:for-each>
    </xsl:template>
    <xsl:template name="dvt_3.rowview">
        <tr>
            <xsl:attribute name="class">ms-alternating</xsl:attribute>

            <xsl:if test="$dvt_3_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 mpi">
                <xsl:value-of select="@On_x0020_a_x0020_scale_x0020_of_2" />
            </td>
           
        </tr></xsl:template>
    <xsl:template name="dvt_3.empty">
    </xsl:template>

6.  Change the <xsl:value-of select="@On_x0020_a_x0020_scale_x0020_of_2" /> to the Internal Name of the field that you want displayed.

7.  Repeat steps 5 and 6 for each column you added.

The end result will be a merged dataview showing the joined results from two separate lists, that users can view or right-click to easily export to Excel: