For the best performance, design the data model to perform as much of the data processing as possible. When it is not possible to 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
Note:
The Template Builder for Excel does not 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 BI Publisher catalog, as you would a template:
Navigate to the report in the catalog.
Click Edit.
Click Add New Layout.
Click Upload.
Complete the fields in the Upload dialog and select "XSL Stylesheet (HTML/XML/Text)" as the template Type.
After upload, click View a List. Deselect Active, so that users do not see this template as an option when they view the report.
Note:
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.
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
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:
Splitting 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>
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.
This example demonstrates how to use an XSLT preprocess file to group the sample XML data.
The sample XML data is grouped 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