For our statement of account I need to compare months to decide which data to show. In the example below, I only want data to show if the months are equal. Here is the code I am using: It is returning all dates. I thought it might be an issue with the hierarchy, however, when I try and put that into the code, nothing changes. Here is the portion of the xml I am referencing. S T A T E M E N T O F A C C O U N T 1 Page: 52848 Statement Number Remit To: Test Company 1234 TEST STREET CITY STATE 99999 Date 2025-05-30 Account 103356 Billing Address: CUSTOMER ATTN: CUSTOMER 2222 Test Drive CITY STATE 99999 testing@gmail.com 103356 CUSTOMER Invoice 874.77 159255 * 2025-04-07 874.77 Recurring Invoice 256.60 159361 * 2025-04-15 256.60 Recurring Invoice 1128.75 159801 * 2025-05-01 1128.75

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Month Comparison Is Not Returning Correct Data

User_CYBWX

For our statement of account I need to compare months to decide which data to show. In the example below, I only want data to show if the months are equal. Here is the code I am using:

<?Date___local_ID669?>

<?for-each:A_R_Statement_Detail_S9?>

<?xdofx:if to_char(Invoice_DueDate_ID67,'MM')

= to_char(Date___local_ID669,'MM')?>

<?Invoice_DueDate_ID67?>

<?Amount_Due___section_ID91?>

<?end for-each?>

It is returning all dates. I thought it might be an issue with the hierarchy, however, when I try and put that into the code, nothing changes.

Here is the portion of the xml I am referencing.

<A_R_Statement_Detail_S9_Group>

<Payor_Level_Header_S14>

<S_T_A_T_E_M_E_N_T___O_F___A_C__ID698>S T A T E M E N T O F A C C O U N T</S_T_A_T_E_M_E_N_T___O_F___A_C__ID698>

<Page__000055_ID705>1</Page__000055_ID705>

<Page__ID706>Page:</Page__ID706>

<APChecksControlField_ID700>52848</APChecksControlField_ID700>

<Statement_Number_ID699>Statement Number</Statement_Number_ID699>

<Remit_To__ID702>Remit To:</Remit_To__ID702>

<Remit_to_Name_ID445>Test Company </Remit_to_Name_ID445>

<RemitToAddressLine1_ID2>1234 TEST STREET</RemitToAddressLine1_ID2>

<RemitToAddressLine2_ID5>CITY STATE 99999</RemitToAddressLine2_ID5>

<Date_ID707>Date</Date_ID707>

<Date___local_ID669>2025-05-30</Date___local_ID669>

<Account_ID708>Account</Account_ID708>

<Account___local_ID670>103356</Account___local_ID670>

<Billing_Address__ID703>Billing Address:</Billing_Address__ID703>

<NameAlpha_ID21>CUSTOMER</NameAlpha_ID21>

<AddressLine1_ID24>ATTN: CUSTOMER</AddressLine1_ID24>

<AddressLine2_ID27>2222 Test Drive</AddressLine2_ID27>

<AddressLine3_ID30>CITY STATE 99999</AddressLine3_ID30>

<EmailAddressow_ID714>testing@gmail.com</EmailAddressow_ID714>

<Currency_Level_Footer_S34>

<Customer_Level_Header_S32>

<A_R_Statement_Detail_S9>

<AN8_LBH_Conditional_S216>

<AddressNumber_ID2>103356</AddressNumber_ID2>

<Description_ID3>CUSTOMER</Description_ID3>

</AN8_LBH_Conditional_S216>

<Doc_Type_Desc_ID69>Invoice</Doc_Type_Desc_ID69>

<Amount_Due___section_ID91>874.77</Amount_Due___section_ID91>

<Document_Number_ID1>159255</Document_Number_ID1>

<DocumentPayItem_ID65>*</DocumentPayItem_ID65>

<Invoice_DueDate_ID67>2025-04-07</Invoice_DueDate_ID67>

<Previous_Amount_Due___Report_ID109>874.77</Previous_Amount_Due___Report_ID109>

</A_R_Statement_Detail_S9>

<A_R_Statement_Detail_S9>

