Using Advanced BI Publisher Functions

BI Publisher provides a set of functions to achieve additional reporting functionality.

You define these functions in the Data Constraints region of the XDO_METADATA sheet.

The functions make use of Columns A, B, and C in the XDO_METADATA sheet as follows:

Use Column A to declare the function or to specify the defined name of the object to which to map the results of a calculation or XSL evaluation.

Use Column B to enter the special XDO-XSL syntax to describe how to control the data constraints for the XDO function, or the XSL syntax that describes the special constraint to apply to the XDO_ named elements.

Use Column C to specify additional instructions for a few functions.

The functions are described in the following sections:

• Reporting Functions

• Formatting Functions That Rely on Specific Data Attribute Values

• Grouping Functions

Reporting Functions

You can add functions to a template using the commands shown and a combination of BI Publisher syntax and XSL.

A summary list of the commands is shown in the following table. See the corresponding section for details on usage.

Function	Commands
Splitting the Report into Multiple Sheets	XDO_SHEET_? with XDO_SHEET_NAME_?
Declaring and Passing Parameters	XDO_PARAM_?n?
Defining a Link	XDO_LINK_?link object name?
Importing and Calling a Subtemplate	XDO_SUBTEMPLATE_?n?
Referencing Java Extension Libraries	XDO_EXT_?n?

Splitting the Report into Multiple Sheets

Use this set of commands to define the logic to split the report data into multiple sheets.

Note:

Images are not supported across multiple sheets. If the template sheet includes images, when the data is split into multiple sheets, the images are displayed only on the first sheet.

The set of commands is described in the following list:

• Use XDO_SHEET_? to define the logic by which to split the data onto a new sheet.

• Use XDO_SHEET_NAME_? to specify the naming convention for each sheet.

The following table describes the column entries.

Column A Entry	Column B Entry	Column C Entry
XDO_SHEET_?	<?xsl_evaluation to split the data?> Example: <?.//DEPT?>	n/a
XDO_SHEET_NAME_?	<?xsl_expression to name the sheet?> Example: <?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>	(Optional) <?original sheet name?> Example: <?Sheet3?>

XDO_SHEET_? must refer to an existing high-level node in the XML data. The example <?.//DEPT?> creates a new sheet for each occurrence of <DEPT> in the data.

If the data is flat, then you cannot use this command unless you first preprocess the data to create the desired hierarchy. To preprocess the data, define the transformation in an XSLT file, then specify this file in the Preprocess XSLT File field of the header section of the XDO _METADATA sheet. For more information, see Preprocessing the Data Using an XSL Transformation (XSLT) File.

Use XDO_SHEET_NAME_? to define the name to apply to the sheets. In Column B enter the XSL expression to derive the new sheet name. The expression can reference a value for an element or attribute in the XML data, or you can use the string operation on those elements to define the final sheet name. This example:

<?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>

names each sheet using the value of DEPARTMENT_NAME concatenated with "-" and the count of employees in the DEPT group.

The original sheet name entry in Column C tells BI Publisher on which sheet to begin the specified sheet naming. If this parameter is not entered, BI Publisher applies the naming to the first sheet in the workbook that contains XDO_ names. You must enter this parameter if, for example, you have a report that contains summary data in the first two worksheets and the burst data should begin on Sheet3. In this case, you enter <?SHEET3?> in Column C.

Example: Splitting the data into multiple sheets

Using the employee data shown in the previous example. This example:

• Creates a new worksheet for each department

• Names each worksheet the name of the department with the number of employees in the department, for example: Sales-21.

To split the data into sheets:

1. Enter the defined names for each cell of employee data and create the group for the repeating employee data, as shown in the following illustration.
   
   Description of the illustration GUID-EA2F7219-BEB4-4A59-9A7A-A17A6DAD803A-default.gif

   Note:

   Do not create the grouping around the department because the data is split by department.

2. Enter the values that are described in the following table in the Data Constraints section of the XDO_METADATA sheet.

   Column A Entry	Column B Entry
   XDO_SHEET_?	<?.//DEPT?>
   XDO_SHEET_NAME_?	<?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>

The entries are shown in the following illustration.

Description of the illustration GUID-6838AB09-C173-47BD-96C0-D0E20A78BB1D-default.gif

The following illustration shows the generated report. Each department data now displays on its own sheet, which shows the naming convention specified.

Description of the illustration GUID-7690ADED-7EC0-4C98-BEB7-1FF0755D7075-default.gif

Declaring and Passing Parameters

To define a parameter, use the XDO_PARAM_?n? function to declare the parameter, then use the $parameter_name syntax to pass a value to the parameter. A parameter must be defined in the data model.

To declare the parameter, use the command that is described in the following table:

Column A Entry	Column B Entry
XDO_PARAM_?n? where n is the unique identifier for the parameter	<?param@begin:parameter_name;parameter_value?> where parameter_name is the name of the parameter from the data model and parameter_value is the optional default value. For example: <?param@begin:Country;US?>

To use the value of the parameter directly in a cell, refer to the parameter as $parameter_name in the definition for the XDO_ defined name, as described in the following table:

Column A Entry	Column B Entry
XDO_PARAM_?parameter_name? For example: XDO_PARAM_?Country?	<?$parameter_name?> For example: <?$Country?>

You can also refer to the parameter in other logic or calculations in the XDO_METADATA sheet using $parameter_name.

Example: Defining and passing a parameter

To declare and reference a parameter named Country:

1. In the template sheet, mark the cell with a defined name. In the following figure, the cell has been marked with the defined name XDO_?Country?

   
   

   
   Description of the illustration GUID-6A91D3F9-3962-49D2-A98B-3E3720647E71-default.gif

   
   

2. In the hidden sheet assign that cell the parameter value, as shown in the following figure:

   
   

   
   Description of the illustration GUID-F0E7CF75-AAAC-4F3C-95C9-22FEBA605790-default.gif

   
   

Defining a Link

Use the XDO_LINK_? command to define a hyperlink for any data cell, as described in this table.

Example: Defining a Link

Column A Entry	Column B Entry
XDO_LINK_?cell object name? For example: XDO_LINK_?INVOICE_NO?	<xsl statement to build the dynamic URL> For example: <xsl:value-of select="concat('https://server.company.com/documents/invoice_print.show?c_rptno=',./INVOICE_NO)"/>

Assume your company generates customer invoices. The invoices are stored in a central location accessible by a Web server and can be identified by the invoice number (INVOICE_NO).

To generate a report that creates a dynamic link to each invoice:

1. In the template sheet, assign the cell that is to display the INVOICE_NO the XDO defined name: XDO_?INVOICE_NO?, as shown in the following figure:

   
   

   
   Description of the illustration GUID-CA496ADB-E33F-41F2-A886-E12919298C4C-default.gif

   
   

2. In the XDO_METADATA sheet, enter the appropriate values, as described in the following table:

   Column A Entry	Column B Entry
   XDO_LINK_?INVOICE_NO?	<xsl:value-of select="concat('https://server.company.com/documents/invoice_print.show?c_rptno=',./INVOICE_NO)"/>

   The entries in the Excel are shown in the following figure:

   
   

   
   Description of the illustration GUID-2BAEB43F-696C-4A17-A5D8-6C2C36E85BCE-default.gif

   
   

The report output is displayed as shown in the following figure. The logic that is defined in the XDO_METADATA sheet is applied to create a hyperlink for each INVOICE_NO entry.

Description of the illustration GUID-0FB382B0-C8B0-4E56-A038-CA06F788704F-default.gif

Importing and Calling a Subtemplate

Use these commands to declare XSL subtemplates that you can then call and reference in any of the XDO_ commands.

Note:

The Template Builder for Excel does not support preview for templates that import subtemplates.

To import the subtemplate, enter the command shown in the following table:

