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