Oracle Analytics Publisher

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Publisher

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Month Comparison Is Not Returning Correct Data

Received Response
43
Views
8
Comments
User_CYBWX
User_CYBWX Rank 2 - Community Beginner
in Oracle Analytics Publisher

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>

Answers

  • Gaurav Bharadwaj-Oracle
    Gaurav Bharadwaj-Oracle Rank 6 - Analytics Lead
    edited Jun 17, 2025 2:58AM

    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
    Riyaz Ali-Oracle Rank 6 - Analytics Lead

    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:

    1. 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
    User_CYBWX Rank 2 - Community Beginner

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

  • User_CYBWX
    User_CYBWX Rank 2 - Community Beginner

    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')?>

    <!-- Your detail layout fields go here, like: -->

    <?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
    Riyaz Ali-Oracle Rank 6 - Analytics Lead

    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
    User_CYBWX Rank 2 - Community Beginner

    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
    Riyaz Ali-Oracle Rank 6 - Analytics Lead

    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
    User_CYBWX Rank 2 - Community Beginner

    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