• Post category:BI Publisher
  • Post comments:3 Comments
  • Post last modified:January 22, 2021
  • Reading time:7 mins read
You are currently viewing Etext template tips and tricks Part 2
Etext template tips and tricks Part 2

In this article I will go through some of the functions which we use daily while developing etext templates along with sample code for them.

If you haven’t gone through the Etext Part 1 article, I suggest you to go through it by clicking here before proceeding with this article.

Some of the functions listed over here are not even on the oracle documentation and they come based on my personal experience and they will work perfectly without any issues.

Table of Contents

Here is the list of functions along with samples:

Function UsedSample Code
Concatenation Operator (||)PAYROLL_NAME||’SAR’||’S’
Converting Date to Characterto_char(SYSDATE,’YYYYMMDD’)
Integer Part of NumberINTEGER_PART(CONTRIBUTION_AMOUNT)
Decimal Part of NumberDECIMAL_PART(CONTRIBUTION_AMOUNT)
Left padding with ‘0’ for 2 charactersLPAD(DECIMAL_PART(CONTRIBUTION_AMOUNT),2,’0’)
Right Padding with spaces for 5 charactersRpad(‘L01’,5,’ ‘)
IF ConditionIF YEAR_END_PARAM=’Y’ THEN YEAR_END_PERIOD ELSE SUBSTR(PERIOD_NAME,1,2) END IF
Formatting Numberformat_number(to_number(EARNS1YTD),’00000000.00’)
Instring to search for | characterINSTR(ABBR_RESULT,’|’,1,2)
Substring to get the organization till first occurrence of –substr(Organization,1,Instr(Organization,’-‘,1)-1)
Nested If conditionIF Pay_cycle IS NOT NULL THEN (IF Pay_cycle = ‘Biweekly’ THEN ‘B’ ELSE ‘M’ END IF) ELSE NULL END IF
Converting to Number and then to stringTO_CHAR(Round(TO_NUMBER(SALARY_AMOUNT),4))
Format Date to stringformat_date(sysdate,’YYhh’), FORMAT_DATE(SYSDATE,’YYYYMMDD’), FORMAT_DATE(SYSDATE,’HH24MI’)
Replace – with blankReplace(../../../../Person_Primary_National_Identifier_Number, ‘-’, ‘’)
Accessing child levels at parent level using XPATH commandsPPM_TRV/PPM_DG[1]/PPM_RC/BANK_ACCOUNT_NUMBER
Increase Date function to add number of days to dateINCREASE_DATE(ACTUAL_TERMINATION_DATE,1)
NVL if value is blankNVL(ACTUAL_TERMINATION_DATE, EFFECTIVE_START_DATE)
Age Calculation using Julian Number of TO_CHAR(TO_NUMBER(TO_CHAR(sysdate,’J’)) – TO_NUMBER(TO_CHAR(DATE_OF_BIRTH,’J’))) DIV 365
Division operatorDIV
Decode functionDECODE(SEQUENCE_NUMBER(FileSeq), 1, ‘A’, 2,’B’)
Count function to count the number of records at that levelCount(ISSUE)
Length functionIF LENGTH(Department)<=10 THEN Department ELSE NULL END IF
Resetting VariablesResetVar(‘DAILYOTTOTAL2’,0)
Adding to variableAddToVar(‘DAILYOTTOTAL2’,1)
Getting value of variableGetVar(‘DAILYOTTOTAL2’)
Not equal to conditionIF (INSTR(OPRID,’@’,1) != 0) THEN SUBSTR(OPRID,1,instr(OPRID,’@’,1)-1) ELSE OPRID END IF
Sum FunctionRound(SUM(TO_NUMBER(RUN_401K_HOURS)),2)
New Line CharacterChr(10)
Tab characterCHR(09)
Sequence Number (Used along with Sequence Block)SEQUENCE_NUMBER(Sequence_EMP)
Traverse a level up from current level and get the END_DATE../END_DATE
Traverse 4 levels up from the current level and get the NID../../../../Person_Primary_National_Identifier_Number
TRIM function to trim the leading and trailing spacesTRIM(EARN1_EARN2)
Accessing the BAL_VALUE at POSITIVEBALANCEDETAILS level for the Balance Name ’ABC US HEALTHCARE FSA’ (You can use this when you have multiple child rows and you need them in different columns in the output)POSITIVEBALANCEDETAILS[BALANCE_NAME=’ABC US HEALTHCARE FSA’]/BAL_VALUE
Sum of Balance Values for a Balance Namesum(POSITIVEBALANCEDETAILS[BALANCE_NAME=’ABC US HEALTHCARE FSA’]/BAL_VALUE)
Etext functions with sample

How to use Sequence function:

If we have to use the sequence function to get the auto incrementing sequence then we have to use the DEFINE SEQUENCE to define the sequence and use the increment basis at RECORD or LEVEL to increment the sequence. RECORD increments the sequence each time it is used in a new record. LEVEL increments the sequence only for a new instance of the level.

sequence sample - Etext template tips and tricks Part 2
Sample etext for fixed length using sequence

How to use the Concatenation option:

In order to use the Concatenation, we need to define the DEFINE CONCATENATION segment and define the level at which the concatenation should happen, field which should be concatenated and the delimiter for the concatenation.

Concatenation etext - Etext template tips and tricks Part 2
Sample etext for delimited using concatenation

The output will have the Person Number followed by concatenation of all assignment numbers. It’s basically like converting rows into columns with a delimiter.

How to use the Grouping condition in etext:

We can also use the grouping condition in etext template to achieve the grouping and get the distinct values. This is also achieved using DEFINE LEVEL but with GROUPING CRITERIA and GROUP SORT ASCENDING or GROUP SORT DESCENDING

distinct clause in etext - Etext template tips and tricks Part 2
Sample delimited template with distinct clause applied

You can find the complete list of functions supported in etext template by clicking here

If you have any questions, please feel free to reach out to me by posting in comments section.

  • Hi Sricharan,

    It would be helpful if we have similar functions for excel templates also.

    Thanks

  • In visible box by plugintheme