Using SQR for PeopleSoft in Global Implementations

Init_Report_Translation

Call the Init_Report_Translation procedure from your SQR program before using any of the String table information. Typically, you should call Init_Report_Translation in the Init-Report section of your SQR program. Init_Report_Translation takes two parameters:

If you want to change languages during the processing of an SQR report (for example, if you want each page of the report to be in a different language), you can call Init_Report_Translation multiple times within a single SQR program, each time passing a new $Report_Language value.

Get_Field_Information

Call the Get_Field_Information procedure for each string that you want to retrieve from the Strings table. It retrieves the label or string table entry for the field specified and places it in a report variable. You can then print the contents of this variable on your report as a label, column heading, or free text. Get_Field_Information takes four parameters:

Append_Report_Translation

If your SQR program uses strings from more than one Strings table program ID, call Append_Report_Translation to add the strings from another program ID to the initialization array created by Init_Report_Translation. This function is particularly useful if you have a set of strings that is used across many of your SQR programs. You can group these strings under a generic program ID and use them in multiple SQR programs.

Append_Report_Translation takes a single argument, $Report_ID. It assumes the same language that was specified in Init_Report_Translation. It must be called after Init_Report_Translation.

Add_Report_Translation

The Add_Report_Translation procedure calls Init_Report_Translation or Append_Report_Translation, depending on whether the Strings table has been initialized. It takes the same arguments as Init_Report_Translation. If Init_Report_Translation has not yet been called during the processing of this SQR program, this function calls it, passing both parameters. If Init_Report_Translation has already been called, Add_Report_Translation calls Append_Report_Translation, passing only the $Report_ID parameter.

This function is useful in your own SQC files if you cannot be certain that the calling SQR program has already initialized the Strings table. The function ensures that the table is initialized or appended correctly.

Sample Strings Table Enabled SQR Program

The following sample code demonstrates how to use the Strings table to retrieve string values in SQR, using the procedures described in the preceding sections.

!************************************************
!  SAMP001:  Report on database's base language *
!************************************************
#include 'setenv.sqc'    !Set environment
begin-report
  do Init-Report
  do Process-Main
  do Reset