Column A Entry	Column B Entry
XDO_SUBTEMPLATE_?n? where n is a unique identifier. For example: XDO_SUBTEMPLATE_?1?	<xsl:import href="xdoxsl:///path to subtemplate.xsb?"/> For example: <xsl:import href="xdoxsl:///Shared Folders/Financial Reports/SubTemplates/MySubTemplate.xsb?"/>

To call the subtemplate, declare the cell name for which the results should be returned in Column A, then enter the call-template syntax with any other XSL processing to be performed. The commands are shown in the following table:

Column A Entry	Column B Entry
XDO_?cell object name?	<xsl:call-template name="template_name"> </xsl:call-template>

For more information on XSL subtemplates and creating the subtemplate object in the catalog, see Designing XSL Subtemplates.

Example: Importing and Calling a Subtemplate

Assume you have the following subtemplate uploaded to the BI Publisher catalog as PaymentsSummary-SubTemplate.xsb. This subtemplate evaluates the value of a parameter named pPayType and based on the value, return a string that indicates the payment type:

<?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:template>
      <xsl:template name="BRM_PAY_TYPES">
         <xsl:param name="pPayType" select="string('ALL')"/>
         <xsl:choose>
            <xsl:when test="$pPayType = '0'">UNDEFINED</xsl:when>
            <xsl:when test="$pPayType=string('10000')">PREPAID</xsl:when>
            <xsl:when test="$pPayType=string('10001')">INVOICE</xsl:when>
            <xsl:when test="$pPayType=string('10003')">CREDIT CARD</xsl:when>
            <xsl:when test="$pPayType=string('10005')">DIRECT DEBIT</xsl:when>
            <xsl:when test="$pPayType=string('10011')">CASH</xsl:when>
            <xsl:when test="$pPayType=string('10012')">CHECK</xsl:when>
            <xsl:when test="$pPayType=string('ALL')">ALL</xsl:when>
            </xsl:choose>
        </xsl:template>
</xsl:stylesheet>

In the Excel template, you have defined a field with the XDO Defined Name XDO_?TYPE?, which is populated based on the string returned from code performed in the subtemplate, as shown in the following figure:

Description of the illustration GUID-ACEC222D-71F2-47CD-949D-4088C72F3F3F-default.gif

Enter the commands shown in the following table in the Data Constraints region.

Column A Entry	Column B Entry
XDO_SUBTEMPLATE_?1?	<xsl:import href="xdoxsl:///Shared Folders/Financial Reports/SubTemplates/PaymentsSummary-SubTemplate.xsb?"/>
XDO_?TYPE?	<xsl:call-template name="BRM_PAY_TYPES"> <xsl:with-param name="pPayType" select="string('10000')"/> </xsl:call-template>

The XDO_SUBTEMPLATE_?1? function imports the subtemplate from the BI Publisher catalog.

The XDO_?TYPE? cell entry maps the results of the subtemplate processing entered in Column B.

Referencing Java Extension Libraries

You can include the reference to a Java extension library in the template and then call methods from this library to perform processing in the template.

Use the command shown in the following table to reference the Java extension libraries.

Column A Entry	Column B Entry
XDO_EXT_?n? where n is a unique identifier. Example: XDO_EXT?1?	<?namespace:xmlns:bipext="extension library"?> Example: <?namespace:xmlns:bipext="http://www.example.com/XSL/Transform/java/ example.com.xmlpublisher.reports.BIPExtension"?>

You can have multiple extension libraries defined in a single template file.

Example: Calling a Java Extension Library

Assume the extension library includes the following two methods that you want to call in the template:

• bipext:infTimeToStr()

• bipext:infStrToTimet()

After you have declared the library as shown above, specify the cell to which you want to apply the method by entering the XDO defined name in Column A and calling the function in Column B. The following table shows example commands.

Column A Entry	Column B Entry
XDO_?PARAM_START_DATE?	<xsl:value-of select="bipext:infTimeToStr(bipext:infStrToTimet((.//PARAM_START_DATE)[1],2),3)"

