For example, if you have the following raw data:
You could use XSLT to summarized the data as follows:
Or even summarized it as follows:
The XSLT used is shown below. Noticed that it is possible to create subroutines within XSLT but instead of calling it sub, it is known as templates. The two templates used to produce the above summary are "Horizontal" and "Vertical".
There is an extensive tutorial on XSLT and XML found at http://www.w3schools.com/xsl/. There is a very powerful free tool from Microsoft called XSLTMajic. This tool can verify your xml and also allow you to debug your xslt. Unfortunately, I can no longer find it. You could find other commercial software to do this but I have tried any of these.
In ASP.Net, you can then use xml web control to perform the transformation easily:
<!--
Program: rpt.xsl
Author: Strovek
Date: Dec 05, 2004
Revised
Note
XSLT template for formating summary output
-->
<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
<xsl:param name="mode" />
<xsl:output method="html"/>
<xsl:key name="ProdKey" match="partMaster" use="PROD" />
<xsl:key name="StepKey" match="partMaster" use="CURRENTSTEP" />
<xsl:key name="ProdStepKey" match="partMaster" use="concat(PROD, ' ', CURRENTSTEP)" />
<xsl:template match="/">
<link rel="stylesheet" type="text/css" href="stylesheets/xsl.css" title="Style"/>
<table border="1">
<xsl:choose>
<xsl:when test="$mode='V'">
<xsl:call-template name="Vertical" />
</xsl:when>
<xsl:otherwise>
<xsl:call-template name="Horizontal" />
</xsl:otherwise>
</xsl:choose>
</table>
</xsl:template>
<xsl:template name="Horizontal">
<tr class="head">
<th>Step</th>
<xsl:for-each select="//partMaster[count(.|key('ProdKey', PROD)[1])=1]">
<xsl:sort select="PROD" />
<th><xsl:value-of select="PRODUCT" /></th>
</xsl:for-each>
</tr>
<xsl:for-each select="//partMaster[count(.|key('StepKey', CURRENTSTEP)[1])=1]">
<xsl:sort select="CURRENTSTEPNUM" />
<tr class="row{position() mod 2}">
<xsl:variable name="stepName"><xsl:value-of select="CURRENTSTEP" /></xsl:variable>
<td><xsl:value-of select="$stepName" /></td>
<xsl:for-each select="//partMaster[count(.|key('ProdKey', PROD)[1])=1]">
<xsl:sort select="PROD" />
<xsl:variable name="prodName"><xsl:value-of select="PROD" /></xsl:variable>
<xsl:call-template name="sumPrdStep">
<xsl:with-param name="vProd"><xsl:value-of select="$prodName" /></xsl:with-param>
<xsl:with-param name="vStep"><xsl:value-of select="$stepName" /></xsl:with-param>
</xsl:call-template>
</xsl:for-each>
</tr>
</xsl:for-each>
</xsl:template>
<xsl:template name="Vertical">
<tr class="head">
<th>Step</th>
<xsl:for-each select="//partMaster[count(.|key('StepKey', CURRENTSTEP)[1])=1]">
<xsl:sort select="CURRENTSTEP" />
<th><xsl:value-of select="CURRENTSTEP" /></th>
</xsl:for-each>
</tr>
<xsl:for-each select="//partMaster[count(.|key('ProdKey', PROD)[1])=1]">
<xsl:sort select="PROD" />
<tr class="row{position() mod 2}">
<xsl:variable name="prodName"><xsl:value-of select="PROD" /></xsl:variable>
<td><xsl:value-of select="PRODUCT" /></td>
<xsl:for-each select="//partMaster[count(.|key('StepKey', CURRENTSTEP)[1])=1]">
<xsl:sort select="CURRENTSTEPNUM" />
<xsl:variable name="stepName"><xsl:value-of select="CURRENTSTEP" /></xsl:variable>
<xsl:call-template name="sumPrdStep">
<xsl:with-param name="vProd"><xsl:value-of select="$prodName" /></xsl:with-param>
<xsl:with-param name="vStep"><xsl:value-of select="$stepName" /></xsl:with-param>
</xsl:call-template>
</xsl:for-each>
</tr>
</xsl:for-each>
</xsl:template>
<xsl:template name="sumPrdStep">
<xsl:param name="vProd" />
<xsl:param name="vStep" />
<xsl:variable name="cStr"><xsl:value-of select="concat($vProd, ' ', $vStep)" /></xsl:variable>
<td>
<xsl:value-of select="sum(key('ProdStepKey', $cStr)/CURRENTQTY)" />
</td>
</xsl:template>
</xsl:stylesheet>
The aspx is as follows:
The code behind that does the transformation is as follows:
<%@ Page Language="vb" autoeventwireup="false" codebehind="WipSum.aspx.vb" Inherits="PJ.WipSum" %>
<html>
<head>
<link href="stylesheets/ANOB.css" type="text/css" rel="stylesheet" />
</head>
<body>
<form runat="server">
<div class="BannerStyle"> Wip Tracking System - Wip Summary Display
</div>
<br />
<asp:Label id="lblMsg" runat="server"></asp:Label>
<br />
<asp:Label id="lblErrMsg" runat="server" forecolor="Red"></asp:Label>
<table>
<tbody>
<tr>
<td>
<asp:RadioButtonList id="SumOptList" runat="server" RepeatDirection="Horizontal" Width="250px">
<asp:ListItem Value="H" Selected="True">Horizontal</asp:ListItem>
<asp:ListItem Value="V">Vertical</asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td>
<asp:Button id="btnSubmit" runat="server" Text="Submit"></asp:Button>
</td>
</tr>
</tbody>
</table>
</form>
<p>
<hr />
</p>
<p>
<asp:Label id="lblRslt" runat="server"></asp:Label>
</p>
<p>
<asp:Xml id="XmlOut" runat="server"></asp:Xml>
</p>
</body>
</html>
Private Sub ShowSum(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnSubmit.Click
Dim ds As New DataSet
Trace.Write("Button clicked")
lblErrMsg.Text = ""
Try
If IsNothing(Cache(cacheName)) Then
' Populate the dataset
Conn.Open()
Dim sql As String = "select prod, id product, currentstep, " _
& "currentstepnum, " _
& "sum(currentqty) currentqty from partmaster p, system_lookup s where " _
& "status = 'Active' and s.systemid = 'Prod' and id_type = prod group " _
& "by prod, id, currentstep, currentstepnum"
Dim dbCmd As OleDbCommand = Conn.CreateCommand
dbCmd.CommandText = sql
Dim dbAdapt As New OleDbDataAdapter(dbCmd)
dbAdapt.Fill(ds, "partMaster")
' Populate the dataset
' After getting the dataset, store in cache for 120 seconds.
Cache.Add(cacheName, ds, Nothing, DateTime.Now.AddSeconds(120), _
System.TimeSpan.Zero, System.Web.Caching.CacheItemPriority.Normal, _
Nothing)
Else
' Read from Cache
ds = Cache.Get(cacheName)
End If
' Perform transformation
Trace.Write("Selected value is " & SumOptList.SelectedValue)
Dim xmlStr As String = ds.getXML
Dim xmlDoc As New XMLDocument
Dim xslArg As New System.Xml.Xsl.XsltArgumentList()
Dim uriStr As String = xmlDoc.BaseURI
Trace.Write("BaseUri is *" & uriStr & "*")
xslArg.AddParam("mode", uriStr, SumOptList.SelectedValue)
xmlDoc.LoadXML(xmlStr)
'XMLOut.Document = xmlDoc
XmlOut.DocumentContent = xmlStr
XMLOut.TransformSource = "xslt/rpt.xsl"
XMLOut.TransformArgumentList = xslArg
' Perform transformation
Catch ex As Exception
lblErrMsg.Text += "<br>Error " & ex.Message
Trace.Write("<br>" & ex.StackTrace)
Finally
If Conn.State = ConnectionState.Open Then
Conn.Close()
End If
End Try
End Sub
No comments:
Post a Comment