<Doc_Type_Desc_ID69>Recurring Invoice</Doc_Type_Desc_ID69>

<Amount_Due___section_ID91>256.60</Amount_Due___section_ID91>

<Document_Number_ID1>159361</Document_Number_ID1>

<DocumentPayItem_ID65>*</DocumentPayItem_ID65>

<Invoice_DueDate_ID67>2025-04-15</Invoice_DueDate_ID67>

<Previous_Amount_Due___Report_ID109>256.60</Previous_Amount_Due___Report_ID109>

</A_R_Statement_Detail_S9>

<A_R_Statement_Detail_S9>

<Doc_Type_Desc_ID69>Recurring Invoice</Doc_Type_Desc_ID69>

<Amount_Due___section_ID91>1128.75</Amount_Due___section_ID91>

<Document_Number_ID1>159801</Document_Number_ID1>

<DocumentPayItem_ID65>*</DocumentPayItem_ID65>

<Invoice_DueDate_ID67>2025-05-01</Invoice_DueDate_ID67>

<Previous_Amount_Due___Report_ID109>1128.75</Previous_Amount_Due___Report_ID109>

Find more posts tagged with

Accepted answers

All comments

Gaurav Bharadwaj-Oracle

Hi @User_CYBWX
Have a look at this thread and see if it is helpful :

https://community.oracle.com/mosc/discussion/4557783/how-to-compare-a-date-with-system-date-in-the-rtf-template

Additional Reference that talks about Date Conversion Function: https://docs.oracle.com/middleware/bi12214/bip/BIPRD/GUID-E2904A42-80A1-4086-8A8C-31FDE24389C1.htm

If my response has answered your question or assisted you with your concern, please click "yes" below to accept the answer or comment with any additional queries. You can also read the Cloud Customer Connect Guidelines for Accepted Answer

Regards,
Gaurav

Riyaz Ali-Oracle

Hi User_CYBWX,

It looks like your xdofx condition is not filtering correctly, which is why all dates are being returned instead of only those matching the month. You can try below and check:

Ensure Proper Data Type Handling

The to_char() function extracts the month as a string, but sometimes XML data types can cause unexpected behavior.
Try explicitly converting both values to numeric format before comparison:
<?xdofx:if to_number(to_char(Invoice_DueDate_ID67,'MM')) = to_number(to_char(Date___local_ID669,'MM'))?>

2. Check XML Hierarchy & Placement

If Date___local_ID669 is outside the for-each loop, it may not be correctly referenced.
Ensure that Date___local_ID669 is accessible within the loop.

3. Use an Alternative Filtering Approach

Instead of filtering inside the loop, try pre-filtering the dataset:
<?for-each:A_R_Statement_Detail_S9[ to_char(Invoice_DueDate_ID67,'MM') = to_char(Date___local_ID669,'MM') ]?>
This ensures that only matching records are processed.

4. Verify XML Data Structure

If Invoice_DueDate_ID67 is nested within multiple levels, ensure that the correct path is used.
Try referencing it explicitly:
<?xdofx:if to_char(.//Invoice_DueDate_ID67,'MM') = to_char(Date___local_ID669,'MM')?>

Thanks,
Riyaz Ali

User_CYBWX

Unfortunately, none of the suggested fixes have worked. I am still looking for a resolution.

User_CYBWX

I have something that works.

<?xdoxslt:set_variable($_XDOCTX, 'headerMonth', substring(Date___local_ID669, 6, 2))?>

<?for-each:A_R_Statement_Detail_S9?>

<?if:substring(Invoice_DueDate_ID67, 6, 2) = xdoxslt:get_variable($_XDOCTX, 'headerMonth')?>

<?Invoice_DueDate_ID67?> <?Amount_Due___section_ID91?> etc.

<?end if?>

<?end for-each?>

However, I also need a variable that will be for the previous month and I cannot get that to work. Here is what I have tried.

<?xdoxslt:set_variable($_XDOCTX, 'prevMonth', substring(Date___local_ID669, 6, 2) -1 ))?>

AND

