Monday, 2 January 2017

Split by count excel report in Oracle BI Publisher 10g


Objective:

To create a template to split records in excel sheet in Oracle BI Publisher 10g.

Solution:

Template Creation - EXCEL


Step 1: Open the Excel. (See below image)

Step 2: Go to Formulas => Name Manager – See the XDO call

Step 3: Go to Sheet2 (XDO_METADATA) – See the XSLT code



Step 4: Come to Sheet1 – See how the groupings were done

Step 5: Create one XML file – with the below code, see how the sheet were split by count of records using XML

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<ROWSET>
<xsl:variable name="var_size" select="50000" />
<xsl:for-each select="/ROWSET/ROW">
<xsl:variable name="var_pos" select="position()" />
<xsl:variable name="var_mod" select="$var_pos mod($var_size)" />
<xsl:if test="$var_mod = 1">
<xsl:variable name="var_groupNum" select="($var_pos - $var_mod) div
number($var_size) + 1" />
<xsl:element name="CountGroup">
<xsl:attribute name="name">
<xsl:value-of select="concat('Group', $var_groupNum)" />
</xsl:attribute>
<xsl:for-each select="/ROWSET/ROW[position() &gt; ($var_pos -1) and position() &lt; ($var_pos + $var_size)]">
<xsl:copy-of select="." />
</xsl:for-each>
</xsl:element>
</xsl:if>
</xsl:for-each>
</ROWSET>
</xsl:template>
</xsl:stylesheet>

Note:
Ø  Workout example screenshot shared below.  
Ø  Template has been attached separately with this component.
Ø  Output will be multiple sheets, which split by count of records.







Output: Output will be multiple sheets of excel file, which are split by count of records (50000).

By
Karkuvelraja T

5 comments:

  1. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (no matter why you broke up) you must watch this video
    right away...

    (VIDEO) Have your ex CRAWLING back to you...?

    ReplyDelete
  2. Thanks for sharing such a great article about Punchout CXML, it will help me in my work.
    Punchout CXML Catlog




    ReplyDelete
  3. Thanks for sharing artile about OCI Punchout
    cXML PunchOut

    ReplyDelete
  4. cXML PunchOut - Commerce extensible markup language application who is a protocol created by Ariba used to communicate between sessions across the internet.
    Commerce Extensible Markup Language Punchout

    ReplyDelete