XSLT pivot data by date -
i have xml records show sales date. need pivot information table shows sales week.
here xml:
<?xml version="1.0"?> -<data> <report datetime="9/24/2013 10:27 am"/> <reportnumber displayas="report number">7</reportnumber> <businessdate displayas="business date">08/19/2013 08/25/2013</businessdate> <businesssiteids displayas="store">nebo enchiladas</businesssiteids> <colheaders> -<colheader day7="08/25/2013" day7name="sunday" day6="08/24/2013" day6name="saturday" day5="08/23/2013" day5name="friday" day4="08/22/2013" day4name="thursday" day3="08/21/2013" day3name="wednesday" day2="08/20/2013" day2name="tuesday" day1="08/19/2013" day1name="monday"/> </colheaders> <row reportamtdaily="0.000000" listname="closing reading:" businessdate="2013-08-19t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="1"/> <row reportamtdaily="0.000000" listname="closing reading:" businessdate="2013-08-21t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="2"/> <row reportamtdaily="0.000000" listname="closing reading:" businessdate="2013-08-22t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="3"/> <row reportamtdaily="0.000000" listname="closing reading:" businessdate="2013-08-23t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="4"/> <row reportamtdaily="0.000000" listname=" - opening reading" businessdate="2013-08-19t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="5"/> <row reportamtdaily="0.000000" listname=" - opening reading" businessdate="2013-08-21t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="6"/> <row reportamtdaily="0.000000" listname=" - opening reading" businessdate="2013-08-22t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="7"/> <row reportamtdaily="0.000000" listname=" - opening reading" businessdate="2013-08-23t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="8"/> <row reportamtdaily="3334.220000" listname="gross sales:" businessdate="2013-08-19t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="9"/> <row reportamtdaily="4187.620000" listname="gross sales:" businessdate="2013-08-21t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="10"/> <row reportamtdaily="572.190000" listname="gross sales:" businessdate="2013-08-22t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="11"/> <row reportamtdaily="10856.970000" listname="gross sales:" businessdate="2013-08-23t00:00:00" businesssiteid="40e9e40d-5e7e-4eb3-99c6-8013cd31480d" linenumber="12"/> </data> i want output each businesssiteid:
------------------08/19/2013 08/20/20/2013 08/21/2013 08/22/2013 08/23/2013 08/24/2013 08/25/2013 closing reading: 0 0 0 0 0 0 0 -opening reading 0 0 0 0 0 0 0 gross sales 3334.22 0 4187.62 572.19 10856.97 0 0 i tried using key , variable in xslt nothing returns either way , don't think it's right because need value based on combination of businessdate,listname , businesssiteid.
this template of returns data in un-pivoted format. can point me in right direction please.
<xsl:template match="row"> <tr> <td> <div class="col1"> <xsl:value-of select="@listname"/> </div> </td> <td> <div class="col2"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col3"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col4"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col5"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col6"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col7"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col8"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col9"> <!--<xsl:value-of select="@"/>--> </div> </td> </tr> <!--</xsl:if>--> xslt
<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" exclude-result-prefixes="msxsl" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:xsl="http://www.w3.org/1999/xsl/transform"> <user:data xmlns:user="user"> <datamodel name="sales.sdr"> <editable value="false" /> <fields>listname,reportqtydaily</fields> <filters> <filter field="reportnumber" value="7" /> </filters> <totals /> <sorting> </sorting> <grouping /> <parameters> <parameter field="businessdate" prompt="true" /> <parameter field="businesssiteids" prompt="true" /> </parameters> <reportheader headerfile="netsales1.xslt"/> </datamodel> <version>1.0</version> <reportdescription>filtered by: reportnumber parameters: businessdate,businesssiteids fields displayed: line text</reportdescription> <orientation value="horizontal" /> </user:data> <xsl:output method="html" /> <xsl:key name="klistname" match="/data/row/@listname" use="."/> <xsl:template match="data"> <html> <head /> <style type="text/css">div.col1{width:2.00in;left}div.col2{width:1.00in;right}div.col3{width:1.00in;right}div.col4{width:1.00in;right}div.col5{width:1.00in;right}div.col6{width:1.00in;right}div.col7{width:1.00in;right}div.col8{width:1.00in;right}div.col9{width:1.00in;right}</style> <body> <table class="report_header"> <tr> <td class="title">weekly sdr</td> </tr> <tr> <td> <table> <tr> <td /> <td class="parameterlabel">business date:</td> <td> <xsl:value-of select="businessdate" /> </td> </tr> <tr> <td /> <td class="parameterlabel">store:</td> <td> <xsl:value-of select="businesssiteids" /> </td> </tr> </table> </td> </tr> <tr> <td /> <td /> </tr> <tr> <td> <table class="report_tabulardata"> <thead class="report_tabulardata"> <tr class="columnheader"> <xsl:apply-templates select="colheaders/colheader" /> </tr> </thead> </table> </td> </tr> <tr> <td> <table class="report_tabulardata"> <xsl:apply-templates select="row" /> </table> </td> </tr> </table> </body> </html> ...other templates remove make easier read <xsl:template match="row"> <!--<xsl:for-each select="row[@listname]"> <xsl:variable name="desc" select="row[@listname]"/>--> <tr> <td> <div class="col1" align="left"> <xsl:value-of select="@listname"/> </div> </td> <!--<xsl:for-each select="//@region[generate-id(.)= generate-id(key('b',.)[1])]"> </xsl:for-each>--> <td> <div class="col2" align="right"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col3" align="right"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col4" align="right"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col5" align="right"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col6" align="right"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col7" align="right"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col8" align="right"> <xsl:value-of select="@reportamtdaily"/> </div> </td> <td> <div class="col9" align="right"> <!--<xsl:value-of select="sum($thisgroup/@reportamtdaily)"/>--> </div> </td> </tr> <!--</xsl:for-each>--> </xsl:template> </xsl:stylesheet>
something might trick:
<?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/xsl/transform" version="2.0"> <xsl:variable select="reverse(/data/colheaders/colheader/@*[not(local-name()[contains(.,'name')])])" name="days"/> <xsl:variable name="ids" select="distinct-values((//@businesssiteid))"/> <xsl:template match="/"> <html> <body> <h2>weekly report</h2> <p>report date/time: <xsl:value-of select="/data/report/@datetime"/></p> <p><xsl:value-of select="/data/reportnumber/@displayas"/>: <xsl:value-of select="/data/reportnumber"/></p> <p><xsl:value-of select="/data/businessdate/@displayas"/>: <xsl:value-of select="/data/businessdate"/></p> <p><xsl:value-of select="/data/businesssiteids/@displayas"/>: <xsl:value-of select="/data/businesssiteids"/></p> <xsl:for-each select="$ids"> <h4>site id: <xsl:value-of select="."/></h4> <table> <thead> <tr> <th/> <xsl:apply-templates select="$days" mode="header"/> </tr> </thead> <tbody> <tr> <td>closing reading:</td> <xsl:apply-templates select="$days" mode="closing"> <xsl:with-param name="currentid" select="."/> </xsl:apply-templates> </tr> <tr> <td>opening reading:</td> <xsl:apply-templates select="$days" mode="opening"> <xsl:with-param name="currentid" select="."/> </xsl:apply-templates> </tr> <tr> <td>gross sales:</td> <xsl:apply-templates select="$days" mode="sales"> <xsl:with-param name="currentid" select="."/> </xsl:apply-templates> </tr> </tbody> </table> </xsl:for-each> </body> </html> </xsl:template> <xsl:template match="/data/colheaders/colheader/@*" mode="header"> <th> <xsl:value-of select="."/> </th> </xsl:template> <xsl:template match="/data/colheaders/colheader/@*" mode="closing"> <xsl:param name="currentid"/> <xsl:variable select="concat(substring-after(substring-after(.,'/'),'/') , '-', substring-before(.,'/'), '-', substring-before(substring-after(.,'/'),'/'))" name="closedate"/> <xsl:for-each select="."> <xsl:choose> <xsl:when test="/data/row/@businessdate[../@businesssiteid=$currentid][../@listname='closing reading:'][contains(.,$closedate)]"> <xsl:variable name="numbera" select="/data/row/@businessdate[../@businesssiteid=$currentid][../@listname='closing reading:'][contains(.,$closedate)]/../@reportamtdaily"/> <td> <xsl:value-of select="format-number( round(100*$numbera) div 100 , '###,###,##0.00' )" /> </td> </xsl:when> <xsl:otherwise> <td> <xsl:text>0</xsl:text> </td> </xsl:otherwise> </xsl:choose> </xsl:for-each> </xsl:template> <xsl:template match="/data/colheaders/colheader/@*" mode="opening"> <xsl:param name="currentid"/> <xsl:variable select="concat(substring-after(substring-after(.,'/'),'/') , '-', substring-before(.,'/'), '-', substring-before(substring-after(.,'/'),'/'))" name="opendate"/> <xsl:for-each select="."> <xsl:choose> <xsl:when test="/data/row/@businessdate[../@businesssiteid=$currentid][../@listname=' - opening reading'][contains(.,$opendate)]"> <xsl:variable name="numberb" select="/data/row/@businessdate[../@businesssiteid=$currentid][../@listname=' - opening reading'][contains(.,$opendate)]/../@reportamtdaily"/> <td> <xsl:value-of select="format-number( round(100*$numberb) div 100 , '###,###,##0.00' )" /> </td> </xsl:when> <xsl:otherwise> <td> <xsl:text>0</xsl:text> </td> </xsl:otherwise> </xsl:choose> </xsl:for-each> </xsl:template> <xsl:template match="/data/colheaders/colheader/@*" mode="sales"> <xsl:param name="currentid"/> <xsl:variable select="concat(substring-after(substring-after(.,'/'),'/') , '-', substring-before(.,'/'), '-', substring-before(substring-after(.,'/'),'/'))" name="salesdate"/> <xsl:for-each select="."> <xsl:choose> <xsl:when test="/data/row/@businessdate[../@businesssiteid=$currentid][../@listname='gross sales:'][contains(.,$salesdate)]"> <xsl:variable name="numberc" select="/data/row/@businessdate[../@businesssiteid=$currentid][../@listname='gross sales:'][contains(.,$salesdate)]/../@reportamtdaily"/> <td> <xsl:value-of select="format-number( round(100*$numberc) div 100 , '###,###,##0.00' )" /> </td> </xsl:when> <xsl:otherwise> <td> <xsl:text>0</xsl:text> </td> </xsl:otherwise> </xsl:choose> </xsl:for-each> </xsl:template>
Comments
Post a Comment