The entries in the XDO_METADATA sheet to declare and call the Java extension libraries are shown in the following illustration.

Description of the illustration GUID-9D713C06-B71C-4310-AD32-6D38A44AA06F-default.gif

Formatting Functions That Rely on Specific Data Attribute Values

The commands in this table require that specific formatting attributes be present in the XML data file.

A summary list of the commands is shown in the following table. See the corresponding section for details on usage.

Function	Command
Defining Border and Underline Styles	XDO_STYLE_n_?cell object name?
Skipping a Row	XDO_SKIPROW_?cell object name?

Defining Border and Underline Styles

While you can define a consistent style in the template using Excel formatting, the XDO_STYLE command enables you to define a different style for any data cell dynamically based on the XML data.

With the XDO_STYLE command you specify the cell to which to apply the style, the logic to determine when to apply the style, and the style type to apply. The style value must be present in the XML data. The following table provides examples.

Column A Entry	Column B Entry	Column C Entry
XDO_STYLE_n_?cell_object_name? For example: XDO_STYLE_1_?TOTAL_SALARY?	<xsl evaluation that returns a supported value> For example: <xsl:value-of select=".//TOTAL_SALARY/@borderStyle"/>	Style type For example: BottomBorderStyle

BI Publisher supports the normal Excel style types and values as shown in the following table:

Style Type	Supported Values (Must be in returned by evaluation in Column B)	Supported Types (Enter in Column C)
Normal	BORDER_NONE BORDER_THIN BORDER_MEDIUM BORDER_DASHED BORDER_DOTTED BORDER_THICK BORDER_DOUBLE BORDER_HAIR BORDER_MEDIUM_DASHED BORDER_DASH_DOT BORDER_MEDIUM_DASH_DOT BORDER_DASH_DOT_DOT BORDER_MEDIUM_DASH_DOT_DOT BORDER_SLANTED_DASH_DOT	BottomBorderStyle TopBorderStyle LeftBorderStyle RightBorderStyle DiagonalLineStyle

You can also set a color using one of the types shown in the following table:

Style Type	Supported Value (Must be in returned by evaluation in Column B)	Supported Types (Enter in Column C)
Normal	When you set Color Style, give the value in RRBBGG hex format, for example: borderColor="0000FF"	BottomBorderColor TopBorderColor LeftBorderColor RightBorderColor DiagonalLineColor

BI Publisher also supports the underline type with the values shown in the following table:

Style Type	Supported Values (Must be in returned by evaluation in Column B)	Supported Type (Enter in Column C)
Underline	UNDERLINE_NONE UNDERLINE_SINGLE UNDERLINE_DOUBLE UNDERLINE_SINGLE_ACCOUNTING UNDERLINE_DOUBLE_ACCOUNTING	UnderlineStyle

You can have multiple underline styles defined for a single cell.

Example: Defining Styles

To apply a style in a template, the style value must be present in the data. In this example, a border style and an underline style are applied to the DEPT_TOTAL_SALARY field shown in the Excel template.

For this example, the following data is used. Note that the DEPT_TOTAL_SALARY element in the data has these attributes defined:

• borderStyle

• underLineStyle

• borderColor

The value of each of these attributes is used to apply the defined style based on logic defined in the template.

<?xml version="1.0" encoding="UTF-8"?>

