Creating Reports that include Rich Text Editor Data

This chapter provides an overview of rich text editor data in BI reporting and discusses how to:

• Configure RTE on page for BI reporting.

• Create template that includes rich text editor data.

• Use Query or Connected Query as a data source.

• Use XML file as a data source.

Understanding Rich Text Editor Data in BI Reporting

The rich text editor control extends the capability of a long edit box. It allows for the rich formatting of text content, including common structural treatments such as lists, formatting treatments such as bold and italic text, and so forth.

The data entered in a rich text enabled long edit field is stored in the PeopleSoft database as formatted HTML data. BI Publisher reports do not directly support HTML formatted fields. In order to transform the HTML formatted data, a special style sheet needs to be incorporated into your RTF template that will handle the conversion.

PeopleTools supplies a sub-template HTML_SUB that can be added to an RTF template to transform the HTML formatted data from the database into your report.

Sub-template HTML_SUB is used to transform rich text area in the BI report

Note. The XSL-FO (XSL Formatting Objects) style sheet used by PeopleSoft BI Publisher was developed by a third party Antenna House, Inc (http://www.antennahouse.com) in 2001,2002. The original style sheet has been modified by PeopleSoft BI Publisher team to be used as a sub-template in the PeopleSoft environment. You can create your own customized version of the XSL sub-template to reflect the predefined hardcoded styles you want to use.

Requirements for Reports Using RTE Fields

In order to create reports that include RTE data fields:

1. User data must be input using RTE Tool supplied by PeopleSoft.

2. All RTE enabled database fields should be of type LONG CHARACTER or CHARACTER.

3. Report needs to use an RTF template.

4. The RTF template needs to reference the HTML_SUB template.

   Note. If you have created a customized sub-template, reference your customized XSL sub-template.

5. The report needs to be based on XML file, Query or Connected Query data source.

6. The field that contains the RTE formatted data should not use a CDATA xml section.

7. Special Characters and Images are not supported in the HTML data.

8. The BI Publisher report output can be HTML, PDF, RTF or XLS.

   Note. There may be slight differences in appearance based on output type, therefore the developer should test all formats that will be available for the report.

Configuring RTE on Page for BI Reporting

In order to use rich text in a BI Report, the rich text must be entered from a Rich Text long edit box on a PeopleSoft page.

PeopleSoft delivers a configuration file PT_RTE_CFG_PTXP that hides the Image and Special Characters buttons in the RTE toolbar.

To enable rich text editor functionality for the long edit box:

1. Add the long edit box to a page.

2. Double-click the control.

   Alternatively, you can highlight the control and select Page Field Properties from the Edit menu or the pop-up menu, which is activated by right clicking the control.

3. Access the Options tab and select Enable Rich Text Editor.

4. Select the Configuration Setting Id PT_RTE_CFG_PTXP or any other configuration file that hides Special Character and Image buttons.

The toolbar displayed on the page will not allow Images and Special Characters.

See Creating Custom Configuration Files.

Creating Template that Includes Rich Text Editor Data

In order to incorporate rich text fields into your PeopleSoft BI reports, you must use an RTF template and the template needs to call the sub-template HTML_SUB.

This section discusses:

• Loading Sample Data containing an RTE field

• Including the HTML_SUB Template

Loading Sample Data Containing an RTE Field

RTE generated HTML text could have HTML entries representing HTML white space that looks like   . This symbol ( ) is not recognized as an XML/ XSL entity. The sub template HTML_SUB converts these symbols to XML compatible entries. When you run BI Publisher reports using Process Scheduler or Application Server the conversion is performed and the report is created.

When you are creating your RTF template and load sample data containing this symbol ( ), you will receive an error message. This error can be ignored and preview report will be generated.

If an application developer wants to eliminate this template design problem, a PeopleCode statement to substitute   characters with XML compatible white space symbols should be placed in the code prior to writing the formatted string to the database:

RICHTXTEXT.COMMENTS = Substitute (RICHTXTEXT.COMMENTS, " ", " ");   

In this code example RICHTXTEXT.COMMENTS is the rich text enabled record.field.

Including the HTML_SUB Sub-Template

To include the HTML_SUB sub-template:

1. Create a new document in Microsoft Word.

2. To import the HTML_SUB template, place the following syntax at the top of the primary template file.

   <?import:psxmlp://HTML_SUB?>

3. Design your template.

4. Right-click the field that contains the RTE data, and select BI Publisher, Properties.

5. Enter the code to apply the template:

   <xsl:apply-templates select="field_name"/>

   where field_name is the name of the RTE field.

6. Save the RTF template.

You will not be able to preview the template in Microsoft Word. To preview, you will need to modify import statement in step 2 to select the sub-template file stored in the file system .

<?import:file:///C:\PeopleSoft_docs\PSXP_xhtml2fo.xsl?>

Note. You will need to download the PSXP_xhtml2fo.xsl from the content library to a local directory, in this example C:\PeopleSoft_docs was used.

See Testing a Sub-Template in Microsoft Word.

Using Query or Connected Query as a Data Source

By default, Query and Connected Query use CDATA xml sections for all character fields. In order to display rich text format, the CDATA needs to be removed from the RTE field. The Report Property psxp_nocdatafields must be applied to all RTE enabled fields in order for the formatting to be preserved.

To apply the psxp_nocdatafields property:

1. Open the report definition based on the Query or Connected Query data source.

2. Access the Properties page.

3. Select Property Group PeopleTools Settings.

4. In the Text field for psxp_nocdatafields, enter the RTE enabled fields names.

   If there is more than one field, the field names can be combined in a single string using a typical delimiter. Possible delimiter values are:

   • White space

   • Tab

   • Comma

   • Period

   • Colon

   • Semicolon

   • Carriage return

   • Line feed

5. Save the report definition.

Using XML File as a Data Source

XML files can be created using various methods with assistance of SQL object, File Layout object, XMLDoc, Rowset, SQR and so forth. Some PeopleTools objects, such as File Layout and XMLDoc escape XML tags. The RTF processor is not able to recognize the escaped tags. Escaped tags, include:

• <

• >

• &

• "

• &nbsp

It is necessary to add PeopleCode substitute statements which perform unescape functionality and restore the original XML/HTML tags.

/* open the file - here &XMLFilePath is the full path to the file with RTE output⇒
 */     
Local File &MYFILE = GetFile(&XMLFilePath, "E", "UTF-8", %FilePath_Absolute;
If &MYFILE.IsOpen Then
   /* Get the string from the file */
   Local String &myRawTxt = &MYFILE.GetString();
   /* substitute escape char */     
   If All(&myRawTxt) Then
      &myRawTxt = Substitute(&myRawTxt, "&lt;", "<");
      &myRawTxt = Substitute(&myRawTxt, "&gt;", ">");
      &myRawTxt = Substitute(&myRawTxt, "&quot;", Char(34));
      &myRawTxt = Substitute(&myRawTxt, "&amp;", "&");
      &myRawTxt = Substitute(&myRawTxt, "&nbsp;", "&#160;");
      &MYFILE = GetFile(&XMLFilePath, "W", "UTF-8", %FilePath_Absolute);
      If &MYFILE.IsOpen Then
      /* Rewrite the file using the modified string */
         &MYFILE.WriteString(&myRawTxt);
         &MYFILE.Close();
      End-If;
    End-If;
End-If;