Preprocess the Data Using an XSL Transformation (XSLT) File

For the best performance, design the data model to perform as much of the data processing as possible. When you can't get the required output from the data engine, you can preprocess the data using an XSLT file that contains the instructions to transform the data.

Some sample use cases include:

To create groups to establish the necessary hierarchy to support the desired layout.
To add style attributes to data elements.
To perform complex data processing logic that may be impossible in the Excel Template or undesirable for performance reasons. The Template Builder for Excel doesn't support preview for templates that require XSLT preprocessing.

To use an XSLT preprocess file:

Create the file and save as .xsl.
Upload the file to the report definition in the Publisher catalog, as you would a template:
1. Navigate to the report in the catalog.
2. Click Edit.
3. Click Add New Layout.
4. Click Upload.
5. Complete the fields in the Upload dialog and select XSL Stylesheet (HTML/XML/Text) as the template Type.
6. After upload, click View a List. Deselect Active, so that users do not see this template as an option when they view the report.
  
  For testing purposes, you might want to maintain the XSL template as active to enable you to view the intermediate data when the template is applied to the data. After testing is complete, set the template to inactive.
7. Save the report definition.
In the Excel template, on the XDO_METADATA sheet, in the Header section, enter the file name for the Preprocess XSLT File parameter. For example: splitByBrand.xsl.

XSLT Preprocessing Examples: Split Flat Data into Multiple Sheets

This topic presents two examples of using an XSLT preprocess file to group flat data so that it can be split into multiple sheets in Excel.

The examples are:

Split the Data by a Specific Field
Split the Data by Count of Rows

Splitting the data by row count is an option when your report data exceeds the sheet row size of Excel 2003 (65,536 rows per sheet).

Both examples use the following XML data:

 <ROWSET>
    <ROW>
    <Products.Type>COATINGS</Products.Type> 
    <Products.Brand>Enterprise</Products.Brand>
    <Markets.Region>CENTRAL REGION</Markets.Region> 
    <Markets.District>CHICAGO DISTRICT</Markets.District> 
    <Periods.Year>2000</Periods.Year> 
    <Measures.Dollars>1555548.0</Measures.Dollars> 
   </ROW>
   <ROW>
    <Products.Type>COATINGS</Products.Type> 
    <Products.Brand>Enterprise</Products.Brand> 
    <Markets.Region>EASTERN REGION</Markets.Region> 
    <Markets.District>NEW YORK DISTRICT</Markets.District>
    <Periods.Year>2000</Periods.Year> 
    <Measures.Dollars>1409228.0</Measures.Dollars> 
   </ROW>
...
</ROWSET>

Split the Data by a Specific Field

This example demonstrates how to use an XSLT preprocess file to create a grouping in the data that will enable the splitting of the data across multiple Excel sheets based on the grouping.

This example groups the sample data by the Products.Brand field.

Create an XSLT file to group the data.

The following sample XSLT file groups the data according to <Products.Brand> and creates a high level element <BrandGroup> for each of those groups.

<?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:for-each-group select="/ROWSET/ROW" group-by="./Products.Brand">
        <xsl:variable name="var_brand" select="current-grouping-key()" /> 
        <BrandGroup>
          <xsl:attribute name="name">
             <xsl:value-of select="$var_brand" /> 
          </xsl:attribute>
          <xsl:copy-of select="current-group()" /> 
       </BrandGroup>
     </xsl:for-each-group>
    </ROWSET>
  </xsl:template></xsl:stylesheet>

When applied to the data sample, this XSLT file generates intermediate data as follows:

 <ROWSET> 
   <BrandGroup name="Enterprise"> 
     <ROW> 
       <Products.Type>COATINGS</Products.Type>
       <Products.Brand>Enterprise</Products.Brand>
       <Markets.Region>CENTRAL REGION</Markets.Region>
       <Markets.District>CHICAGO DISTRICT</Markets.District>
       <Periods.Year>2000</Periods.Year> 
       <Measures.Dollars>1555548.0</Measures.Dollars>
     </ROW> 
     ... 
   </BrandGroup> 
   ...  <ROWSET>

Save the XSLT file as splitByBrand.xsl and upload the file to the report definition in the catalog. Select "XSL Stylesheet (HTML/XML/Text)" as the template type.
In the Excel template file, in the XDO_METADATA sheet, enter the following:
- For the Preprocess XSLT File parameter, enter "splitByBrand.xsl"
- In the Data Constraints region, make the entries to split the data into multiple sheets based on the <BrandGroup> element created by the results of the XSLT preprocessing.
  - Coulmn A entry: XDO_SHEET_?, Column B entry: <?//BrandGroup?>
  - Coulmn A entry: XDO_SHEET_NAME_?, Column B entry: <?./@name?>
  The sample entries in the XDO_METADATA sheet are shown in this figure.
  
  Description of the illustration excel_ex_xslt.gif
Hide the XDO_METADATA sheet if you do not want your users to see it. Upload the Excel template file to the report definition in the catalog.

Split the Data by Count of Rows

This example demonstrates how to use an XSLT preprocess file to group the sample XML data.

Group the sample XML data by the count of occurrences of /ROWSET/ROW, and then configure the Excel template to create a new sheet for each occurrence of the newly created group.

Create an XSLT file to create groups in the data according to a size specified in a variable.

The following sample XSLT file groups the occurrences of /ROWSET/ROW according to the value of $var_size and creates a high level element <CountGroup> for each of those groups.

  <?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="3" />
       <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>

When applied to the data sample, this XSLT file generates intermediate data as follows:

 <ROWSET> 
   <CountGroup name="Group1">
      <ROW> 
       <Products.Type>COATINGS</Products.Type>
        <Products.Brand>Enterprise</Products.Brand>
        <Markets.Region>CENTRAL REGION</Markets.Region>
        <Markets.District>CHICAGO DISTRICT</Markets.District>
        <Periods.Year>2000</Periods.Year>
        <Measures.Dollars>1555548.0</Measures.Dollars>
      </ROW> 
     ... 
   </CountGroup> 
   ... 
 <ROWSET>

Save the XSLT file as splitByCount.xsl and upload the file to the report definition in the catalog. Select "XSL Stylesheet (HTML/XML/Text)" as the template type.
In the Excel template file, in the XDO_METADATA sheet, enter the following:
- For the Preprocess XSLT File parameter, enter "splitByCount.xsl".
- In the Data Constraints region, make these entries.
  - Column A entry: XDO_SHEET_?, Column B entry: <?//CountGroup?>
  - Column A entry: XDO_SHEET_NAME_?, Column B entry: <?./@name?>
Hide the XDO_METADATA sheet so that it doesn't display to report consumers.
Upload the Excel template file to the report definition in the catalog.