Problem with Grouping & Totals in XSL 2

[dachaney]

I have a Sharepoint list of help calls and need to produce produce some stats for the the current and previous months calls

The calls have the following basic information (I have shown relevant fields only for simplicity)

<CALLS>

<Help Call>
 <ID>1</ID>
 <Category>Hardware</Category>
 <Created Date>2007-12-31</Created Date>
 <Closed Date>2007-12-31</Closed Date>
 <Status>Completed</Closed>
</Help Call>

<Help Call>
 <ID>2</ID>
 <Category>Hardware</Category>
 <Created Date>2008-01-01</Created Date>
 <Closed Date></Closed Date>
 <Status>Open</Closed>
</Help Call>

<Help Call>
 <ID>3</ID>
 <Category>Software</Category>
 <Created Date>2007-12-31</Created Date>
 <Closed Date>2008-01-02</Closed Date>
 <Status>Completed</Closed>
</Help Call>

<Help Call>
 <ID>4</ID>
 <Category>Software</Category>
 <Created Date>2008-01-01</Created Date>
 <Closed Date>2008-01-02</Closed Date>
 <Status>Completed</Closed>
</Help Call>

</CALLS>

I would like to show this in the following format

Category    Created LM   Closed LM   Created TM  Closed TM   Open

Hardware    1            1           1           0           1
Software    1            0           1           2           0

Total       2            1           2           2           1

where
Created LM = Calls per category created last calendar month
Closed LM = Calls per category closed last calendar month
Created TM = Calls per category created this calendar month
Closed TM = Calls per category closed this calendar month
Open = number of calls per category still open (regardless of age)

I have created an XSL Dataview (in Sharepoint Designer) which shows the table in the right format grouped by category, but I am having trouble getting the view to create the values?

Ignoring the fact I am using sharepoint, how can this be achieved with XSL ?

Many Thanks in advance

 

[jel]

Doing calculations on dates is not really funny in XSLT: for example: there is no function that gives you the current date. In this example I just use fixed string-values.
Counting is not to difficult.
I had to correct your xml to make this example:

<CALLS>

<HelpCall>
 <ID>1</ID>
 <Category>Hardware</Category>
 <CreatedDate>2007-12-31</CreatedDate>
 <ClosedDate>2007-12-31</ClosedDate>
 <Status>Completed</Status>
</HelpCall>

<HelpCall>
 <ID>2</ID>
 <Category>Hardware</Category>
 <CreatedDate>2008-01-01</CreatedDate>
 <ClosedDate></ClosedDate>
 <Status>Open</Status>
</HelpCall>

<HelpCall>
 <ID>3</ID>
 <Category>Software</Category>
 <CreatedDate>2007-12-31</CreatedDate>
 <ClosedDate>2008-01-02</ClosedDate>
 <Status>Completed</Status>
</HelpCall>

<HelpCall>
 <ID>4</ID>
 <Category>Software</Category>
 <CreatedDate>2008-01-01</CreatedDate>
 <ClosedDate>2008-01-02</ClosedDate>
 <Status>Completed</Status>
</HelpCall>

</CALLS>

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
<xsl:key name="calls_by_cat" match="HelpCall" use="Category"/> 

<xsl:variable name="this_month" select="'2008-01'"/>
<xsl:variable name="last_month" select="'2007-12'"/>

<xsl:template match="/">
 <table border="1">
  <tr>
   <td>Category</td>
   <td>Created LM</td>
   <td>Closed LM</td>
   <td>Created TM</td>
   <td>Closed TM</td>
   <td>Open</td>
  </tr>
  <xsl:for-each select="CALLS/HelpCall[count(. | key('calls_by_cat', Category)[1]) = 1]">
   <tr>
    <td><xsl:value-of select="Category" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(CreatedDate,1,7)=$last_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(ClosedDate,1,7)=$last_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(CreatedDate,1,7)=$this_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(ClosedDate,1,7)=$this_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[Status='Open'])" /></td>
   </tr>      
  </xsl:for-each>	
  <tr>
   <td>Total</td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(CreatedDate,1,7)=$last_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(ClosedDate,1,7)=$last_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(CreatedDate,1,7)=$this_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(ClosedDate,1,7)=$this_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[Status='Open'])" /></td>
  </tr> 
 </table>
</xsl:template>
	
</xsl:stylesheet>

 

[jel]

And just because my pc stands in the only room where I'm allowed to smoke, here's one that also calculates the current month as the last month in the xml.
Notice that it is no real calculation, it's more like string-manupulation.

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
<xsl:key name="calls_by_cat" match="HelpCall" use="Category"/> 

