Matrix Report using SQL Server XML and XSLT Code

Desiging Matrix Report using SQL Server XML, XSLT and JAVA Code

In the above matrix report image is grouped week by employee vs employee by week.
Part 1 - SQL CODE

DECLARE @depart_id int
DECLARE @startdate datetime
DECLARE @enddate DATETIME

SET @depart_id = 314
SET @startdate = '01/01/2001'
SET @enddate = '02/28/2001'

DECLARE @e_id int
DECLARE @ename varchar(100)

Creating temporary table

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#tmpWeeklyAmounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[#tmpWeeklyAmounts]
CREATE TABLE #tmpWeeklyAmounts(eid int, ename varchar(100), sumofspend decimal(10,2), weekenddate datetime)

Inserting employee's sum of weekly spending amount data to the temporary table.

INSERT INTO #tmpWeeklyAmounts
SELECT
  eid, ename, sum(sumofspend), weekenddate
FROM
  viewTimeSheettmpWeeklyAmounts
WHERE
  ptypeid = 2 and
  (weekenddate >= CONVERT(DATETIME, @startdate, 102)) AND
  (weekenddate <= CONVERT(DATETIME, @enddate, 102)) and departid = @depart_id
GROUP BY
  eid, ename, weekenddate, departid, departname
ORDER BY
  weekenddate

Creating Cursor, to insert null value if employee not spending amount on the weekly.

DECLARE tsweekenddate_cursor CURSOR FOR

