Split by count excel report in Oracle BI Publisher 10g

create a template to split records in excel sheet in Oracle BI Publisher 10g.
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″
<xsl:template match=”/”>
<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:for-each select=”/ROWSET/ROW[position() &gt; ($var_pos -1) and position() &lt; ($var_pos + $var_size)]”>
<xsl:copy-of select=”.” />

Ø  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).

Karkuvelraja T
  • January 2, 2017 | 86 views