<xsl:template match="/">

 <xsl:variable name="sorted_months">
  <xsl:for-each select="CALLS/HelpCall/ClosedDate | CALLS/HelpCall/CreatedDate">
   <xsl:sort select="." order="descending"/>
   <xsl:value-of select="."/>
  </xsl:for-each>
 </xsl:variable>
 
 <xsl:variable name="this_month" select="substring($sorted_months,1,7)"/>

 <xsl:variable name="last_month">
  <xsl:choose>
   <xsl:when test="substring($this_month,6,2)='01'">
    <xsl:value-of select="concat(number(substring($this_month,1,4))-1, '-12')"/>
   </xsl:when>
   <xsl:when test="number(substring($this_month,6,2)) &gt; 11">
    <xsl:value-of select="concat(substring($this_month,1,5), number(substring($this_month,6,2))-1)"/>
   </xsl:when>
   <xsl:otherwise>
    <xsl:value-of select="concat(substring($this_month,1,5), '0', number(substring($this_month,6,2))-1)"/>
   </xsl:otherwise>
  </xsl:choose>
 </xsl:variable>

 <table border="1">
  <tr>
   <td>Category</td>
   <td>Created <xsl:value-of select="$last_month"/></td>
   <td>Closed <xsl:value-of select="$last_month"/></td>
   <td>Created <xsl:value-of select="$this_month"/></td>
   <td>Closed <xsl:value-of select="$this_month"/></td>
   <td>Open</td>
  </tr>
  <xsl:for-each select="CALLS/HelpCall[count(. | key('calls_by_cat', Category)[1]) = 1]">
   <tr>
    <td><xsl:value-of select="Category" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(CreatedDate,1,7)=$last_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(ClosedDate,1,7)=$last_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(CreatedDate,1,7)=$this_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(ClosedDate,1,7)=$this_month])" /></td>
    <td><xsl:value-of select="count(key('calls_by_cat', Category)[Status='Open'])" /></td>
   </tr>      
  </xsl:for-each>	
  <tr>
   <td>Total</td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(CreatedDate,1,7)=$last_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(ClosedDate,1,7)=$last_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(CreatedDate,1,7)=$this_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[substring(ClosedDate,1,7)=$this_month])" /></td>
   <td><xsl:value-of select="count(CALLS/HelpCall[Status='Open'])" /></td>
  </tr> 
 </table>
</xsl:template>
	
</xsl:stylesheet>

Well, I finished my cigarette,
enjoy.

 

[tsuji]

[1] The solution(s) posted by jel are nicely constructed. Only one thing: the hard data of "this month" and "last month". This is particularly significant when the data in the records are lacunary.

[2] For instance, conceiving a situation where 2008-01 contains "created" entries and then only 2007-11 contains "created"entries again. At the same time 2008-01 contains "closed" entries and then 2007-12 contains "closed"entries again. From the "created" point of view, there is a missing month "2007-12", nothing being created there but jobs were being closed all the same.

[3] This following development makes this flexible: listing last two months with entries for "created" and "closed" which may not be the same set of months.
[tt]
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

<xslutput method="html" indent="yes" encoding="utf-8" />
<xsl:key name="calls_by_cat" match="HelpCall" use="Category"/>
[blue]
<xsl:key name="calls_created_ccyy-mm" match="HelpCall" use="substring(CreatedDate,1,7)" />
<xsl:variable name="unique_created_ccyy-mm" select="//HelpCall[count(.|key('calls_created_ccyy-mm',substring(CreatedDate,1,7))[1])=1]" />
<xsl:variable name="created_this_month">
<xsl:for-each select="$unique_created_ccyy-mm">
<xsl:sort select="substring(CreatedDate,1,7)" order="descending" />
<xsl:if test="position()=1">
<xsl:value-of select="substring(CreatedDate,1,7)" />
</xsl:if>
</xsl:for-each>
</xsl:variable>

<xsl:variable name="created_last_month">
<xsl:for-each select="$unique_created_ccyy-mm">
<xsl:sort select="substring(CreatedDate,1,7)" order="descending" />
<xsl:if test="position()=2">
<xsl:value-of select="substring(CreatedDate,1,7)" />
</xsl:if>
</xsl:for-each>
</xsl:variable>

<xsl:key name="calls_closed_ccyy-mm" match="HelpCall" use="substring(ClosedDate,1,7)" />
<xsl:variable name="unique_closed_ccyy-mm" select="//HelpCall[count(.|key('calls_closed_ccyy-mm',substring(ClosedDate,1,7))[1])=1]" />
<xsl:variable name="closed_this_month">
<xsl:for-each select="$unique_closed_ccyy-mm">
<xsl:sort select="substring(ClosedDate,1,7)" order="descending" />
<xsl:if test="position()=1">
<xsl:value-of select="substring(ClosedDate,1,7)" />
</xsl:if>
</xsl:for-each>
</xsl:variable>