SELECT DISTINCT eid, ename FROM #tmpWeeklyAmounts ORDER BY eid
OPEN tsweekenddate_cursor
FETCH NEXT FROM tsweekenddate_cursor INTO @e_id, @ename

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #tmpWeeklyAmounts
  SELECT DISTINCT
    @e_id, @ename, null, weekenddate
  FROM
    #tmpWeeklyAmounts
  WHERE
    weekenddate NOT IN (SELECT weekenddate FROM #tmpWeeklyAmounts WHERE eid = @e_id)
  ORDER BY
    weekenddate
  FETCH NEXT FROM tsweekenddate_cursor INTO @e_id, @ename
END

CLOSE tsweekenddate_cursor
DEALLOCATE tsweekenddate_cursor

Generating the temporary table record select statement into XML using SQL XML EXPLICIT.
List of all the distinct employee records. (i.e., top of the record, tag is 1 and parent is null)
1 NULL 1234 Kid Mah NULL NULL
1 NULL 7342 Tio Aal NULL NULL
1 NULL 7567 Sia Rbj NULL NULL

SELECT DISTINCT
  1 AS Tag,
  NULL AS Parent,
  eid AS [emp!1!eid],
  ename AS [emp!1!empname],
  null AS [ts!2!sumofspend],
  null AS [ts!2!weekenddate]
FROM
  #tmpWeeklyAmounts emp

Create a dummy one header record for the week header.(i.e., top of the record, tag is 1 and parent is null)
1 NULL 0 NULL NULL

UNION ALL
SELECT
  1 AS Tag,
  NULL AS Parent,
  0 AS [emp!1!eid],
  '' AS [emp!1!empname],
  null AS [ts!2!sumofspend],
  null AS [ts!2!weekenddate]

List of all the distinct weekend date records.(i.e., Parent of tag 1, tag is 2 and parent is 1)
2 1 0 0 01/01/2001
2 1 0 0 01/08/2001
2 1 0 0 01/15/2001

UNION ALL
SELECT DISTINCT
  2 AS Tag,
  1 AS Parent,
  0 AS [emp!1!eid],
  '' AS [emp!1!empname],
  0 AS [ts!2!sumofspend],
  CONVERT(VARCHAR(10),weekenddate,101) AS [ts!2!weekenddate]
FROM
  #tmpWeeklyAmounts ts

List of all the distinct employee's and weekend's sum of spending amount records.(i.e., tag is 2 and parent is 1)
2 1 1234 Mdr Mih NULL 01/01/2001
2 1 1344 Mdr Mih NULL 01/08/2001
2 1 1454 Mdr Mih 21.50 01/29/2001
2 1 1564 Mdr Mih 0.00 01/15/2001
2 1 1674 Mdr Mih 3.00 01/22/2001
2 1 7892 Mbo Ahl .00 01/15/2001
2 1 7902 Mbo Ahl .00 01/29/2001
2 1 7122 Mbo Ahl 11.00 01/08/2001
2 1 7142 Mbo Ahl 21.00 01/01/2001

UNION ALL
SELECT DISTINCT
  2 AS Tag,
  1 AS Parent,
  eid AS [emp!1!eid],
  ename AS [emp!1!empname],
  sumofspend AS [ts!2!sumofspend],
  CONVERT(VARCHAR(10),weekenddate,101) AS [ts!2!weekenddate]
FROM
  #tmpWeeklyAmounts ts

Order by empname and weeek end, list of all the combine records are converted into XML format.
1 NULL 0 NULL NULL
2 1 0 .00 01/01/2001
2 1 0 .00 01/08/2001
2 1 0 .00 01/15/2001
1 NULL 21261 Mke Dkn NULL NULL
2 1 21261 Mke Dkn NULL 01/01/2001
2 1 21261 Mke Dkn NULL 01/08/2001
2 1 21261 Mke Dkn NULL 01/15/2001
1 NULL 33219 Maa Dlk NULL NULL

ORDER BY
  [emp!1!empname],[ts!2!weekenddate]
FOR XML EXPLICIT

Delete temporary table

DROP TABLE #tmpWeeklyAmounts

Final XML Output

XML_F52E2B61-18A1-11d1-B105-00805F49916B
<emp eid="0" ename="">
  <ts sumofspend="0.00" weekenddate="01/01/2001"/>
  <ts sumofspend="0.00" weekenddate="01/08/2001"/>
  <ts sumofspend="0.00" weekenddate="01/15/2001"/>
</emp>
<emp eid="7122" ename="Mbo Ahl">
  <ts sumofspend="2.00" weekenddate="01/01/2011"/>
  <ts sumofspend="1.00" weekenddate="01/08/2011"/>
  <ts sumofspend="0.00" weekenddate="01/15/2011"/>
</emp>
<emp eid="6723" ename="Est Vid">
  <ts sumofspend="5.00" weekenddate="01/01/2011"/>
  <ts sumofspend="16.00" weekenddate="01/08/2011"/>
  <ts sumofspend="40.00" weekenddate="01/15/2011"/>
</emp>

Part 2 - Java Code
Read the above records through any program language (like .net, java or php). Here i put java code example to understand to retrieve XML records and pass to the XSLT code. Finally XSLT code is displaying final matrix report.
Java Database Code.

//Database Connection Code
//Calling Stored Procedure through CallableStatement and execute
conDB.prepareCall("{call uspDepart_amount_By_Weekendd()}");
// Return xml based results, assign to string
ResultSet rs = csReport.getResultSet();
while (rs.next()) {
  xmlOutput = xmlOutput+rs.getString("XML_F52E2B61-18A1-11d1-B105-00805F49916B");
}

Java XML Transform code.

//Close both resultset and connection
//Call javax.xml.transform.* function to transform XML to XSLT.
TransformerFactory tf = TransformerFactory.newInstance();
StreamResult tr = new StreamResult( new ByteArrayOutputStream() );
//Pass xsl file name with full path.
Transformer trans = tf.newTransformer(new StreamSource("depart_amount_by_weekend.xsl"));
//Pass parameter if any to display header information.
trans.setParameter("param1", "01/01/2001");
trans.setParameter("param2", "02/25/2001");
//Pass SQL XML output result.
trans.transform(new StreamSource(new StringReader("<Reports>"+xmlOutput +"</Reports>")), tr);
//Print matrix report.
out.println((tr.getOutputStream().toString()).trim());

Part 3 - XSLT Code - depart_amount_by_weekend.xsl

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl='http://www.w3.org/1999/XSL/Transform' version='1.0'>
 <xsl:param name="param1" select="Value"/>
 <xsl:param name="param2" select="Value"/>

Parsing xml.

 <xsl:template match='Reports'>
  <xsl:variable name="totalweekend" select="0"/>
  <HTML>
   <BODY valign="top"><TABLE cellSpacing='0' cellPadding='0' width="100%" border='1' valign="top"><TBODY valign="top">
    <TR valign="top">
     <TD class='title1' nowrap="nowarp">Name</TD>

Display header information with the weekend date data.

     <xsl:for-each select="emp[@eid=0]">
      <xsl:for-each select="ts">
       <TD class='title1' align='center'><xsl:value-of select="substring(@weekenddate,1,5)"/></TD>
      </xsl:for-each>
     </xsl:for-each>
     <TD class='title1' align='center'>Total</TD>
    </TR>

Count total number of weekend date.

    <xsl:variable name="totalweekend"><xsl:value-of select="count(emp[@eid=0]/ts/@weekenddate)" /></xsl:variable>
    <xsl:for-each select="emp[@eid!=0]">
     <TR valign="top">

Display employee name.

      <TD nowrap="nowarp" class='title1'>;<xsl:number value="position()" format="1"/>.<xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text><xsl:value-of select="@ename"/></TD>

Display corresponding employee's amount.

      <xsl:for-each select="ts">
       <TD align="right">
        <xsl:value-of select="@sumofspend"/>
        <xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text>
       </TD>
      </xsl:for-each>

Calculate sum of row wise (employee) total amount of all the week.

      <xsl:call-template name="for.loop">
       <xsl:with-param name="i"><xsl:value-of select="count(ts/@weekenddate)+1"/></xsl:with-param>
       <xsl:with-param name="count"><xsl:value-of select="$totalweekend"/></xsl:with-param>
       <xsl:with-param name="class"></xsl:with-param>
       <xsl:with-param name="cellvalue"></xsl:with-param>
       <xsl:with-param name="type">display</xsl:with-param>
      </xsl:call-template>
      <TD nowrap="nowarp" class='title1' align="right"><xsl:value-of select="format-number(sum(ts/@sumofspend), '##,##,##0.00')"/><xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text></TD>
     </TR>
    </xsl:for-each>

Calculate sum of column wise (weekend) total amount of all the employee.

    <TR valign="top">
     <TD class='title1' nowrap="nowarp" align="right">Total<xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text></TD>
      <xsl:call-template name="for.loop">
       <xsl:with-param name="i">1</xsl:with-param>
       <xsl:with-param name="count"><xsl:value-of select="$totalweekend"/></xsl:with-param>
       <xsl:with-param name="class">title1</xsl:with-param>
       <xsl:with-param name="type">total</xsl:with-param>
       <xsl:with-param name="cellvalue"><xsl:value-of select="format-number(sum(emp/ts[1]/@sumofspend), '##,##,##0.00')"/></xsl:with-param>
      </xsl:call-template>
      <TD nowrap="nowarp" class='title1' align="right"><xsl:value-of select="format-number(sum(emp/ts/@sumofspend), '##,##,##0.00')"/><xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text></TD>
    </TR>
   </TBODY></TABLE></BODY>
  </HTML>
 </xsl:template>

Function for sum of amounts.

 <xsl:template name="for.loop">
  <xsl:param name="i" />
  <xsl:param name="count" />
  <xsl:param name="class" />
  <xsl:param name="cellvalue" />
  <xsl:param name="type" />
  <xsl:if test="$i <= $count">
   <TD align="right"><xsl:attribute name="class"><xsl:value-of select="$class"/></xsl:attribute><xsl:value-of select="$cellvalue"/><xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text></TD>
  </xsl:if>

  <xsl:if test="$i <= $count">
   <xsl:call-template name="for.loop">
    <xsl:with-param name="i"> <xsl:value-of select="$i + 1"/></xsl:with-param>
    <xsl:with-param name="count"> <xsl:value-of select="$count"/></xsl:with-param>
    <xsl:with-param name="class"> <xsl:value-of select="$class"/></xsl:with-param>
    <xsl:with-param name="cellvalue">
     <xsl:if test="$type='total'">
      <xsl:value-of select="format-number(sum(emp/ts[$i+1]/@sumofspend), '##,##,##0.00')"/>
     </xsl:if>
    </xsl:with-param>
    <xsl:with-param name="type"> <xsl:value-of select="$type"/></xsl:with-param>
   </xsl:call-template>
  </xsl:if>
 </xsl:template>
</xsl:stylesheet>