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
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
1 NULL 0 NULL NULL
2 1 0 0 01/01/2001
2 1 0 0 01/08/2001
2 1 0 0 01/15/2001
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
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
Final XML Output
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 tableDECLARE @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)
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.DROP TABLE [dbo].[#tmpWeeklyAmounts]
CREATE TABLE #tmpWeeklyAmounts(eid int, ename varchar(100), sumofspend decimal(10,2), weekenddate datetime)
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.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
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.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
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 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
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)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]
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)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
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.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
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[emp!1!empname],[ts!2!weekenddate]
FOR XML EXPLICIT
DROP TABLE #tmpWeeklyAmounts
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<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>
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.//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");
}
//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//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());
<?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 <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"/>
<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: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>
<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: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>
<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.<xsl:for-each select="emp[@eid!=0]">
<TR valign="top">
<TD nowrap="nowarp" class='title1'>;<xsl:number value="position()" format="1"/>.<xsl:text disable-output-escaping="yes"> </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"> </xsl:text>
</TD>
</xsl:for-each>
Calculate sum of row wise (employee) total amount of all the week.<TD align="right">
<xsl:value-of select="@sumofspend"/>
<xsl:text disable-output-escaping="yes"> </xsl:text>
</TD>
</xsl:for-each>
<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"> </xsl:text></TD>
</TR>
</xsl:for-each>
Calculate sum of column wise (weekend) total amount of all the employee.<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"> </xsl:text></TD>
</TR>
</xsl:for-each>
<TR valign="top">
<TD class='title1' nowrap="nowarp" align="right">Total<xsl:text disable-output-escaping="yes"> </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"> </xsl:text></TD>
</TR>
</TBODY></TABLE></BODY>
</HTML>
</xsl:template>
Function for sum of amounts.<TD class='title1' nowrap="nowarp" align="right">Total<xsl:text disable-output-escaping="yes"> </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"> </xsl:text></TD>
</TR>
</TBODY></TABLE></BODY>
</HTML>
</xsl:template>
<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"> </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>
<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"> </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>
No comments:
Post a Comment