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>