end-report
begin-heading 6
do Get_Field_Information ('SAMP001',    'REPORT_TITLE', $REPORTTITLE,   #DWRT)
do Get_Field_Information ('SAMP001',    'EXPLAIN_TEXT', $EXPTEXT,       #DWET)
PRINT $REPORTTITLE       (1)    CENTER
PRINT $EXPTEXT           (+2,1)
end-heading
begin-procedure Init-Report
move 'SAMP001' to $ReportID
move 'ENG' to $Language_cd
do Init_Report_Translation ($ReportID, $Language_cd)
do Append_Report_Translation ('GEN')
end-procedure
begin-procedure Process-Main
do Get_Field_Information ('GEN',        'BASELANGUAGE', $BASELANGUAGE,  #DWBL)
do Get_Field_Information ('GEN',        'ENDOFREPORT',  $ENDOFREPORT,   #DWER)
begin-SELECT 
LANGUAGE_CD &Base_Language
   let $langlabel = $BASELANGUAGE || ':'
   print $langlabel  (+1,1)
   let #fieldpos = #DWBL + 3
   print &Base_Language (0,#fieldpos)
FROM PSOPTIONS
end-SELECT
print $ENDOFREPORT      (+4,1)
end-procedure
#Include 'reset.sqc'     !Reset printer procedure
#Include 'sqrtrans.sqc'  !SQR Strings Table procedures

Advanced PSSQR.INI/UNX Settings

The following parameters under the [Environment: Common] or [Environment: <Database type>] sections of the pssqr.ini/unx file control the character set SQR uses for specific operations. The ENCODING parameter sets the default encoding for all types of operations. You can, however, control encodings for each operation independently by adding the parameters in the [Environment: Common] section of the file.

ENCODING
ENCODING-SQR-SOURCE
ENCODING-FILE-OUTPUT
ENCODING-FILE-INPUT
ENCODING-REPORT-OUTPUT
ENCODING-CONSOLE

If an individual encoding parameter is explicitly included, this specific setting overrides the default encoding specified by the ENCODING parameter.

• The ENCODING-SQR-SOURCE parameter specifies the encoding in which the SQR source (*.SQR, *.SQC) files are encoded.

• The ENCODING-FILE-OUTPUT and ENCODING-FILE-INPUT parameters control the character set that is used to read and write files using the SQR OPEN command.

  If no character set is specified explicitly in the OPEN command, the character set specified in these parameters is used to read or write the file. If SQR detects a byte-order mark (BOM) at the top of file, it always reads as a Unicode file, regardless of this setting.

• The ENCODING-REPORT-OUTPUT parameter determines the encoding used for report output types LP, HP, PS, and CSV.

  The parameter should be set to a character set that contains all the characters you expect to print using SQR and, if you intend to print the output on a printer, it should also be set to the character set that the printer supports. Output in SPF, HTML, Enhanced HTML, and PDF format is not controlled by this parameter. SPF HTML, and Enhanced HTML always use Unicode for their output, and PDF output is controlled exclusively by the font configuration.

• The ENCODING-CONSOLE parameter determines the character set that is used to write progress and other messages to the console during an SQR run.

  In Microsoft Windows, the encoding used by DOS consoles, such as the one that is used by SQR, is known as an OEM encoding; this is often different from the character set that is used by Microsoft Windows. See Microsoft's web site, OEM Code Pages, for a list of Microsoft Windows OEM code pages. For Unix/Linux systems, this setting should match the character set that is supported by your terminal device.

• If the ENCODING parameter is not set, SQR determines the encoding to use based on the machine locale.

• If the same parameter is not set in the [Environment: Common] and [Environment: <Database Type>] sections of the pssqr.ini/unx file, the configuration in the [Environment: <Database Type>] section overrides the configuration in the [Environment: Common] section.

Setting Up Fonts

SQR for PeopleSoft represents fonts internally as numbers, and the pssqr.ini/unx file controls the mapping between the font numbers for some printer types. The [Fonts] section in the pssqr.ini/unx file controls the font mappings of the following output types:

• SPF Viewer

• WP (Microsoft Windows Printer)

• Enhanced HTML

By default, this section is not configured with language-specific fonts. This is because Microsoft Windows and HTML browsers have a font linking mechanism that allows them to select an appropriate language-specific font automatically when the font specified does not have characters to show. However, Microsoft Windows and the browser's font linking works differently based on versions, availability of fonts on the system, and the configuration of Microsoft Windows and the browser. If you experience problems showing characters for these output types, you should try the configuration described in this section

To set up fonts:

1. Open the appropriate pssqr.ini/unx file for your language, located in PS_HOME\sqr.

2. In the [Fonts] section, the following default values appear.

   Use the chart to replace each of the entries for your target language.

   3=Courier New,fixed
   300=Courier New,fixed,bold
   4=Arial,proportional
   400=Arial,proportional,bold
   5=Times New Roman,proportional
   500=Times New Roman,proportional,bold
   6=AvantGarde,proportional
   8=Palatino,proportional
   800=Palatino,proportional,bold
   11=Symbol,symbol
   900=unknown,proportional
   901=Times New Roman,proportional
   28825=MS UI Gothic,proportional
   

Setting Up PDF Fonts

For PDF output, SQR for PeopleSoft has an internal font linking mechanism by which you can link up to 10 fonts for a single font number. This allows you to get character coverage that is not possible by using single font. With this feature, you can print reports containing multiple languages without rewriting your program to show each language in a different font. PeopleSoft delivers the pssqr.ini/unx file with world-ready configuration; therefore, you usually do not need to modify the pssqr.ini/unx file to print non-Western European languages. Many major business languages can be printed in PDF independent of the user's language settings on the My Personalization page. For example, you can output Greek data in PDF even when your My Personalizations language is set to English.

Although it is possible to print Chinese, Japanese, or Korean text in a PDF report without modifying the pssqr.ini/unx configuration, printing Chinese, Japanese, and Korean text in a single report requires special configuration. Font linking data is configured in the pssqr.ini/unx file and is transparent to the user. Depending on your reporting requirements, you can add or remove fonts from the delivered configuration, and you can control which actual font to use down to a single character level. This section explains how to add fonts for PDF output.

The [PDF Fonts] section in the pssqr.ini/unx file supports multiple fonts mapped to a single font number.

The [PDF Fonts: Exclusion Ranges] section of the file specifies exclusion ranges for fonts listed in the [PDF Fonts] section. If an exclusion range is set, then when a character is covered by a font, that font is not used to print the character. Exclusion ranges are set in Unicode format, in hexadecimal or decimal. The base configuration does not contain an exclusion range.

The [TrueType Fonts] section of the file specifies the mapping from TrueType font names used in the [PDF Fonts] configuration section, along with the physical file path of the font on the operating system. For TrueType collection (.ttc) files, the font directory number should also be specified (in the format font name=file path, directory number.

The font path is the directory where the font resides. The default is SQRDIR. On Microsoft Windows, the font directory is looked up as well. Fonts residing in other directories must be specified by their full physical pathnames.

The [PDF Fonts] information in the pssqr.ini/unx file is language-specific for Chinese, Japanese, and Korean languages; the information is the same for other supported languages due to Unicode characteristics. If you want to add another language (for example, Russian), you should refer to the documentation for adding new languages.

See the PeopleTools Installation for your database platform.

See Understanding the Addition of New Language Definitions.

See SQR Language Reference for PeopleSoft.

Sample Steps for Adding a Font for PDF Output: User Defined Font for Japanese

For computing in the Japanese language, it is common to use user-defined characters. A user-defined character is a character that is not encoded in standard character sets like JIS X 0208 or Unicode, and sometimes variants of glyphs existing in standard character sets. This kind of character is often used for a person's name. Unicode has space allocated to encode such user-defined characters, and users can create fonts that include characters not defined in Japanese fonts.

On Microsoft Windows, users can create user-defined fonts using a program called EUDCEDIT to design and define private characters. This program creates a TrueType font file named "eudc.tte" under the Microsoft Windows font folder. As an example of adding fonts for PDF output from SQR for PeopleSoft, perform the following steps to add the "eudc.tte" font to the PeopleSoft-delivered configuration file for Japanese, pssqrjpn.ini:

1. Open the pssqrjpn.ini file from PS_HOME\sqr and add the following line under [TrueType Fonts] section:

   [TrueType Fonts]
   Font Path=...
   Cumberland=...
   GaijiFont=eudc.tte

   As explained in this section, you do not need to specify a full path for the font residing in the Microsoft Windows font folder, and you can specify a name different from the actual name of the font ("GaijiFont" means a user defined font in Japanese. The actual font name that "eudc.tte" itself has is "EUDC").

2. Under the [PDF Fonts] section, add "GaijiFont" at the end of the font list.

   For example, for font number 3:

   3=Courier,HeiseiKakuGo-W5,Cumberland,MHei-Medium,STSong-Light,HYGoThic-Medium,
   Angsana,GaijiFont

3. Define exclusion ranges in the [PDF Fonts: Exclusion Ranges] section, to exclude higher priority fonts to show characters in the Unicode private use range where characters in eudc.tte are defined.

   Chinese fonts have glyphs defined in the Unicode private use range mainly to include characters defined in national character standards like GBK but not defined in earlier versions of Unicode. Thai fonts also have Thai presentation form characters mapped in the Unicode private use range. As we are working on a Japanese configuration and Chinese characters and Thai presentation forms are rarely needed in a Japanese context, we will configure these fonts not to print characters in the Unicode private range, and private range characters are always printed using "GaijiFont," which we just added. The Unicode private use range is from 0xE000 to 0xF8FF. Chinese fonts are third and fourth in the font link list, and the Thai font is seventh. Therefore, the exclusion definition looks like the following (exclusion range setting for second font comes from original configuration of pssqrjpn.ini, that excludes extended Latin characters from Japanese font):

   3=,0x0000-0x02FF,,0xE000-0xF8FF,0xE000-0xF800,0xE000-0xF800,,

Considerations for Printing Chinese, Japanese, and Korean Text in a Single PDF Output

Unicode application printing of Chinese, Japanese, and Korean text in a single context requires special consideration in the use of fonts, because Unicode encodes ideographs used in Chinese, Japanese, and Korean languages in unified codepoints. A Unicode character is unique in meaning, but the glyph (the shape of the character) might be different depending on the language context. A commonly used technique in resolving this glyph difference is to use a language-specific font for Chinese, Japanese, and Korean, based on external context.

The PeopleSoft system delivers different font linking configurations for Chinese, Japanese, and Korean languages in language-specific pssqr.ini/unx files. In the Simplified Chinese configuration, the Simplified Chinese font is given higher priority than other Chinese, Japanese, or Korean fonts, so that a Simplified Chinese glyph is used for most Chinese ideographs. At runtime, PeopleSoft Process Scheduler picks up language-specific pssqr.ini/unx files with appropriate font linking data for the language, based on the user's My Personalization language setting. If you set your language as Traditional Chinese, the configuration file that uses the Traditional Chinese font for the majority of ideographic characters will be picked up. It is recommended that you set up the language in the My Personalization page when you are printing reports that include Chinese, Japanese, and Korean data. If you do not use any Chinese, Japanese, and Korean languages for the My Personalization page, the default configuration is used and in this case the printed glyph image might be different from what you expect to see.

If you have a reporting requirement to print a mix of Chinese and Japanese in a single report and you need to show each language in the correct glyph image for that language, you will need to use the ALTER-PRINTER command to programmatically change the font in your SQR program based on external data, such as the Language Code of the data. For this purpose, pssqr.ini/unx files are provided with the following font numbers, each of which uses fonts specific for each Chinese, Japanese, and Korean locale.

• 28825, 28752 (Japanese).

• 37110, 37058 (Simplified Chinese).

• 33269 (Traditional Chinese).

Setting Up Files for PCL and Line Printing

Most of the configurations for printing international text from SQR for PeopleSoft are performed by setting options in the pssqr.ini/unx file. However, for some of the printer-specific settings, you must set parameters in SQR for PeopleSoft programs because of the SQR for PeopleSoft syntax.

For example, SQR for PeopleSoft enables users to control printer parameters for PCL format in DEFINE-PRINTER or ALTER-PRINTER commands. PeopleTools provides default setup values for PCL format in three SQC files (ptset01.sqc, ptset02.sqc and ptset02a.sqc). If you intend to print languages in PCL format other than those covered by Latin1, you must add printer setup parameters to the SQC files.

Before you perform the configuration tasks described in this section, make sure that you have properly configured your printers to print in the target language.

If your printer does not have the fonts for the languages you want to print, you might need to purchase fonts in ROM from your printer vendor and install them on your printer.

The parameters you set for PCL printing must be consistent with the ENCODING parameters set in the pssqr.ini/unx file. You must set up the ENCODING parameters for each language before you set the parameters for PCL printing.

To set up PCL printing parameters for Japanese:

1. Open setenv.sqc file under PS_HOME\sqr.

2. Uncomment the following parameter:

    #define PRINT_JAPANESE

3. Save and close the file.

To set up PCL printing parameters for languages other than Japanese:

1. Open the ptset01.sqc file located under PS_HOME\sqr.

2. Between the declare printer DEFAULT-HP and end-declare statements, add the following information with the appropriate values:

   font
   symbol-set
   

   The values you set differ from language to language. The following table shows sample values for several languages commonly used by PeopleSoft customers.

   Language	Font	Symbol Set
   Central/Eastern European	No need to set.	2N
   Traditional Chinese	33269	18T
   Simplified Chinese	37058	18C

   For example, if you are setting ptset01.sqc for Simplified Chinese printing, try the following setting:

   declare-printer DEFAULT-HP
   #ifdef PRINT_JAPANESE
   		init-string=<27>&t31P
   		font=28825
   		symbol-set=19K
   #endif
   		font=37058symbol-set=18C
   		point-size=7.2
   		pitch=17
   end-declare
   

3. Under the declare printer DEFAULT-LP statement, locate the init-string parameter.

   Modify the init-string parameter based on the language to print. The following example shows sample values for HP LaserJet printer’s line printer mode. Consult your printer manual for correct values for this parameter.

   Language	init-string
   Eastern European	<27>E<27>(2N<27>&l0O<27>&l6C<27>&l0E<27>&l95F<27>(s16.66H<27>&k2G
   Traditional Chinese	<27>E<27>(18T<27>&l0O<27>&16C<27>&10E<27>&195F<27>(s16.66H<27>&k2G<27>(s1p7.25v0s0b33269T
   Simplified Chinese	<27>E<27>(18C<27>&l0O<27>&16C<27>&10E<27>&195F<27>(s16.66H<27>&k2G<27>(s1p7.25v0s0b37058T

4. Save and close the file.

5. Open ptset02.sqc from PS_HOME\sqr and repeat steps 2 through 4.

6. Open ptset02a.sqc PS_HOME\sqr and repeat steps 2 through 4.

Setting up Files for PostScript Output

In addition to PCL and line printer format, SQR for PeopleSoft supports output in PostScript format, which can be printed with a printer that has PostScript interpreter. However, you configure fonts in a setup file provided for PostScript printing.

The parameters you set for PostScript printing must to be consistent with the ENCODING parameters you set in the pssqr.ini/unx file.

By default, the setup file provided for PostScript printing is configured for printing Western European languages. Depending on the encoding parameter in the pssqr.ini/unx file, you must either “re-encode” fonts, or use different fonts that support specific languages other than those set in the configuration file by default (you may need to use printer pre-installed fonts that support specific languages, or install external fonts on your printers). Consult your printer manual for the character sets your printer supports.

To set up files for PostScript output:

1. Open the postscri.str file from PS_HOME\bin\sqr\db_platform\binw or from the directory where the SQR executable resides.

2. If you are using select character sets, such as Latin9, it is possible to print the entire repertoire of the character set just by re-encoding PostScript fonts.

   The PeopleSoft system delivers PostScript code and re-encoding data for Microsoft Windows CP1252 and CP1250 on Microsoft Windows, and Latin1, Latin9, and Latin2 on Unix/Linux. The default is CP1252 on Microsoft Windows and Latin1 on Unix/Linux. If you are using other character sets, the PeopleSoft system provides re-encoding data and you will need to modify part of the setup file. For example, the following example is for using the Latin9 character set on Unix/Linux platforms:

   Locate the following section:

   % ISO Latin1
   /CODE []
   160 /space
   ...

   Comment out the “CODE” section entirely, by adding a percent sign (%) at the beginning of each line.

   Locate the following section:

   % ISO Latin9 
   % (enable this array when you set ENCODING/ENCODING-REPORT-OUTPUT=ISO-8859-15
   %  and comment out above array for ISO Latin1)
   % /CODE [
   % 160 /space
   …
   

   Remove the percent signs in front of each line for the entire “CODE” section.

   Note: If you plan to use CP1250 on Microsoft Windows or Latin2 on Unix/Linux, you will need to ensure that fonts installed on printer have the characters needed for these character sets. Depending on the printer and the fonts installed, you may not be able to print the entire repertoire of these Eastern European character sets using provided re-encoding data. Note also that if you plan to use character sets other than those discussed previously in this section, you will need to ensure that the printer has fonts supporting the specific character set you want to use. You may need to install additional fonts, and may also need to re-encode the fonts by adding your own font re-encoding data.

3. Some other character sets, including character sets commonly used for Japanese, Korean, and Chinese, can be printed by using PostScript fonts supporting the specific language. You can replace the font names in the setup file in the following way:

   Locate the following section:

   /Fonts [   % Array of point sizes and font names

   Replace each font with the name of the appropriate font installed on your printer that can print the target language. For CJK languages (Chinese, Japanese, and Korean), PostScript printers accept the font names in the following format:

   (Font Name)-(CMap name)

   Font Name represents the name of the font installed on your printer that supports the language that you want to print. CMap is a file the PostScript printer used to find the glyph that corresponds to the codepoint passed to the printer. You need to use the CMap that matches the encoding specified in the ENCODING parameter in the pssqr.ini/unx file.

   The following example shows setup values for each of the CJK languages. Supported font names and CMap files may vary depending on your printer. For details, refer to your printer documentation.

   Language	Report Output Encoding	Serif Typeface	San Serif Typeface
   Japanese	SJIS	Ryumin-Light-RKSJ-H	GothicBBB-Medium-RKSJ-H
   Traditional Chinese	Big5	MSung-Light-B5-H	MHei-Medium-B5-H
   Simplified Chinese	CP936	STSong-Light-GBK-EUC-H	STHeiti-Regular-GBK-EUC-H
   Korean	CP949	HYSMyoeongJo-Medium-KSCms-UHC-H	HYGoThic-Medium-KSCms-UHC-H

   If you do not need to re-encode the fonts you have set, you must comment out the following section entirely by adding a percent sign (%) in front of each line. (For CJK character sets, you do not need to re-encode fonts; however, you might need to do so for other languages.)

   %
   % Re-encode all the fonts except for symbol/dingbats
   % (do not re-encode CJK fonts - if you intend to use CJK fonts comment
   %  out the below section entirely)
   %
   /Courier /Courier CODE ReEncode
   /Courier-Bold /Courier-Bold CODE ReEncode
   /Helvetica /Helvetica CODE ReEncode
   /Helvetica-Bold /Helvetica-Bold CODE ReEncode
   …
   

4. Save and close the file.

Limitations in Printing International Text from SQR for PeopleSoft

The following limitations apply when printing international text from SQR for PeopleSoft:

• SQR for PeopleSoft does not support languages that require bi-directional text rendering, such as Arabic and Hebrew.

• The default configuration of report formats other than PDF, SPF, WP, HTML, and Enhanced HTML is to use non-Unicode character sets (ISO or commonly used national standard character sets on Unix/Linux, and the Microsoft Windows character set on Microsoft Windows).

  The ability to print language data for LP, HP, PS, CSV, and HT output types is limited by the character set used for output. For example, you can print Western European languages like English, French, Spanish, and Portuguese using the Latin1 character set (which is used in pssqr.unx), but you cannot add Polish data using Latin1 because the Polish language uses characters not encoded in the Latin1 character set. You can configure the pssqr.ini/unx files to generate output types other than HT in Unicode-based character sets like UCS-2 or UTF-8, but in these cases you need to ensure that your printer or viewer application supports Unicode natively.

• If you intend to print output using the LP, HP, or PS format, SQR for PeopleSoft's ability to print international characters is also limited by the fonts installed on the printers. For example, you can print Japanese on a PostScript printer if a Japanese font like Ryumin-Light is installed on the printer. However, if your printer does not natively provide font support, the printer output with international text may not be printed as intended. Unlike with PDF output, SQR for PeopleSoft currently does not support downloading host-based fonts, including True Type fonts, to the printer as part of the print job. Printing from Adobe Reader or SPF Viewer is not affected by this limitation.

System Variables to Check Encoding Settings

You can reference the values set for the ENCODING parameters in pssqr.ini/unx from your SQR for PeopleSoft programs using the following system variables:

• $sqr-encoding

  Stores value set to the ENCODING parameter of pssqr.ini/unx. If no value is set for this parameter, SQR automatically determines the appropriate encoding for input and output based on the operating system locale and stores values in this variable.

• $sqr-encoding-database-api

  Stores value set to ENCODING-DATABASE-API parameter of pssqr.ini/unx file. Except for DB2 UDB for Linux, Unix, and Microsoft Windows, the value for this parameter is automatically determined by the system and cannot be overridden by editing pssqr.ini/unx. In this case, this variable stores the encoding value automatically determined and used by system. For example, on an Oracle platform, this variable value is always UTF-8.

• $sqr-encoding-file-input

  Stores value set to ENCODING-FILE-INPUT parameter in pssqr.ini/unx file. If no value is set for this parameter, the input files are considered to be encoded in the encoding specified in the ENCODING parameter and this variable stores the same value as $sqr-encoding.

• $sqr-encoding-file-output

  Stores value set to ENCODING-FILE-OUTPUT parameter in pssqr.ini/unx file. If no value is set for this parameter, the same encoding set in the ENCODING parameter is used for file output encoding and this variable stores the same value as $sqr-encoding.

• $sqr-encoding-report-output

  Stores value set to ENCODING-REPORT-OUTPUT parameter in pssqr.ini/unx file. If no value is set for this parameter, the same encoding set in the ENCODING parameter is used for report output encoding and this variable stores the same value as $sqr-encoding.

• $sqr-encoding-source

  Stores value set to ENCODING-SQR-SOURCE parameter in pssqr.ini/unx file. If no value is set for this parameter, SQR source files are considered to be encoded in the character set encodings specified in the ENCODING parameter and this variable stores the same value as $sqr-encoding.

• $sqr-encoding-console

  Stores value set to ENCODING-CONSOLE parameter in pssqr.ini/unx file. If no value is set for this parameter, the same encoding set in the ENCODING parameter is used for console output encoding and this variable stores the same value as $sqr-encoding.

  Note: The system variables are read-only and cannot be overwritten at runtime. They can be used with functions and commands that take encodings as parameters, such as substrt(), lengtht(), and open.

String Length

When you work with strings in SQR, you must consider three different ways of measuring string length:

• The number of characters in the string.

• The number of print columns occupied by these characters.

  For example, characters from the Latin alphabet normally require one print column; characters in Japanese often require two.

• The number of bytes used to store the character.

  SQR uses the Unicode UCS-2 method of encoding characters, which means that every character occupies two bytes; however, your database may use a different encoding that requires a different number of bytes for each character.

SQR provides the following functions to help you manage string length according to each of these criteria.

• Length() and Substr().

  Length() and Substr() deal with the number of characters in a string. As the PeopleSoft system field lengths (as defined in Application Designer) are character-based, the Length( ) and Substr( ) functions are useful for calculating the length of the string as it is stored in the database.

  For example, the following code determines whether string &abc will fit into a database field that is 10 characters long. If the string won’t fit into the field, the code truncates the string to use only the first 10 characters.

  If length(&abc) > 10 then
  &abc = substr(&abc,1,10)
  End-if

• Lengthp() and Substrp().

  Lengthp() and Substrp() deal with the number of print columns required to print the character using a monospace (nonproportional) font.

  For example, the following code determines whether string &abc will fit into a print area that is 10 columns wide. If the string won’t fit into the print area, the code truncates the string to use only the first 10 columns of characters.

  If lengthp(&abc) > 10 then
  &abc = substrp(&abc,1,10)
  End-if

• Lengtht() and Substrt().

  Lengtht() and Substrt() deal with the number of bytes that the string occupies in a specified character set. Typically, you would use lengtht( ) and substrt( ) if you are writing to a file in a specific character set, and you need to check or limit the byte length of the string in the output file, as would be required by most interface files.

For example, the following code determines whether string &abc will require more than 10 bytes in an output file. If the string is more than 10 bytes, the code truncates the string to use only the first 10 bytes worth of characters. The SQR system variable $sqr-encoding-file-output is used to reference the SQR.INI ENCODING-FILE-OUTPUT variable, which determines the default character set of any file that is written to by the SQR OPEN command. You can substitute any valid PeopleSoft system encoding for the $sqr-encoding-file-output variable.

If lengtht(&abc, $sqr-encoding-file-output) >10 then
&abc = substrt(&abc, $sqr-encoding-file-output,1,10)
End-if

OPEN Command

The SQR OPEN command, used to read and write files from within SQR programs, enables the report designer to specify the character set of the file being opened. You can specify a character set explicitly in the OPEN command. If you do not specify a character set, the SQR program uses the character set specified in the pssqr.ini/unx parameter ENCODING-FILE-OUTPUT or ENCODING-FILE-INPUT, depending on whether you are opening the file for reading or writing.

To integrate with a third-party system, specify the character set for SQR programs to match the target data. For example, a mainframe-based payroll system may expect an EBCDIC format file. Specifying the character set directly in the OPEN command enables the SQR program to be independent of the pssqr.ini/unx settings and enables the SQR program to create the file directly in the character set expected by the target system (without the need to convert the output in a separate step).

As another example, if you are generating text output for a mail merge in Microsoft Word, you can specify UCS-2 or Unicode-Little-Endian to the ENCODING parameter of the OPEN statement, so that Microsoft Word can import the Unicode file including international text.

Explanation of the Following Tables

The Encoding Parameter column lists the values that you can specify in the ENCODING parameter in the pssqr.ini/unx file.

ENCODING-REPORT-OUTPUT applies to all output types.

In the Output Supported column of the following tables, note the following:

SPF, HTML, Enhanced HTML, and PDF can output all the supported languages and encodings. For SPF, HTML, and Enhanced HTML, SQR uses UTF-8 encoding (Unicode), even if ENCODING and ENCODING-REPORT-OUTPUT is set to non-Unicode encoding in the pssqr.ini/unx file. For PDF, character support is dependent on the fonts listed in the [PDF Fonts] section of the pssqr.ini/unx file.

Data processing of Arabic/Hebrew and other languages normally written in right-to-left order is supported, but SQR for PeopleSoft does not generate reports in right-to-left page order.

Supplementary characters, such as Hong Kong characters in GB18030, are not supported in SQR PDF output.These characters are supported in HTML output. Using BI Publisher instead of SQR is recommended for supplementary character support in PDF files,or for Arabic and Hebrew support.

Arabic

The following table lists the supported encodings for the Arabic character set:

Baltic

The following table lists the supported encodings for the Baltic character set:

Celtic

The following table lists the supported encodings for the Celtic character set:

Chinese (Simplified)

The following table lists the supported encodings for the Simplified Chinese character set:

Chinese (Traditional)

The following table lists the supported encodings for the Traditional Chinese character set:

Cyrillic

The following table lists the supported encodings for the Cyrillic character set:

Greek

The following table lists the supported encodings for the Greek character set:

Gurmukhi

The following table lists the supported encodings for the Gurmukhi character set:

Hebrew

The following table lists the supported encodings for the Hebrew character set:

Icelandic

The following table lists the supported encodings for the Icelandic character set:

Japanese

The following table lists the supported encodings for the Japanese character set:

Korean

The following table lists the supported encodings for the Korean character set:

Latin

The following table lists the supported encodings for the Latin character set:

Latin (Canadian French)

The following table lists the supported encodings for the Latin (Canadian French) character set:

Latin (Central European)

The following table lists the supported encodings for the Latin (Central European) character set:

Latin (Southeast European)

The following table lists the supported encodings for the Latin (Southeast European) character set:

Latin (U.S. English)

The following table lists the supported encodings for the Latin (U.S. English) character set:

Latin (Western European)

The following table lists the supported encodings for the Latin (Western European) character set:

Malayalam

The following table lists the supported encodings for the Malayalam character set:

Nordic

The following table lists the supported encodings for the Nordic character set:

Slavic

The following table lists the supported encodings for the Slavic character set:

Symbol

The following table lists the supported encodings for the Symbol character set:

Thai

The following table lists the supported encodings for the Thai character set:

Turkish

The following table lists the supported encodings for the Turkish character set:

Unicode

The following table lists the supported encodings for the Unicode character set:

Vietnamese

The following table lists the supported encodings for the Vietnamese character set: