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=”/”>

<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() > ($var_pos -1) and position() < ($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).

Karkuvelraja T

Doyensys

A technology service company that provides innovative solutions around the oracle platform With Laser focus on customer delight as the primary success parameter.