14 Replies Latest reply: Sep 10, 2012 2:25 AM by 959753 RSS

    How to print "there is not data found for this report" in a XML report

    959753
      Hi ,

      User has a requirement that print "there is not data found for this report" when no data found for the report. But I have an issue when I try to achieve this in RTF template. My loop is as below:

      <?for-each@section:G_SETUP?>
      <?for-each-group@section:G_STATEMENT[STATEMENT_TYPE!='SUMMARY'];./STATEMENT_DATE?><?for-each-group@section:current-group();././SEND_TO_ADDRESS_ID?>
      ..............
      <?end for-each?>
      <?end for-each?>

      To meet user's requirement, I add an if logic outside the loop. like below:
      <?if:count(G_SETUP)<=0?>
      there is not data found for this report
      <?end if?>
      but it does not work.
      I have test, even i put some text like '123' outside the loop without if logic, i can not get it printed.

      could you please help me?

      Best regards
      Terrence

      Below is the data source file for test:
      <?xml version="1.0" encoding="UTF-8"?>

      <ARXSGPO>

      <LIST_G_SETUP>
      <G_SETUP>
      <COMPANY_NAME>test for no data found</COMPANY_NAME>
      <COA_ID>50348</COA_ID>
      <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
      <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
      <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
      <PARENT_REQUEST_ID>2809593</PARENT_REQUEST_ID>
      </G_SETUP>
      </LIST_G_SETUP>
      </ARXSGPO>

      Edited by: 956750 on Sep 4, 2012 12:22 AM
        • 1. Re: How to print "there is not data found for this report" in a XML report
          959753
          who can help me, this is urgent for me. thanks in advance for your great help.
          • 2. Re: How to print "there is not data found for this report" in a XML report
            AlexAnd
            did you see this? https://blogs.oracle.com/xmlpublisher/entry/no_data_found
            • 3. Re: How to print "there is not data found for this report" in a XML report
              959753
              thanks for your help. I do it as the blog suggest,but it seems not work. Could you please help me to find where is the issue.I paste the solution according to the blog based on my situation.

              1. XML file. G_STATEMENT is a child of G_SETUP. I want to print no data found when G_STATEMENT does not occur in the file
              <?xml version="1.0" encoding="UTF-8"?>

              <ARXSGPO>

              <LIST_G_SETUP>
              <G_SETUP>
              <COMPANY_NAME>test company</COMPANY_NAME>
              <COA_ID>50348</COA_ID>
              <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
              <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
              <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
              <PARENT_REQUEST_ID>2809593</PARENT_REQUEST_ID>
              </G_SETUP>
              </LIST_G_SETUP>
              </ARXSGPO>

              2. template logic

              <?for-each@section:G_SETUP?>
              <?for-each-group@section:G_STATEMENT[STATEMENT_TYPE! ='SUMMARY'];./STATEMENT_DATE?><?for-each-group@section:current-group();././SEND_TO_ADDRESS_ID?>
              <?choose:?>
              <?when:SEND_TO_CUSTOMER_NAME!=''?>
              Statement data found
              <?end when?>
              <?otherwise?>
              No Statement data found
              <?end otherwise?>
              <?end choose?>
              <?end for-each-group?><?end for-each-group?>
              <?end for-each?>

              Result: when i generate it to a PDF file. It tells me the file can not be open as no page found.

              Best Regards
              Terrence
              • 4. Re: How to print "there is not data found for this report" in a XML report
                AlexAnd
                you can try another way http://winrichman.blogspot.com/2009/05/no-data-found.html

                because i haven't your xml with and without G_STATEMENT
                then i used
                <?xml version="1.0" encoding="UTF-8"?>
                <ARXSGPO>
                     <LIST_G_SETUP>
                          <G_SETUP>
                               <G_STATEMENT>
                                    <STATEMENT>qwe</STATEMENT>
                               </G_STATEMENT>
                               <COMPANY_NAME>test company</COMPANY_NAME>
                               <COA_ID>50348</COA_ID>
                               <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
                               <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
                               <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
                               <PARENT_REQUEST_ID>2809593</PARENT_REQUEST_ID>
                          </G_SETUP>
                     </LIST_G_SETUP>
                </ARXSGPO>
                and
                <?xml version="1.0" encoding="UTF-8"?>
                <ARXSGPO>
                     <LIST_G_SETUP>
                          <G_SETUP>
                               <G_STATEMENT/>
                               <COMPANY_NAME>test company</COMPANY_NAME>
                               <COA_ID>50348</COA_ID>
                               <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
                               <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
                               <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
                               <PARENT_REQUEST_ID>2809593</PARENT_REQUEST_ID>
                          </G_SETUP>
                     </LIST_G_SETUP>
                </ARXSGPO>
                and for template
                <?for-each@section:G_SETUP?>
                <?if:count(./G_STATEMENT/STATEMENT)>0?>
                Statement
                <?STATEMENT?>
                <?end if?>
                <?if:count(./G_STATEMENT/STATEMENT)=0?>
                No Data Found 
                <?end if?>
                <?end for-each?>
                is it what do you want?
                • 5. Re: How to print "there is not data found for this report" in a XML report
                  959753
                  Hi

                  The file you use is correct and it works in your example. But it seems that it does not work for nested loop. My situation is that I have nested loop in my template. Outer loop is G_SETUP , Inner loop is G_STATEMENT.

                  <?for-each@section:G_SETUP?>
                  <?for-each@section:G_STATEMENT?>
                  <?if:count(./G_STATEMENT/STATEMENT)>0?>
                  Statement
                  <?STATEMENT?>
                  <?end if?>
                  <?if:count(./G_STATEMENT/STATEMENT)=0?>
                  No Data Found
                  <?end if?>
                  <?end for-each?>
                  <?end for-each?>

                  It does not work for above situation that if no statement, then print no data found. Could you please help?

                  Best Regards
                  Terrence
                  • 6. Re: How to print "there is not data found for this report" in a XML report
                    AlexAnd
                    plz post sample xml with and without G_STATEMENT/STATEMENT
                    • 7. Re: How to print "there is not data found for this report" in a XML report
                      959753
                      1. With G_STATEMENT
                      <?xml version="1.0" encoding="UTF-8"?>

                      <ARXSGPO>

                      <LIST_G_SETUP>
                      <G_SETUP>
                      <COMPANY_NAME>test company01</COMPANY_NAME>
                      <COA_ID>50348</COA_ID>
                      <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
                      <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
                      <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
                      <PARENT_REQUEST_ID>2809593</PARENT_REQUEST_ID>
                      <LIST_G_STATEMENT>
                      <G_STATEMENT>
                      <P_LINE_CLUSTER_ID>2209</P_LINE_CLUSTER_ID>
                      <P_MAX_LINE_CLUSTER_ID>2209</P_MAX_LINE_CLUSTER_ID>
                      <SEND_TO_ADDRESS_ID>1112</SEND_TO_ADDRESS_ID>
                      <CUSTOMER_ID>2071</CUSTOMER_ID>
                      <SITE_USE_ID>1080</SITE_USE_ID>
                      <STATEMENT_TYPE>BILL_TO</STATEMENT_TYPE>
                      <REMIT_TO_ADDRESS_ID>-1</REMIT_TO_ADDRESS_ID>
                      <STATEMENT_DATE>2012-08-31T00:00:00.000-05:00</STATEMENT_DATE>
                      <BUCKET1_HEADING>Current</BUCKET1_HEADING>
                      <BUCKET2_HEADING>1-30 Days</BUCKET2_HEADING>
                      <BUCKET3_HEADING>31-60 Days</BUCKET3_HEADING>
                      <BUCKET4_HEADING>61-90 Days</BUCKET4_HEADING>
                      <BUCKET5_HEADING>Over 90 Days</BUCKET5_HEADING>
                      <BUCKET1>0</BUCKET1>
                      <BUCKET2>0</BUCKET2>
                      <BUCKET3>0</BUCKET3>
                      <BUCKET4>2423.97</BUCKET4>
                      <BUCKET5>0</BUCKET5>
                      <FINANCE_CHARGES>0</FINANCE_CHARGES>
                      <TOTAL_AMOUNT_DUE>2423.97</TOTAL_AMOUNT_DUE>
                      <CURRENCY_CODE>EUR</CURRENCY_CODE>
                      <MONTHLY_INTEREST_RATE/>
                      <ERROR_NO_SEND_TO>N</ERROR_NO_SEND_TO>
                      <ERROR_NO_REMIT_TO>Y</ERROR_NO_REMIT_TO>
                      <ERROR_AMOUNT_DIFFERENT>N</ERROR_AMOUNT_DIFFERENT>
                      <CUST_NUM>1036623</CUST_NUM>
                      <SEND_TO_CUSTOMER_NAME>Terrence01</SEND_TO_CUSTOMER_NAME>
                      <CUST_ID>14400</CUST_ID>
                      <SEND_TO_SUMMARY_LOCATION>test</SEND_TO_SUMMARY_LOCATION>
                      <SEND_TO_ADDRESS1>Nitest</SEND_TO_ADDRESS1>
                      <SEND_TO_ADDRESS2/>
                      <SEND_TO_ADDRESS3/>
                      <SEND_TO_ADDRESS4/>
                      <SEND_TO_CITY>test</SEND_TO_CITY>
                      <SEND_TO_STATE/>
                      <SEND_TO_POSTAL_CODE>2040</SEND_TO_POSTAL_CODE>
                      <SEND_TO_COUNTRY_DESC>test</SEND_TO_COUNTRY_DESC>
                      <SEND_COUNTRY>test</SEND_COUNTRY>
                      <REMIT_TO_ADDRESS1/>
                      <REMIT_TO_ADDRESS2/>
                      <REMIT_TO_ADDRESS3/>
                      <REMIT_TO_ADDRESS4/>
                      <REMIT_TO_CITY/>
                      <REMIT_TO_STATE/>
                      <REMIT_TO_POSTAL_CODE/>
                      <REMIT_TO_COUNTRY_DESC/>
                      <REMIT_COUNTRY/>
                      <LIST_G_LINE_CLUSTER>
                      <G_LINE_CLUSTER>
                      <INVOICE_NUMBER>1220152</INVOICE_NUMBER>
                      <TRX_DATE>2012-08-15T00:00:00.000-05:00</TRX_DATE>
                      <TRANSACTION>Payment</TRANSACTION>
                      <DUE_DATE>2012-06-15T00:00:00.000-05:00</DUE_DATE>
                      <TRX_AMOUNT>3044.07</TRX_AMOUNT>
                      <AMOUNT_DUE>2423.97</AMOUNT_DUE>
                      </G_LINE_CLUSTER>
                      </LIST_G_LINE_CLUSTER>
                      </G_STATEMENT>
                      <G_STATEMENT>
                      <P_LINE_CLUSTER_ID>2187</P_LINE_CLUSTER_ID>
                      <P_MAX_LINE_CLUSTER_ID>2187</P_MAX_LINE_CLUSTER_ID>
                      <SEND_TO_ADDRESS_ID>1106</SEND_TO_ADDRESS_ID>
                      <CUSTOMER_ID>2451</CUSTOMER_ID>
                      <SITE_USE_ID>1069</SITE_USE_ID>
                      <STATEMENT_TYPE>BILL_TO</STATEMENT_TYPE>
                      <REMIT_TO_ADDRESS_ID>1042</REMIT_TO_ADDRESS_ID>
                      <STATEMENT_DATE>2012-08-31T00:00:00.000-05:00</STATEMENT_DATE>
                      <BUCKET1_HEADING>Current</BUCKET1_HEADING>
                      <BUCKET2_HEADING>1-30 Days</BUCKET2_HEADING>
                      <BUCKET3_HEADING>31-60 Days</BUCKET3_HEADING>
                      <BUCKET4_HEADING>61-90 Days</BUCKET4_HEADING>
                      <BUCKET5_HEADING>Over 90 Days</BUCKET5_HEADING>
                      <BUCKET1>0</BUCKET1>
                      <BUCKET2>319.15</BUCKET2>
                      <BUCKET3>0</BUCKET3>
                      <BUCKET4>0</BUCKET4>
                      <BUCKET5>0</BUCKET5>
                      <FINANCE_CHARGES>0</FINANCE_CHARGES>
                      <TOTAL_AMOUNT_DUE>319.15</TOTAL_AMOUNT_DUE>
                      <CURRENCY_CODE>EUR</CURRENCY_CODE>
                      <MONTHLY_INTEREST_RATE/>
                      <ERROR_NO_SEND_TO>N</ERROR_NO_SEND_TO>
                      <ERROR_NO_REMIT_TO>N</ERROR_NO_REMIT_TO>
                      <ERROR_AMOUNT_DIFFERENT>N</ERROR_AMOUNT_DIFFERENT>
                      <CUST_NUM>1036608</CUST_NUM>
                      <SEND_TO_CUSTOMER_NAME>Terrence02</SEND_TO_CUSTOMER_NAME>
                      <CUST_ID>14780</CUST_ID>
                      <SEND_TO_SUMMARY_LOCATION>COURCELLES-30</SEND_TO_SUMMARY_LOCATION>
                      <SEND_TO_ADDRESS1>test</SEND_TO_ADDRESS1>
                      <SEND_TO_ADDRESS2/>
                      <SEND_TO_ADDRESS3/>
                      <SEND_TO_ADDRESS4/>
                      <SEND_TO_CITY>test</SEND_TO_CITY>
                      <SEND_TO_STATE/>
                      <SEND_TO_POSTAL_CODE>6180</SEND_TO_POSTAL_CODE>
                      <SEND_TO_COUNTRY_DESC>test</SEND_TO_COUNTRY_DESC>
                      <SEND_COUNTRY>test</SEND_COUNTRY>
                      <REMIT_TO_ADDRESS1>Ctest</REMIT_TO_ADDRESS1>
                      <REMIT_TO_ADDRESS2>test</REMIT_TO_ADDRESS2>
                      <REMIT_TO_ADDRESS3/>
                      <REMIT_TO_ADDRESS4/>
                      <REMIT_TO_CITY>test</REMIT_TO_CITY>
                      <REMIT_TO_STATE/>
                      <REMIT_TO_POSTAL_CODE>2800</REMIT_TO_POSTAL_CODE>
                      <REMIT_TO_COUNTRY_DESC>test</REMIT_TO_COUNTRY_DESC>
                      <REMIT_COUNTRY>BE</REMIT_COUNTRY>
                      <LIST_G_ORG>
                      <G_ORG>
                      <ORG_ID>82</ORG_ID>
                      </G_ORG>
                      </LIST_G_ORG>
                      <LIST_G_LINE_CLUSTER>
                      <G_LINE_CLUSTER>
                      <INVOICE_NUMBER>121000808</INVOICE_NUMBER>
                      <TRX_DATE>2012-08-27T00:00:00.000-05:00</TRX_DATE>
                      <TRANSACTION>Payment</TRANSACTION>
                      <DUE_DATE>2012-08-01T00:00:00.000-05:00</DUE_DATE>
                      <TRX_AMOUNT>2904</TRX_AMOUNT>
                      <AMOUNT_DUE>319.15</AMOUNT_DUE>
                      </G_LINE_CLUSTER>
                      </LIST_G_LINE_CLUSTER>
                      </G_STATEMENT>
                      </LIST_G_STATEMENT>
                      </G_SETUP>
                      </LIST_G_SETUP>
                      </ARXSGPO>

                      2. Without G_STATEMENT
                      <?xml version="1.0" encoding="UTF-8"?>

                      <ARXSGPO>

                      <LIST_G_SETUP>
                      <G_SETUP>
                      <COMPANY_NAME>test company</COMPANY_NAME>
                      <COA_ID>50348</COA_ID>
                      <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
                      <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
                      <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
                      <PARENT_REQUEST_ID>2809593</PARENT_REQUEST_ID>
                      </G_SETUP>
                      </LIST_G_SETUP>
                      </ARXSGPO>

                      Edited by: 956750 on Sep 5, 2012 12:45 AM
                      • 8. Re: How to print "there is not data found for this report" in a XML report
                        AlexAnd
                        you can try
                        <?for-each@section:G_SETUP?>
                        <?for-each@section:G_STATEMENT?>
                        <?if:count(./P_LINE_CLUSTER_ID)>0?>
                        Statement
                        <?P_LINE_CLUSTER_ID?>
                        <?end if?>
                        <?if:count(./P_LINE_CLUSTER_ID)=0?>
                        No Data Found 
                        <?end if?>
                        <?end for-each?>
                        <?end for-each?>
                        but for case when xml hasn't G_STATEMENT/STATEMENT, in my opinion, should be
                        <?xml version="1.0" encoding="UTF-8"?>
                        <ARXSGPO>
                             <LIST_G_SETUP>
                                  <G_SETUP>
                                       <COMPANY_NAME>test company</COMPANY_NAME>
                                       <COA_ID>50348</COA_ID>
                                       <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
                                       <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
                                       <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
                                       <PARENT_REQUEST_ID>2809593</PARENT_REQUEST_ID>
                                       <LIST_G_STATEMENT>
                                            <G_STATEMENT/>
                                       </LIST_G_STATEMENT>
                                  </G_SETUP>
                             </LIST_G_SETUP>
                        </ARXSGPO>
                        • 9. Re: How to print "there is not data found for this report" in a XML report
                          959753
                          It works for your example you just posted. it indeed work and print "No data found"if it has the tags like below, but unfortunalely it is not generated by EBS. Do you know why? How can we modify the data source to make it generate the tags like below when no statement?
                          <LIST_G_STATEMENT>
                          <G_STATEMENT/>
                          </LIST_G_STATEMENT>

                          Actually , the data souce of this report is oracle EBS standard report called "AR Customer Statement Report". I just create a new customer and run this report with it.Ideally, there is no statement to be printed as no transaction has happened to the customer. I check the XML file the report generated in this scenario,it is actually like below, it has no tags for G_STATEMENT like </G_STATEMENT> or </LIST_G_STATEMENT>.

                          <?xml version="1.0" encoding="UTF-8" ?>
                          - <ARXSGPO>
                          - <LIST_G_SETUP>
                          - <G_SETUP>
                          <COMPANY_NAME>test company</COMPANY_NAME>
                          <COA_ID>50348</COA_ID>
                          <FUNCTIONAL_CURRENCY>EUR</FUNCTIONAL_CURRENCY>
                          <FUNCTIONAL_CURRENCY_PRECISION>2</FUNCTIONAL_CURRENCY_PRECISION>
                          <REPORT_NAME>AR Customer Balance Statement Letter</REPORT_NAME>
                          <PARENT_REQUEST_ID>4349684</PARENT_REQUEST_ID>
                          </G_SETUP>
                          </LIST_G_SETUP>
                          </ARXSGPO>
                          • 10. Re: How to print "there is not data found for this report" in a XML report
                            AlexAnd
                            ok
                            try
                            <?for-each:G_SETUP?>
                             <?if: not(G_STATEMENT)?>
                              No data Found 
                             <?end if?>
                             <?if: (G_STATEMENT)?>
                              <?for-each:G_STATEMENT?>
                               <?if:count(./P_LINE_CLUSTER_ID)>0?>
                                Statement
                                <?P_LINE_CLUSTER_ID?>
                               <?end if?>
                               <?if:count(./P_LINE_CLUSTER_ID)=0?>
                                No Data Found 2
                               <?end if?>
                               <?end for-each?>
                              <?end if?>
                            <?end for-each?>
                            • 11. Re: How to print "there is not data found for this report" in a XML report
                              959753
                              If you add @section , it will not work. I need the @section to display the dynamic footer.
                              <?for-each@section:G_SETUP?>
                              <?if: not(G_STATEMENT)?>
                              No data Found
                              <?end if?>
                              <?if: (G_STATEMENT)?>
                              <?for-each@section:G_STATEMENT?>
                              <?if:count(./P_LINE_CLUSTER_ID)>0?>
                              Statement
                              <?P_LINE_CLUSTER_ID?>
                              <?end if?>
                              <?if:count(./P_LINE_CLUSTER_ID)=0?>
                              No Data Found 2
                              <?end if?>
                              <?end for-each?>
                              <?end if?>
                              <?end for-each?>
                              • 12. Re: How to print "there is not data found for this report" in a XML report
                                AlexAnd
                                try
                                <?for-each@ section:G_SETUP?>
                                   <?if@ section: not(G_STATEMENT)?>
                                      No data Found 
                                   <?end if?>
                                   <?if@ section: (G_STATEMENT)?>
                                      <?for-each@ section:G_STATEMENT?>
                                         <?if:count(./P_LINE_CLUSTER_ID)>0?>
                                            Statement
                                            <?P_LINE_CLUSTER_ID?>
                                         <?end if?>
                                         <?if:count(./P_LINE_CLUSTER_ID)=0?>
                                            No Data Found 2
                                         <?end if?>
                                      <?end for-each?>
                                   <?end if?>
                                <?end for-each?>
                                • 13. Re: How to print "there is not data found for this report" in a XML report
                                  959753
                                  Hi,

                                  Thanks a lot for your help. But it does not work if I use regrouping for G_STATEMENT. I try below in my template, but always display no data found regardless of statement exist in data source file or not. Did i do something wrong in below logic?

                                  <?for-each@section:G_SETUP?>
                                  <?if@ section: not(G_STATEMENT)?>
                                  No data Found
                                  <?end if?>
                                  <?if@ section: (G_STATEMENT)?>
                                  <?for-each-group@section:G_STATEMENT[STATEMENT_TYPE!='SUMMARY'];./STATEMENT_DATE?><?for-each-group@section:current-group();././SEND_TO_ADDRESS_ID?>

                                  <?SEND_TO_CUSTOMER_NAME?>
                                  <?SEND_TO_ADDRESS1?>
                                  <?for-each:current-group()?>
                                  <?for-each:G_LINE_CLUSTER?><?sort:TRX_DATE?>
                                  <?end-for-each?>
                                  <?end for-each?>
                                  <?end for-each-group?><?end for-each-group?>
                                  <?end if?>
                                  <?end for-each?>
                                  • 14. Re: How to print "there is not data found for this report" in a XML report
                                    959753
                                    Hi, could you please help me?