<xsl:variable name="closed_last_month">
<xsl:for-each select="$unique_closed_ccyy-mm">
<xsl:sort select="substring(ClosedDate,1,7)" order="descending" />
<xsl:if test="position()=2">
<xsl:value-of select="substring(ClosedDate,1,7)" />
</xsl:if>
</xsl:for-each>
</xsl:variable>
[/blue]
<xsl:template match="/">
<html><body>
<table border="1">
<tr>
<td>Category</td>
<td><xsl:value-of select="concat('Created ',$created_last_month)" /></td>
<td><xsl:value-of select="concat('Closed ',$closed_last_month)" /></td>
<td><xsl:value-of select="concat('Created ',$created_this_month)" /></td>
<td><xsl:value-of select="concat('Closed ',$closed_this_month)" /></td>
<td>Open</td>
</tr>
<xsl:for-each select="CALLS/HelpCall[count(. | key('calls_by_cat', Category)[1]) = 1]">
<tr>
<td><xsl:value-of select="Category" /></td>
<td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(CreatedDate,1,7)=$created_last_month])" /></td>
<td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(ClosedDate,1,7)=$closed_last_month])" /></td>
<td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(CreatedDate,1,7)=$created_this_month])" /></td>
<td><xsl:value-of select="count(key('calls_by_cat', Category)[substring(ClosedDate,1,7)=$closed_this_month])" /></td>
<td><xsl:value-of select="count(key('calls_by_cat', Category)[Status='Open'])" /></td>
</tr>
</xsl:for-each>
<tr>
<td>Total</td>
<td><xsl:value-of select="count(CALLS/HelpCall[substring(CreatedDate,1,7)=$created_last_month])" /></td>
<td><xsl:value-of select="count(CALLS/HelpCall[substring(ClosedDate,1,7)=$closed_last_month])" /></td>
<td><xsl:value-of select="count(CALLS/HelpCall[substring(CreatedDate,1,7)=$created_this_month])" /></td>
<td><xsl:value-of select="count(CALLS/HelpCall[substring(ClosedDate,1,7)=$closed_this_month])" /></td>
<td><xsl:value-of select="count(CALLS/HelpCall[Status='Open'])" /></td>
</tr>
</table>
</body></html>
</xsl:template>

</xsl:stylesheet>
[/tt]
[4] I have not added control whre the unique months node-set contains only one entry (only single month data are accumulated), but that can be made perfect in this regard by further testing things.

 

[jel]

Tsuji,
You're quite right.
All in all, I think the best way to handle dates in this case would be just to add them in the XML on creation: a transformation should contain as little calculations and logic as possible.
I love the way you calculate the max-values: more elegant and efficient then what I did. *

 

[tsuji]

Thanks,jel. But I think yours are just about _the_ solution(s) without adding complications not actually being asked.

 

[dachaney]

Guys,

Thanks for your help. I'm not experienced in XSLT and this small example is showing me how much I don't know.

I can get this working as expected with a basic static xml file - thanks, however the next issue would be to connect this to the real data held in a sharepoint list (MOSS2007). Do you have any experience of this ?

The fields in the sharepoint list are referred to as
<Rows>
<Row>
<Category>Hardrware</Category>
....
</Row>
</Rows>
etc

and when looking at other views of Sharepoint lists they refer to the list as '/dsqueryresponse/rows/row'

should it be just a case replacing the the relevant HelpCall or CALLS/HelpCall with Rows or Rows/Rows - do I need to include the dsqueryresponse as well ?

For Info - I am using Sharepoint Designer 2007 with Sharepoint Server 2007


Thanks again

 

[jel]

Sorry, I don't know about Sharepoint.
It seems to me that CALLS are the equivalent of Rows, and HelpCall to Row.
Whether or not you have to include dsqueryresponse I can't say, as I don't know the Sharepoint output.
However, you can find out a lot by by using '//nodename' (meaning any node named nodename):
'//Row' will match 'dsqueryresponse/Rows/Row', 'Rows/Row', and also 'GeeIDidntExpectThat/WhatsThisAllAbout/dsqueryresponse/Rows/Row'.
So for starters you could replace 'CALLS/HelpCall' with '//Row'.
Mind you: this is all case-sensitve: 'Row' is not the same as 'row'!

 

[dachaney]

jel

got this sorted with sharepoint list.

Basically replaced all the node name calls with /dsqueryresponse/Rows/Row but also what threw me was I needed to prefix each value name with @ to read the data (eg Category replace with @Category)

Thanks again

 

[jel]

'@name' means an atrribte, as in:
<person name="jack" />

'/name' means a child-node, as in:
<person>
<name>jack</name>
</person>

Good to hear it works, congrats.