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

4 comments:

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




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

    ReplyDelete
  3. 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
  4. Can we achieve this in Oracle Apps EBS R12.2 ?

    ReplyDelete