Introduction
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() > ($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).
Summary
This Post is described about Split by count excel report in Oracle BI Publisher 10g. Workout example screenshot shared below. The template has been attached separately with this component. Output has multiple sheets, which split by count of records.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
