I'm having difficulty creating an xsl transformation. My three source documents are similar to:
<dsQueryResponse>
<Workgroup>
<Items>
<Item WorkgroupID="4001" WorkgroupCenter="Center1"/>
<Item WorkgroupID="4002" WorkgroupCenter="Center1"/>
<Item WorkgroupID="4003" WorkgroupCenter="Center2"/>
</Items>
</Workgroup>
<Staff>
<Items>
<Item StaffName="Anne Jones" StaffCenter="Center1" StaffID="AJ1" />
<Item StaffName="Bill Smith" StaffCenter="Center1" StaffID="BS1" />
<Item StaffName="Charles Glover" StaffCenter="Center2" StaffID="CG1" />
<Item StaffName="Donald Hill" StaffCenter="Center2" StaffID="DH1" />
<Item StaffName="Evan Dolan" StaffCenter="Center3" StaffID="ED1" />
<Item StaffName="Frank Miller" StaffCenter="Center3" StaffID="FM1" />
</Items>
</Staff>
<Membership>
<Items>
<Item StaffID="AJ1" WorkgroupID="4001" />
<Item StaffID="AJ1" WorkgroupID="4001" />
<Item StaffID="AJ1" WorkgroupID="4003" />
<Item StaffID="CG1" WorkgroupID="4001" />
<Item StaffID="CG1" WorkgroupID="4003" />
<Item StaffID="DH1" WorkgroupID="4002" />
<Item StaffID="ED1" WorkgroupID="4003" />
</Items>
</Membership>
</dsQueryResponse>
My desired output is
Center | Unique Staff | Count (Workgroups)
------------------------------
Center1 | 1 | 2
Center2 | 2 | 1
Center3 | 1 | 0
The third column is simply a count of items in the Workgroup document by the "WorkgroupCenter" attribute - this is not giving me any trouble.
The first column is obviously the Center.
The second column is the count of unique members of each Center (indicated by attribute "StaffCenter" in the "Staff" Items) excluding any Staff Items that do not have a corresponding entry in the "Membership" Items (StaffID). This means that for this column, the WorkgroupCenter attribute is ignored.
I am limited to XSLT 1.0.
Edited to add what I have attempted so far. I'm stuck at trying to aggregate the counts as noted by my comment in the code. I should note the environment is a SharePoint 2010 Data Form Web Part. My example was simplified, so I've edited my existing code to match:
<xsl:stylesheet 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:param name="ManualRefresh"></xsl:param>
<xsl:param name="dvt_firstrow">1</xsl:param>
<xsl:param name="dvt_nextpagedata" />
<xsl:variable name="dvt_1_automode">0</xsl:variable>
<xsl:key name="staffCenter" match="/dsQueryResponse/Staff/Items/Item" use="@StaffCenter"/>
<xsl:key name="workgroupCenter" match="/dsQueryResponse/Workgroup/Items/Item" use="@WorkgroupCenter"/>
<xsl:template match="/" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:SharePoint="Microsoft.SharePoint.WebControls">
<xsl:variable name="centers" select="/dsQueryResponse/Staff/Items/Item[count (. | key('staffCenter',@StaffCenter)[1]) = 1]" />
<xsl:variable name="workgroupCenters" select="/dsQueryResponse/Workgroup/Items/Item[count (. | key('workgroupCenter',@WorkgroupCenter)[1]) = 1]" />
<table>
<tr>
<th>Center</th>
<th>Unique Representative for Activities</th>
<th>Active Workgroups</th>
</tr>
<xsl:for-each select="$centers">
<tr>
<td>
<xsl:value-of select="@StaffCenter" />
</td>
<td>
<xsl:variable name="CurrentCenterNodes" select="key('staffCenter',@StaffCenter)" />
<!-- This gives me count of the number of instances of a particular staff member.
What I want is a count of the number of staff members where their total is greater than 0 -->
<xsl:for-each select="$CurrentCenterNodes">
<xsl:value-of select="@StaffID"/> -
<xsl:value-of select="count(/dsQueryResponse/Membership/Items/Item[@Title=current()/@StaffID])"/>
<br/>
</xsl:for-each>
</td>
<td>
<xsl:variable name="WorkgroupCenterLeadNodes" select="key('workgroupCenter',@StaffCenter)" />
<xsl:value-of select="count($WorkgroupCenterLeadNodes)" />
</td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>
Additionally, the Membership document is necessary because if a Staff Member does not exist in it then they should not be counted.
In the interest of moving this forward, consider the following stylesheet:
XSLT 1.0
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:key name="staff-by-center" match="Staff/Items/Item" use="@StaffCenter"/>
<xsl:key name="workgroup-by-center" match="Workgroup/Items/Item" use="@WorkgroupCenter"/>
<xsl:key name="membership-by-workgroup" match="Membership/Items/Item" use="@WorkgroupID"/>
<xsl:key name="staff-by-id" match="Staff/Items/Item" use="@StaffID"/>
<xsl:template match="/dsQueryResponse">
<root>
<!-- for each distinct center -->
<xsl:for-each select="Staff/Items/Item[count(.|key('staff-by-center', @StaffCenter)[1]) = 1]">
<xsl:variable name="center" select="@StaffCenter" />
<!-- workgroups associated with the current center -->
<xsl:variable name="workgroups" select="key('workgroup-by-center', $center)" />
<!-- memberships associated with the workgroups -->
<xsl:variable name="memberships" select="key('membership-by-workgroup', $workgroups/@WorkgroupID)" />
<!-- distinct staff listed in memberships -->
<xsl:variable name="staff" select="key('staff-by-id', $memberships/@StaffID)" />
<center>
<name>
<xsl:value-of select="$center" />
</name>
<workgroups>
<xsl:copy-of select="$workgroups" />
</workgroups>
<memberships>
<xsl:copy-of select="$memberships" />
</memberships>
<all-staff>
<xsl:copy-of select="$staff" />
</all-staff>
<center-staff>
<xsl:copy-of select="$staff[@StaffCenter=$center]" />
</center-staff>
</center>
</xsl:for-each>
</root>
</xsl:template>
</xsl:stylesheet>
I have used an XML result and copied the relevant nodes instead of just counting them, so that we can see exactly what each step does. Applied to your example input, the result will be:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<center>
<name>Center1</name>
<workgroups>
<Item WorkgroupID="4001" WorkgroupCenter="Center1"/>
<Item WorkgroupID="4002" WorkgroupCenter="Center1"/>
</workgroups>
<memberships>
<Item StaffID="AJ1" WorkgroupID="4001"/>
<Item StaffID="AJ1" WorkgroupID="4001"/>
<Item StaffID="CG1" WorkgroupID="4001"/>
<Item StaffID="DH1" WorkgroupID="4002"/>
</memberships>
<all-staff>
<Item StaffName="Anne Jones" StaffCenter="Center1" StaffID="AJ1"/>
<Item StaffName="Charles Glover" StaffCenter="Center2" StaffID="CG1"/>
<Item StaffName="Donald Hill" StaffCenter="Center2" StaffID="DH1"/>
</all-staff>
<center-staff>
<Item StaffName="Anne Jones" StaffCenter="Center1" StaffID="AJ1"/>
</center-staff>
</center>
<center>
<name>Center2</name>
<workgroups>
<Item WorkgroupID="4003" WorkgroupCenter="Center2"/>
</workgroups>
<memberships>
<Item StaffID="AJ1" WorkgroupID="4003"/>
<Item StaffID="CG1" WorkgroupID="4003"/>
<Item StaffID="ED1" WorkgroupID="4003"/>
</memberships>
<all-staff>
<Item StaffName="Anne Jones" StaffCenter="Center1" StaffID="AJ1"/>
<Item StaffName="Charles Glover" StaffCenter="Center2" StaffID="CG1"/>
<Item StaffName="Evan Dolan" StaffCenter="Center3" StaffID="ED1"/>
</all-staff>
<center-staff>
<Item StaffName="Charles Glover" StaffCenter="Center2" StaffID="CG1"/>
</center-staff>
</center>
<center>
<name>Center3</name>
<workgroups/>
<memberships/>
<all-staff/>
<center-staff/>
</center>
</root>
Now, as you can see, these results do not match your expected output - for example, no group of nodes under Center2 has a count of 2. So either I am grouping the wrong nodes, or your expected counts are off. If the former, please edit your question and elaborate on the logic that needs to be applied (how would one arrive at the expected result manually).
Added:
The second column is the count of unique members of each Center
(indicated by attribute "StaffCenter" in the "Staff" Items) excluding
any Staff Items that do not have a corresponding entry in the
"Membership" Items (StaffID).
Okay then this should be relatively easy:
XSLT 1.0
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:key name="staff-by-center" match="Staff/Items/Item" use="@StaffCenter"/>
<xsl:key name="memberhip-by-staff" match="Membership/Items/Item" use="@StaffID"/>
<xsl:template match="/dsQueryResponse">
<root>
<!-- for each distinct center -->
<xsl:for-each select="Staff/Items/Item[count(.|key('staff-by-center', @StaffCenter)[1]) = 1]">
<xsl:variable name="center" select="@StaffCenter" />
<!-- staff at current center -->
<xsl:variable name="all-staff" select="key('staff-by-center', $center)" />
<!-- exclude staff with no memberships -->
<xsl:variable name="staff" select="$all-staff[key('memberhip-by-staff', @StaffID)]" />
<center>
<name>
<xsl:value-of select="$center" />
</name>
<all-staff>
<xsl:copy-of select="$all-staff" />
</all-staff>
<staff>
<xsl:copy-of select="$staff" />
</staff>
</center>
</xsl:for-each>
</root>
</xsl:template>
</xsl:stylesheet>
Result
<?xml version="1.0" encoding="UTF-8"?>
<root>
<center>
<name>Center1</name>
<all-staff>
<Item StaffName="Anne Jones" StaffCenter="Center1" StaffID="AJ1"/>
<Item StaffName="Bill Smith" StaffCenter="Center1" StaffID="BS1"/>
</all-staff>
<staff>
<Item StaffName="Anne Jones" StaffCenter="Center1" StaffID="AJ1"/>
</staff>
</center>
<center>
<name>Center2</name>
<all-staff>
<Item StaffName="Charles Glover" StaffCenter="Center2" StaffID="CG1"/>
<Item StaffName="Donald Hill" StaffCenter="Center2" StaffID="DH1"/>
</all-staff>
<staff>
<Item StaffName="Charles Glover" StaffCenter="Center2" StaffID="CG1"/>
<Item StaffName="Donald Hill" StaffCenter="Center2" StaffID="DH1"/>
</staff>
</center>
<center>
<name>Center3</name>
<all-staff>
<Item StaffName="Evan Dolan" StaffCenter="Center3" StaffID="ED1"/>
<Item StaffName="Frank Miller" StaffCenter="Center3" StaffID="FM1"/>
</all-staff>
<staff>
<Item StaffName="Evan Dolan" StaffCenter="Center3" StaffID="ED1"/>
</staff>
</center>
</root>