<?xdoxslt:set_variable($_XDOCTX, 'prevMonth', xdoxslt:if(substring(Date___local_ID669, 6, 2) = '01', '12', xdoxslt:format_number(substring(Date___local_ID669, 6, 2) - 1, '00')) )?>

Neither of those worked. Any ideas would be appreciated.

Riyaz Ali-Oracle

Try this for Previous Month Calculation:

<?xdoxslt:set_variable($_XDOCTX, 'prevMonth',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '01', '12',
xdoxslt:format_number(to_number(substring(Date___local_ID669, 6, 2)) - 1, '00')) )?>

User_CYBWX

Unfortunately, that did not work. I get an error for to_number.

<?xdoxslt:set_variable($_XDOCTX, 'prevMonth',

xdoxslt:if(substring(Date___local_ID669, 6, 2) = '01', '12',

xdoxslt:format_number(to_number(substring(Date___local_ID669, 6, 2)) - 1, '00')) )?>

Riyaz Ali-Oracle

The issue is that to_number() isn't supported directly inside xdoxslt:set_variable() in some cases, especially when nested inside xdoxslt:if().

Try this:

<?xdoxslt:set_variable($_XDOCTX, 'prevMonth',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '01', '12',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '02', '01',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '03', '02',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '04', '03',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '05', '04',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '06', '05',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '07', '06',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '08', '07',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '09', '08',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '10', '09',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '11', '10',
'11'))))))))))))?>

User_CYBWX

Unfortunately, that did not work either. Here is how it looks when I put in your code.

<?Date___local_ID669?><?xdoxslt:set_variable($_XDOCTX, 'headerMonth', substring(Date___local_ID669, 6, 2))?><?xdoxslt:set_variable($_XDOCTX, 'prevMonth',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '01', '12',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '02', '01',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '03', '02',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '04', '03',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '05', '04',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '06', '05',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '07', '06',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '08', '07',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '09', '08',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '10', '09',
xdoxslt:if(substring(Date___local_ID669, 6, 2) = '11', '10',
'11'))))))))))))?>

I receive an error.

Dir: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts
Out File: C:\Users\cara.hunter\AppData\Local\Oracle\BIPublisher\TemplateBuilderforWord\tmp\tmp\361750427212107out.pdf
Run XDO Start
Template: C:\Users\cara.hunter\AppData\Local\Oracle\BIPublisher\TemplateBuilderforWord\tmp\tmp\tmp.rtf
RTFProcessor setLocale: en-us
FOProcessor setData: C:\Users\cara.hunter\OneDrive - Nedelco Inc\BI Publisher\MayStatement.xml
FOProcessor setLocale: en-us
oracle.xdo.XDOException: oracle.xdo11g.xpath.XPathException: Extension function error: Method not found 'if'
at oracle.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1300)
at oracle.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:308)
at oracle.xdo.template.FOProcessor.createFO(FOProcessor.java:2110)
at oracle.xdo.template.FOProcessor.generate(FOProcessor.java:1213)
at RTF2PDF2.runRTFto(RTF2PDF2.java:484)
at RTF2PDF2.runXDO(RTF2PDF2.java:343)
at RTF2PDF2.main(RTF2PDF2.java:235)
Caused by: oracle.xdo11g.xpath.XPathException: Extension function error: Method not found 'if'
at oracle.xdo11g.xslt.XSLStylesheet.flushErrors(XSLStylesheet.java:1850)
at oracle.xdo11g.xslt.XSLStylesheet.execute(XSLStylesheet.java:616)
at oracle.xdo11g.xslt.XSLStylesheet.execute(XSLStylesheet.java:551)
at oracle.xdo11g.xslt.XSLProcessor.processXSL(XSLProcessor.java:345)
at oracle.xdo11g.xslt.XSLProcessor.processXSL(XSLProcessor.java:193)
at oracle.xdo11g.xslt.XSLProcessor.processXSL(XSLProcessor.java:230)
at oracle.xdo11g.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:123)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at oracle.xdo.common.xml.XSLT10gR1.invokeProcessXSL(XSLT10gR1.java:1038)
at oracle.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:729)
at oracle.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:385)
at oracle.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:238)
at oracle.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1276)
... 6 more