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
  • January 2, 2017 | 20 views