<EMPLOYEES>
  <G_DEPT>
    <DEPARTMENT_ID>10</DEPARTMENT_ID>
    <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
    <LIST_G_EMP>
      <G_EMP>
        <EMPLOYEE_ID>200</EMPLOYEE_ID>
        <EMP_NAME>Jennifer Whalen</EMP_NAME>
        <EMAIL>JWHALEN</EMAIL>
        <PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
        <HIRE_DATE>1987-09-17T00:00:00.000-06:00</HIRE_DATE>
        <SALARY>4400</SALARY>
      </G_EMP>
    </LIST_G_EMP>

    <DEPT_TOTAL_SALARY borderStyle="BORDER_DOUBLE" underLineStyle="UNDERLINE_DOUBLE_ACCOUNTING" borderColor="0000FF">4400</DEPT_TOTAL_SALARY>
  </G_DEPT>
  <G_DEPT>
    <DEPARTMENT_ID>20</DEPARTMENT_ID>
    <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
    <LIST_G_EMP>
      <G_EMP>
        <EMPLOYEE_ID>201</EMPLOYEE_ID>
        <EMP_NAME>Michael Hartstein</EMP_NAME>
        <EMAIL>MHARTSTE</EMAIL>
        <PHONE_NUMBER>515.123.5555</PHONE_NUMBER>
        <HIRE_DATE>1996-02-17T00:00:00.000-07:00</HIRE_DATE>
        <SALARY>13000</SALARY>
      </G_EMP>
      <G_EMP>
        <EMPLOYEE_ID>202</EMPLOYEE_ID>
        <EMP_NAME>Pat Fay</EMP_NAME>
        <EMAIL>PFAY</EMAIL>
        <PHONE_NUMBER>603.123.6666</PHONE_NUMBER>
        <HIRE_DATE>1997-08-17T00:00:00.000-06:00</HIRE_DATE>
        <SALARY>6000</SALARY>
        </G_EMP>
      </LIST_G_EMP>

      <DEPT_TOTAL_SALARY borderStyle="BORDER_DOUBLE" underLineStyle="UNDERLINE_DOUBLE_ACCOUNTING" borderColor="0000FF">19000</DEPT_TOTAL_SALARY>
  </G_DEPT>

...

</EMPLOYEES>

To define a style:

1. In the Excel template, assign the defined name XDO_?DEPT_TOTAL_SALARY? to the field that is to display the DEPT_TOTAL_SALARY from the data, as shown in the following figure:

   
   

   
   Description of the illustration GUID-B0FD4BD5-4CFE-4F4F-9238-EC3F3CEAAF81-default.gif

   
   

2. In the XDO_METADATA sheet, enter the following:

   • To define the top border style, use the entries shown in the following table:

     Column A Entry	Column B Entry	Column C Entry
     XDO_STYLE_1_?DEPT_TOTAL_SALARY?	<xsl:value-of select=".//DEPT_TOTAL_SALARY/@borderStyle"/>	TopBorderStyle

     The entry in Column A maps this style command to the cell assigned the name XDO_?DEPT_TOTAL_SALARY?

     The entry in Column B retrieves the style value from the attribute borderStyle of the DEPT_TOTAL_SALARY element. Note from the sample data that the value for borderStyle is "BORDER_DOUBLE".

     The entry in Column C tells BI Publisher to apply a TopBorderStyle to the cell.

   • To define the top border color, use the entries shown in the following table:

     Column A Entry	Column B Entry	Column C Entry
     XDO_STYLE_2_?DEPT_TOTAL_SALARY?	<?.//DEPT_TOTAL_SALARY/@borderColor?>	TopBorderColor

     The entry in Column A maps this style command to the cell assigned the name XDO_?DEPT_TOTAL_SALARY?

     The entry in Column B retrieves the style value from the attribute borderColor of the DEPT_TOTAL_SALARY element. Note from the sample data that the value for borderColor is 0000FF (blue).

     The entry in Column C tells BI Publisher to apply a TopBorderColor to the cell.

   • To define the underline style, use the entries shown in the following table:

     Column A Entry	Column B Entry	Column C Entry
     XDO_STYLE_3_?DEPT_TOTAL_SALARY?	<?.//DEPT_TOTAL_SALARY/@underLineStyle?>	UnderlineStyle

     The entry in Column A maps this style command to the cell assigned the name XDO_?DEPT_TOTAL_SALARY?

     The entry in Column B retrieves the style value from the attribute underLineStyle of the DEPT_TOTAL_SALARY element. Note from the sample data that the value for underLineStyle is UNDERLINE_DOUBLE_ACCOUNTING.

     The entry in Column C tells BI Publisher to apply the UnderLineStyle to the cell.

The following figure shows the three entries in the Data Constraints region:

Description of the illustration GUID-DE7587FF-3F88-469A-A2C9-A8975FC56F9C-default.gif

When you run the report, the style commands are applied to the XDO_?DEPT_TOTAL_SALARY? cell, as shown in the following figure:

Description of the illustration GUID-D8F537C9-9D6E-42D7-98C6-285D9F002507-default.gif

Skipping a Row

Use the XDO_SKIPROW command to suppress the display of a row of data in a table when the results of an evaluation defined in Column B return the case insensitive string "True".

Example entries are shown in the following table.

Column A Entry	Column B Entry
XDO_SKIPROW_?cell_object_name? For example: XDO_SKIPROW_?EMPLOYEE_ID?	<xsl evaluation that returns the string "True"/> For example: <xsl:if test="string-length(./EMPLOYEE_ID/@MANAGER) != 0"> <xsl:value-of select="./EMPLOYEE_ID/@MANAGER"/> </xsl:if>

Example: Skipping a Row Based on Data Element Attribute

In this example, the Excel template suppresses the display of the row of employee data when the EMPLOYEE_ID element includes a "MANAGER" attribute with the value "True".

Assume data as shown below. Note that the EMPLOYEE_ID element for employee Michael Hartstein has the MANAGER attribute with the value "True". The other EMPLOYEE_ID elements in this set do not have the attribute.

<?xml version="1.0" encoding="UTF-8"?>

<EMPLOYEES>
  <G_DEPT>
    <DEPARTMENT_ID>20</DEPARTMENT_ID>
    <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
    <LIST_G_EMP>
      <G_EMP>
        <EMPLOYEE_ID MANAGER="TRUE">201</EMPLOYEE_ID>
        <EMP_NAME>Michael Hartstein</EMP_NAME>
        <EMAIL>MHARTSTE</EMAIL>
        <PHONE_NUMBER>515.123.5555</PHONE_NUMBER>
        <HIRE_DATE>1996-02-17T00:00:00.000-07:00</HIRE_DATE>
        <SALARY>13000</SALARY>
       </G_EMP>
       <G_EMP>
        <EMPLOYEE_ID>202</EMPLOYEE_ID>
        <EMP_NAME>Pat Fay</EMP_NAME>
        <EMAIL>PFAY</EMAIL>
        <PHONE_NUMBER>603.123.6666</PHONE_NUMBER>
        <HIRE_DATE>1997-08-17T00:00:00.000-06:00</HIRE_DATE>
        <SALARY>6000</SALARY>
       </G_EMP>
       <G_EMP>
        <EMPLOYEE_ID>652</EMPLOYEE_ID>
        <EMP_NAME>William Morgan</EMP_NAME>
        <EMAIL>WMORGAN</EMAIL>
        <PHONE_NUMBER>219.123.7776</PHONE_NUMBER>
        <HIRE_DATE>1994-10-17T00:00:00.000-06:00</HIRE_DATE>
        <SALARY>8000</SALARY>
       </G_EMP>
      </LIST_G_EMP>
  </G_DEPT>

...

</EMPLOYEES>

To suppress the display of the row of the employee data when the MANAGER attribute is set to "True", enter the entries shown in the following table in the Data Constraints section.

Column A Entry	Column B Entry
XDO_SKIPROW_?EMPLOYEE_ID?	<xsl:if test="string-length(./EMPLOYEE_ID/@MANAGER) != 0"> <xsl:value-of select="./EMPLOYEE_ID/@MANAGER"/> </xsl:if>

The output from this template is shown in the following illustration. Note that the employee Michael Hartstein is not included in the report.

Description of the illustration GUID-2BCEDD93-FE0B-4DAF-8E40-EBC4A5266117-default.gif

Grouping Functions

Use the functions shown in this table to create groupings of data in the template.

