ON TABLE {PCHOLD|HOLD|SAVE} [AS name] FORMAT PPTX 
[TEMPLATE {'template.potx'|'template.potm'} SLIDENUMBER n]

where:

name

Is the name of the PowerPoint output file.

template

Is the name of the PowerPoint template file. The template file must have at least one blank slide and must be saved as a .POTX or .POTM extension on your WebFOCUS Reporting Server application directory.

Note: Since different extensions are supported for PowerPoint templates, it is recommended that you include the extension in the name of your template.

n

Is the number of the slide on which to place the report output. This number is optional if the template has only one slide.

The following request against the GGSALES data source inserts a WebFOCUS report into a PowerPoint PPTX template named mytemplate.potx which is stored in the application directory:

TABLE FILE GGSALES
HEADING
" "
" "
" "
" "
" "
SUM DOLLARS UNITS CATEGORY 
BY  REGION
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PPTX TEMPLATE 'mytemplate.potx' SLIDENUMBER 3
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10,$
TYPE=HEADING, image=gglogo.gif, POSITION=(0.000000 0.000000),$
ENDSTYLE
END

The output is:

CHAR_LENGTH(source_string)

where:

source_string

Alphanumeric

Is the string whose length is returned.

The data type of the returned length value is Integer.

The following request against the EMPLOYEE data source creates a virtual field named LASTNAME of type A15V that contains the LAST_NAME with the trailing blanks removed. It then uses CHAR_LENGTH to return the number of characters.

DEFINE FILE EMPLOYEE
LASTNAME/A15V = RTRIM(LAST_NAME);
END
TABLE FILE EMPLOYEE
SUM LAST_NAME NOPRINT AND COMPUTE
NAME_LEN/I3 = CHAR_LENGTH(LASTNAME);
BY LAST_NAME
ON TABLE SET PAGE NOPAGE
END

The output is:

   LAST_NAME        NAME_LEN    
  ---------        --------      
  BANNING                 7
  BLACKWOOD               9
  CROSS                   5
  GREENSPAN               9
  IRVING                  6
  JONES                   5
  MCCOY                   5
  MCKNIGHT                8
  ROMANS                  6
  SMITH                   5
  STEVENS                 7

DIGITS(number,length)

where:

number

Integer

Is the number to be converted, stored in a field with data type Integer.

length

Integer between 1 and 10

Is the length of the returned character string. If length is longer than the number of digits in the number being converted, the returned value is padded on the left with zeros. If length is shorter than the number of digits in the number being converted, the returned value is truncated on the left.

The following request against the WF_RETAIL_LITE data source converts -123.45 and ID_PRODUCT to character strings:

DEFINE FILE WF_RETAIL_LITE
MEAS1/I8=-123.45;
DIG1/A6=DIGITS(MEAS1,6) ;
DIG2/A6=DIGITS(ID_PRODUCT,6) ;
END
TABLE FILE WF_RETAIL_LITE
PRINT MEAS1 DIG1 
ID_PRODUCT DIG2  
BY PRODUCT_SUBCATEG
WHERE PRODUCT_SUBCATEG EQ 'Flat Panel TV'
ON TABLE SET PAGE NOPAGE
END

The output is:

• Only I Format numbers will be converted. D, P, and F formats generate error messages and should be converted to I before using the DIGITS function. The limit for the number that can be converted is 2GB.
• Negative integers are turned into positive integers.
• Integer formats with decimal places are truncated.
• DIGITS is not supported in Dialogue Manager.

LPAD(string, out_length, pad_character) 

where:

string

Fixed length alphanumeric

Is a string to pad on the left side.

out_length

Integer

Is the length of the output string after padding.

pad_character

Fixed length alphanumeric

Is a single character to use for padding.

In the following request against the WF_RETAIL data source, LPAD left-pads the PRODUCT_CATEGORY column with @ symbols:

DEFINE FILE WF_RETAIL
LPAD1/A25 = LPAD(PRODUCT_CATEGORY,25,'@');
DIG1/A4 = DIGITS(ID_PRODUCT,4);
END
TABLE FILE WF_RETAIL
SUM DIG1 LPAD1
BY PRODUCT_CATEGORY
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=DATA,FONT=COURIER,SIZE=11,COLOR=BLUE,$
END

The output is:

• To use the single quotation mark as the padding character, you must double it and enclose the two single quotation marks within single quotation marks (LPAD(COUNTRY, 20,''''). You can use an amper variable in quotation marks for this parameter, but you cannot use a field, virtual or real.
• Input can be fixed or variable length alphanumeric.
• Output, when optimized to SQL, will always be data type VARCHAR.
• If the output is specified as shorter than the original input, the original data will be truncated, leaving only the padding characters. The output length can be specified as a positive integer or an unquoted &variable (indicating a numeric).

LOWER(source_string)

where:

source_string

Alphanumeric

Is the string to convert to lowercase.

The returned string is the same data type and length as the source string.

In the following request against the EMPLOYEE data source, LOWER converts the LAST_NAME field to lowercase and stores the result in LOWER_NAME:

TABLE FILE EMPLOYEE
PRINT LAST_NAME AND COMPUTE
LOWER_NAME/A15 = LOWER(LAST_NAME);
ON TABLE SET PAGE NOPAGE
END

The output is:

  LAST_NAME        LOWER_NAME           
  ---------        ----------                   
  STEVENS          stevens        
  SMITH            smith          
  JONES            jones          
  SMITH            smith          
  BANNING          banning        
  IRVING           irving         
  ROMANS           romans         
  MCCOY            mccoy          
  BLACKWOOD        blackwood      
  MCKNIGHT         mcknight       
  GREENSPAN        greenspan      
  CROSS            cross    

LTRIM(source_string)

where:

source_string

Alphanumeric

Is the string to trim on the left.

The data type of the returned string is AnV, with the same maximum length as the source string.

In the following request against the MOVIES data source, the DIRECTOR field is right-justified and stored in the RDIRECTOR virtual field. Then LTRIM removes leading blanks from the RDIRECTOR field:

DEFINE FILE MOVIES
RDIRECTOR/A17 = RJUST(17, DIRECTOR, 'A17');
 END
TABLE FILE MOVIES
PRINT RDIRECTOR AND
COMPUTE
TRIMDIR/A17 = LTRIM(RDIRECTOR);
WHERE DIRECTOR CONTAINS 'BR'
ON TABLE SET PAGE NOPAGE
END

The output is:

  RDIRECTOR          TRIMDIR     
  ---------          -------                   
        ABRAHAMS J.  ABRAHAMS J.      
          BROOKS R.  BROOKS R.        
        BROOKS J.L.  BROOKS J.L. 

POSITION(pattern, source_string)

where:

pattern

Alphanumeric

Is the substring whose position you want to locate. The string can be as short as a single character, including a single blank.

source_string

Alphanumeric

Is the string in which to find the pattern.

The data type of the returned value is Integer.

In the following request against the EMPLOYEE data source, POSITION determines the position of the first capital letter I in LAST_NAME and stores the result in I_IN_NAME:

TABLE FILE EMPLOYEE
PRINT LAST_NAME AND COMPUTE
I_IN_NAME/I2 = POSITION('I', LAST_NAME);
ON TABLE SET PAGE NOPAGE
END

The output is:

  LAST_NAME        I_IN_NAME                     
  ---------        ---------                           
  STEVENS                  0
  SMITH                    3
  JONES                    0
  SMITH                    3
  BANNING                  5
  IRVING                   1
  ROMANS                   0
  MCCOY                    0
  BLACKWOOD                0
  MCKNIGHT                 5
  GREENSPAN                0
  CROSS                    0

RTRIM(source_string)

where:

source_string

Alphanumeric

Is the string to trim on the right.

The data type of the returned string is AnV, with the same maximum length as the source string.

The following request against the MOVIES data source creates the field DIRSLASH, that contains a slash at the end of the DIRECTOR field. Then it creates the TRIMDIR field, which trims the trailing blanks from the DIRECTOR field and places a slash at the end of that field:

TABLE FILE MOVIES
PRINT DIRECTOR NOPRINT AND
COMPUTE
DIRSLASH/A18 = DIRECTOR|'/';
TRIMDIR/A17V = RTRIM(DIRECTOR)|'/';
WHERE DIRECTOR CONTAINS 'BR'
ON TABLE SET PAGE NOPAGE
END

On the output, the slashes show that the trailing blanks in the DIRECTOR field were removed in the TRIMDIR field:

  DIRSLASH            TRIMDIR                           
  --------            -------                                         
  ABRAHAMS J.      /  ABRAHAMS J./     
  BROOKS R.        /  BROOKS R./       
  BROOKS J.L.      /  BROOKS J.L./     

SUBSTRING(source_string, start_position, length_limit)

where:

source_string

Alphanumeric

Is the string from which to extract the substring. It can be a field, a literal in single quotation marks ('), or a variable.

start_position

Integer

Is the starting position of the substring in source_string. If the position is 0, it is treated as 1. If the position is negative, the starting position is counted backward from the end of source_string.

length_limit

Integer

Is the limit for the length of the substring. The ending position of the substring is calculated as start_position + length_limit - 1. If the calculated position beyond the end of the source string, the position of the last character of source_string becomes the ending position.

The data type of the returned substring is AnV.

In the following request, POSITION determines the position of the first letter I in LAST_NAME and stores the result in I_IN_NAME. SUBSTRING then extracts three characters beginning with the letter I from LAST_NAME, and stores the results in I_SUBSTR.

TABLE FILE EMPLOYEE
PRINT
COMPUTE
I_IN_NAME/I2 = POSITION('I', LAST_NAME); AND
COMPUTE
I_SUBSTR/A3 =
SUBSTRING(LAST_NAME, I_IN_NAME, I_IN_NAME+2);
BY LAST_NAME
ON TABLE SET PAGE NOPAGE
END

The output is:

  LAST_NAME        I_IN_NAME  I_SUBSTR                  
  ---------        ---------  --------                             
  BANNING                  5  ING     
  BLACKWOOD                0  BL      
  CROSS                    0  CR      
  GREENSPAN                0  GR      
  IRVING                   1  IRV     
  JONES                    0  JO      
  MCCOY                    0  MC      
  MCKNIGHT                 5  IGH     
  ROMANS                   0  RO      
  SMITH                    3  ITH     
                           3  ITH     
  STEVENS                  0  ST    

RPAD(string, out_length, pad_character) 

where:

string

Alphanumeric

Is a string to pad on the right side.

out_length

Integer

Is the length of the output string after padding.

pad_character

Alphanumeric

Is a single character to use for padding.

In the following request against the WF_RETAIL data source, RPAD right-pads the PRODUCT_CATEGORY column with @ symbols:

DEFINE FILE WF_RETAIL
RPAD1/A25 = RPAD(PRODUCT_CATEGORY,25,'@');
DIG1/A4 = DIGITS(ID_PRODUCT,4);
END
TABLE FILE WF_RETAIL
SUM DIG1 RPAD1
BY PRODUCT_CATEGORY
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=DATA,FONT=COURIER,SIZE=11,COLOR=BLUE,$
END

The output is:

• The input string can be data type AnV, VARCHAR, TX, and An.
• Output can only be AnV or An.
• When working with relational VARCHAR columns, there is no need to trim trailing spaces from the field if they are not desired. However, with An and AnV fields derived from An fields, the trailing spaces are part of the data and will be included in the output, with the padding being placed to the right of these positions. You can use TRIM or TRIMV to remove these trailing spaces prior to applying the RPAD function.

TOKEN(string, delimiter, number)

where:

string

Fixed length alphanumeric

Is the character string from which to extract the token.

delimiter

Fixed length alphanumeric

Is a single character delimiter.

number

Integer

Is the token number to extract.

TOKEN extracts the second token from the PRODUCT_SUBCATEG column, where the delimiter is the letter P:

DEFINE FILE WF_RETAIL_LITE
TOK1/A20 =TOKEN(PRODUCT_SUBCATEG,'P',2);
END
TABLE FILE WF_RETAIL_LITE
SUM TOK1 AS Token
BY PRODUCT_SUBCATEG
ON TABLE SET PAGE NOPAGE
END

The output is:

TRIM_(trim_where, trim_character, source_string) 

where:

trim_where

Keyword

Defines where to trim the source string. Valid values are:

• LEADING, which removes leading occurrences.
• TRAILING, which removes trailing occurrences.
• BOTH, which removes leading and trailing occurrences.

trim_character

Alphanumeric

Is a single character, enclosed in single quotation marks ('), whose occurrences are to be removed from source_string. For example, the character can be a single blank (‘ ‘).

source_string

Alphanumeric

Is the string to be trimmed.

The data type of the returned string is AnV.

In the following request, TRIM_ removes leading occurrences of the character ‘B’ from the DIRECTOR field:

TABLE FILE MOVIES
PRINT DIRECTOR AND
COMPUTE
TRIMDIR/A17 = TRIM_(LEADING, 'B', DIRECTOR);
WHERE DIRECTOR CONTAINS 'BR'
ON TABLE SET PAGE NOPAGE
END

The output is:

  DIRECTOR           TRIMDIR                              
  --------           -------                                      
  ABRAHAMS J.        ABRAHAMS J.      
  BROOKS R.          ROOKS R.         
  BROOKS J.L.        ROOKS J.L.       

UPPER(source_string)

where:

source_string

Alphanumeric

Is the string to convert to uppercase.

The returned string is the same data type and length as the source string.

In the following request, LCWORD converts LAST_NAME to mixed case. Then UPPER converts the LAST_NAME_MIXED field to uppercase:

DEFINE FILE EMPLOYEE
LAST_NAME_MIXED/A15=LCWORD(15, LAST_NAME, 'A15');
LAST_NAME_UPPER/A15=UPPER(LAST_NAME_MIXED) ;
END
TABLE FILE EMPLOYEE
PRINT LAST_NAME_UPPER AND FIRST_NAME 
BY LAST_NAME_MIXED
WHERE CURR_JOBCODE EQ 'B02' OR 'A17' OR 'B04';
ON TABLE SET PAGE NOPAGE
END

The output is:

  LAST_NAME_MIXED  LAST_NAME_UPPER  FIRST_NAME              
  ---------------  ---------------  ----------                         
  Banning          BANNING          JOHN      
  Blackwood        BLACKWOOD        ROSEMARIE 
  Cross            CROSS            BARBARA   
  Mccoy            MCCOY            JOHN      
  Mcknight         MCKNIGHT         ROGER     
  Romans           ROMANS           ANTHONY  

DTADD(date, component, increment)

where:

date

Date or date-time

Is the date or date-time value to be incremented.

component

Keyword

Is the component to be incremented. Valid components (and acceptable values) are:

• YEAR (1-9999)
• QUARTER (1-4)
• MONTH (1-12)
• WEEK (1-53). This is affected by the WEEKFIRST setting.
• DAY (of the Month, 1-31)
• HOUR (0-23)
• MINUTE (0-59)
• SECOND (0-59)

increment

Integer

Is the value (positive or negative) to add to the component.

The following request against the WF_RETAIL data source adds three days to the employee date of birth:

DEFINE FILE WF_RETAIL
NEWDATE/YYMD = DTADD(DATE_OF_BIRTH, DAY, 3);
MGR/A3 = DIGITS(ID_MANAGER, 3);
END
TABLE FILE WF_RETAIL
SUM MGR NOPRINT DATE_OF_BIRTH NEWDATE
BY MGR
ON TABLE SET PAGE NOPAGE
END

The output is:

• Each element must be manipulated separately. Therefore, if you want to add 1 year and 1 day to a date, you need to call the function twice, once for YEAR (you need to take care of leap years) and once for DAY. The simplified functions can be nested in a single expression or created and applied in separate DEFINE or COMPUTE expressions.
• With respect to parameter validation, DTADD will not allow anything but a standard date or a date-time value to be used in the first parameter.
• The increment is not checked, and the user should be aware that decimal numbers are not supported and will be truncated. Any combination of values that increases the YEAR beyond 9999 returns the input date as the value, with no message. If the user receives the input date when expecting something else, it is possible there was an error.

DTDIFF(end_date, start_date, component)

where:

end_date

Date or date-time

Is the ending date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.

start_date

Date or date-time

Is the starting date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.

component

Keyword

Is the component on which the number of boundaries is to be calculated. For example, QUARTER finds the difference in quarters between two dates. Valid components (and acceptable values) are:

• YEAR (1-9999)
• QUARTER (1-4)
• MONTH (1-12)
• WEEK (1-53). This is affected by the WEEKFIRST setting.
• DAY (of the Month, 1-31)
• HOUR (0-23)
• MINUTE (0-59)
• SECOND (0-59)

The following request against the WF_RETAIL data source calculates employee age when hired:

DEFINE FILE WF_RETAIL
YEARS/I9 = DTDIFF(START_DATE, DATE_OF_BIRTH, YEAR); 
END
TABLE FILE WF_RETAIL
PRINT START_DATE DATE_OF_BIRTH YEARS AS 'Hire,Age'
BY  EMPLOYEE_NUMBER 
WHERE EMPLOYEE_NUMBER CONTAINS 'AA'
ON TABLE SET PAGE NOPAGE
END 

The output is:

DTPART(date, component)

where:

date

Date or date-time

Is the date in standard date or date-time format.

component

Keyword

Is the component to extract in integer format. Valid components (and values) are:

• YEAR (1-9999).
• QUARTER (1-4).
• MONTH (1-12).
• WEEK (of the year, 1-53). This is affected by the WEEKFIRST setting.
• DAY (of the Month, 1-31).
• DAY_OF_YEAR (1-366).
• WEEKDAY (day of the week, 1-7). This is affected by the WEEKFIRST setting.
• HOUR (0-23).
• MINUTE (0-59).
• SECOND (0-59.)
• MILLISECOND (0-999).
• MICROSECOND (0-999999).

The following request against the WF_RETAIL data source extracts the QUARTER component from the employee start date:

DEFINE FILE WF_RETAIL
QTR/I2 =DTPART(START_DATE, QUARTER); 
END
TABLE FILE WF_RETAIL
PRINT START_DATE QTR AS Quarter
BY  EMPLOYEE_NUMBER 
WHERE EMPLOYEE_NUMBER CONTAINS 'AH'
ON TABLE SET PAGE NOPAGE
END 

The output is:

DTRUNC(date_or_timestamp, date_period)

where:

date_or_timestamp

Date or date-time

Is the date or timestamp of interest.

date_period

Is the period whose starting date you want to find. Can be one of the following:

• DAY, returns day of the month (1-31).
• YEAR, returns year (1-9999).
• MONTH, returns month (1-12).
• QUARTER, returns quarter (1-4).

In the following request against the WF_RETAIL data source, DTRUNC returns the first date of the quarter given the start date of the employee:

DEFINE FILE WF_RETAIL
QTRSTART/YYMD = DTRUNC(START_DATE, QUARTER); 
END
TABLE FILE WF_RETAIL
PRINT START_DATE QTRSTART AS 'Start,of Quarter'
BY EMPLOYEE_NUMBER 
WHERE EMPLOYEE_NUMBER CONTAINS 'AH'
ON TABLE SET PAGE NOPAGE
END 

The output is: