How to Create a Filtered DataView on a Joined Data Source in SharePoint Designer 2007

I am trying to create a filtered DataView for MOSS 2007 using SharePoint Designer 2007. The following simplified example demonstrates what I am trying to accomplish.

Suppose I have two lists: Colors and People. The list of people has a lookup column called Favorite Co开发者_JAVA技巧lor that allows the user to select from the list of colors.

I want to create a DataView that will only display the colors that nobody has selected as their favorite color.

I have successfully created a DataView that shows each color and the number of people who have chosen that color by inserting the following XSL code:

<xsl:value-of select="count(/dsQueryResponse/People/Rows/Row[@Favorite_x0020_Color = current()/@Title])" />

I tried to apply to same logic to the DataView query as shown below, but this still returned all the rows:

<xsl:variable name="Rows" select="/dsQueryResponse/Colors/Rows/Row[count(/dsQueryResponse/People/Rows/Row[@Favorite_x0020_Color = current()/@Title]) = 0]"/>

I think the problem with the above is that at the time the query takes place, the current() function doesn't work because there isn't a current row. So I also tried using a complete reference, which yielded the same results:

<xsl:variable name="Rows" select="/dsQueryResponse/Colors/Rows/Row[count(/dsQueryResponse/People/Rows/Row[@Favorite_x0020_Color = dsQueryResponse/Colors/Rows/Row/@Title]) = 0]"/>

Is it possible to do the type of query I want using XSL? If so, where have I gone wrong? The complete XSL is given below:

<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" 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:variable name="dvt_1_automode">0</xsl:variable>
<xsl:template match="/">
    <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/Colors/Rows/Row[count(/dsQueryResponse/People/Rows/Row[@Favorite_x0020_Color = dsQueryResponse/Colors/Rows/Row/@Title]) = 0]"/>
    <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="nowrap">Color</th>
        <th class="ms-vh" nowrap="nowrap">Number of People</th>
    </tr>
    <xsl:call-template name="dvt_1.body">
        <xsl:with-param name="Rows" select="$Rows"/>
    </xsl:call-template>
    </table>
</xsl:template>
<xsl:template name="dvt_1.body">
    <xsl:param name="Rows"/>
    <xsl:for-each select="$Rows">
        <xsl:call-template name="dvt_1.rowview"/>
    </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="@Title"/>
        </td>
        <td class="ms-vb">
            <xsl:value-of select="count(/dsQueryResponse/People/Rows/Row[@Favorite_x0020_Color = current()/@Title])" />
        </td>
    </tr>
</xsl:template>

I haven't actually tried this but what if you do basically what Andrew suggests, looping through the Colors list first. Just don't display the row if the criteria isn't met.

   <xsl:variable name="Rows" select="/dsQueryResponse/Colors/Rows/Row"/>

and then

<xsl:template name="dvt_1.rowview">
<xsl:variable name="title" select="@Title" />
<xsl:if test="count(/dsQueryResponse/People/Rows/Row[@Favorite_x0020_Color = $title]) &gt; 0">
<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="@Title"/>
    </td>
    <td class="ms-vb">
        <xsl:value-of select="count(/dsQueryResponse/People/Rows/Row[@Favorite_x0020_Color = $title])" />
    </td>
</tr>
</xsl:if>
</xsl:template>