Facing issue for using the SUBSTR function with negative number in eText Template

Question

We have requirement to print the last 4 digits from summed value in eText template. We have used the below code to print the last 4 digits but its not working. please let me know if any one of you worked on similar requirement.

SUBSTR((SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME=’Net Pay’]/RUN)*100)+SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME=’GPR POSITIVE PAY FEDERAL ELEMENTS GROUP’]/GPR_CUSTOM_BAL))

),length(SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME=’Net Pay’]/RUN)*100)+SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME=’GPR POSITIVE PAY FEDERAL ELEMENTS GROUP’]/GPR_CUSTOM_BAL))

)-3)

Sai.Bomma · Answer

HI @Mohammed Rafi ,

For more clarifications, We need to calculate the grand total sum of the BRANCH_NUMBER column. Because the length of this total is unpredictable and can scale up to 30 digits depending on the employee headcount, the system must dynamically extract and return only the trailing (last) 10 digits of the final sum.

Example : Total sum of branch number = '1122334455667788584960' we need to get last 10 number which is '7788584960'

Best Regards,

Sai

Sai.Bomma · Answer

Hi Rafi,

I have a tried with the below code but still it is fetching the first 10 numbers instead the last 10 numbers.

SUBSTR(TO_CHAR(SUM(TO_NUMBER(PAYMENT_RECORD/PAYMENT_DETAILS/DESTINATION_BANK/BRANCH_NUMBER))),-10)

Mohammed Rafi · Answer

Hi Ramu,

In Oracle BI Publisher eText templates, the issue is usually that SUBSTR expects a character string, while your expression is returning a numeric value from the SUM() calculations.

Try converting the summed value to a string using below SQL

SUBSTR(

TO_CHAR(

SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME='Net Pay']/RUN) * 100)

+

SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME='GPR POSITIVE PAY FEDERAL ELEMENTS GROUP']/GPR_CUSTOM_BAL))

),

-4

)

A simpler and more reliable approach is to use MOD if you only need the last 4 digits of the numeric value

MOD(

SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME='Net Pay']/RUN) * 100)

+

SUM(TO_NUMBER(Balance_Record[BASE_BALANCE_NAME='GPR POSITIVE PAY FEDERAL ELEMENTS GROUP']/GPR_CUSTOM_BAL)),

10000

)