Summing a column on a DVWP with 2 levels of grouping with incorrect subtotals

Question

I am working with SharePoint 2007, and have been charged with making a report based on external data (SQL Server). It is a high level look at list of tickets in particular statuses. I decided to go with a Data View Webpart and I am creating it in SharePoint Designer 2007.

The results have just 4 columns (Region, Warehouse, Status, Count(Column1) and the view is grouped by the first 2 columns. I included footers for both groupings, and changed their aggregate function to <xsl:value-of select="sum($nodeset/@Column1)" /> The subtotals are correct for the Region grouping, but are incorrect for some of the Warehouse groupings.

In trying to figure out where the numbers are coming from, it seems that the $nodeset for the Warehouse footer is not respecting the grouping of the Region. The only artifact is that all blank values of Warehouse are added together, and this subtotal is displayed for every footer. For all the other values, Warehouses only exist in a single Region, so this artifact is transparent. Is there a way to modify the nodeset definition to include BOTH groupings to alleviate this problem?

REGION           WAREHOUSE STATUS                COUNT
Corporate        BLANK     PENDING               1
                           SubTotal              210
                 Total     1                     
Midwest          BLANK     ENTERED               9
Midwest          BLANK     NEW                   189
                           Subtotal              210
Midwest          5CLB      APPROVED              2
Midwest          5CLB      CODED                 1
Midwest          5CLB      ENTERED               755
Midwest          5CLB      NEW                   3
Midwest          5CLB      PENDING               2
                           Subtotal              763
                 Total     961                   

the Totals are correct, but the subtotal of 210 is wrong (it's the total of all blank warehouses)

                    <xsl:call-template name="dvt_1.groupfooter1">
                        <xsl:with-param name="fieldtitle">Warehouse</xsl:with-param>
                        <xsl:with-param name="fieldname">LOCATION</xsl:with-param>
                        <xsl:with-param name="fieldvalue" select="$groupfooter1" />
                        <xsl:with-param name="fieldtype" select="'text'" />
                        <xsl:with-param name="nodeset" select="msxsl:node-set($dvt_Rows)/root//Row[((@LOCATION)=$groupfooter1 or ((not(@LOCATION) or @LOCATION='') and $groupfooter1=' '))]" />
                        <xsl:with-param name="groupid" select="'1'" />
                        <xsl:with-param name="displaystyle" select="'auto'" />
                        <xsl:with-param name="showfooter" select="true()" />
                        <xsl:with-param name="showfootercolumn" select="false()" />
                    </xsl:call-template>

Answer 1

I just hard coded (@REGION)=$groupfooter0 and into the $nodeset. I did this for every occurance. You may have to copy the xsl:variable in for groupfooter0 inside the xsl:when. I guess I don't understand why SPDesigner wouldn't do that by default.

<xsl:with-param name="nodeset" select="msxsl:node-set($dvt_Rows)/root//Row[((@REGION)=$groupfooter0 and ((@LOCATION)=$groupfooter1 or ((not(@LOCATION) or @LOCATION='') and $groupfooter1=' ')))]" />