Function	Command
Grouping the Data	XDO_GROUP_?group element?
Regrouping the Data	XDO_REGROUP_?

Grouping the Data

Use the XDO_GROUP command to group flat data when the layout requires a specific data grouping, for example, to split the data across multiple sheets.

Example entries are shown in the following table:

Column A Entry	Column B Entry	Column C Entry
XDO_GROUP_?group element? For example: XDO_GROUP_?STATE_GROUP?	<xsl beginning groupng logic/> For example: <xsl:for-each-group select="current-group()" group-by="./STATE"> <xsl:for-each-group select="current-group()" group-by="./RESOURCE_NAME"> <xsl:for-each select="current-group()">	<xsl ending groupng tags/> For example: </xsl:for-each> <xsl:for-each-group> <xsl:for-each-group>

Define the XSL statements to be placed at the beginning and ending of the section of the group definition marked up by XDO_?cell object name?. You can mark multiple groups nested in the template, giving each the definition appropriate to the corresponding group.

Handling the Generated XDO Define Names in Nested Groups

When XDO_GROUP_? is used in a nested group, the ranges of XDO define names in the final report become meaningless. In this case, do not refer to the define names in formulas in the final report. You can disable the XDO markup activity in the final report using the command XDO_MARKUP_?.

The following table shows the usage of XDO_MARKUP_? in the XDO_METADATA sheet:

Column A Entry	Column B Entry
XDO_MARKUP_?	"false" or "FALSE" (The cell must be formatted as Text in the Excel Format Cells dialog.)

In addition, if your template includes a large number of defined names and these are used in multiple levels of nested groups, Excel may not be able to handle the number of generated defined names. In this case, use the XDO_MARKUP_? command to disable markup for the generated report.

When set to false, BI Publisher does not produce any defined names for any result produced by XDO_GROUP_?

Regrouping the Data

The XDO_REGROUP regroups the data by declaring the structure using the defined names.

The XDO_REGROUP logic is a shortened form of the XDO_GROUP logic and does not require the XSLTcoding requirements in the XDO_METADATA sheet. The definition must therefore be directly on XDO_REGROUP_? define names, or on any other definition on the XDO_METADATA sheet. Entries are shown in the following table.

Column A Entry

Column B Entry

XDO_REGROUP_?

XDO_REGROUP_?UniqueGroupID?levelName?groupByName?sortByName?sortByName?sortByName? where UniqueGroupID is the ID of the group. It can be the same as the levelName or you can assign it unique name. levelName is the XML level tag name in the XML data file or XDO_CURRGRP_ that represents the current-group() in the context of nested grouping. XDO_CURRGRP_ should be used for all inner groups when more than one nesting group exists in your template. groupByName is the field name that you want to use for the GroupBy operation for the current group. This name can be empty if the XDO_REGROUP_? command is used for the most inner group. sortByName is the field name that you want to sort the group by. You can have multiple sortBy fields. If no sortByName is declared, then the data from the XML file is not sorted.

The next three tables show an example of how to create three nested groupings.

Column A Entry	Column B Entry
XDO_REGROUP_?	XDO_REGROUP_?PAYMENTSUMMARY_Q1?PAYMENTSUMMARY_Q1?PAY_TYPE_NAME?

In the definition shown in the previous table, the most outer group is defined as PAYMENTSUMMARY_Q1, and it is grouped by PAY_TYPE_NAME

Column A Entry	Column B Entry
XDO_REGROUP_?	XDO_REGROUP_?COUNTRYGRP?XDO_CURRGRP_?COUNTRY?

The definition shown in the previous table creates a second outer group. The group is assigned the name COUNTRY_GRP and it is grouped by the element COUNTRY.

Column A Entry	Column B Entry
XDO_REGROUP_?	XDO_REGROUP_?STATEGRP?XDO_CURRGRP_?STATE?

The definition shown in the previous table creates the inner group STATEGRP and it includes a sortByName parameter: STATE.