X

An Oracle blog about BI Publisher

  • June 19, 2007

No Data Found

Back and scanning the forum for questions that are worthy of an anser here on the blog, I found one thats pretty relevant to us all. If a report returns no data then we need to inform the reader rather than leave them with a blank sheet of paper wondering if something went wrong.



Here's the question from Marius








Hi all, i have the following situation. I'am trying to print NO_DATA_FOUND where the sql statement is returning no rows and I took this approach:
---------------------------------------------------------------------------------------
<?choose:?><?when:ROW/INVOICE_NO!=''?>
<?for-each-group@section:ROW;./INVOICE_NO?>

..............body................

<?end for-each?>
<?end when?><?otherwise:?>
NO_DATA_FOUND
<?end otherwise?><?end choose?>

------------------------------------------------------------------------------------------

But ther problem is this is not working because of "@section". If I'm removing @section it is working...... but in my case I have to keep "@section" because of the dynamic data from HEADER section. Any body knows where is the error or....maybe you know another approach to show that error.




Let's leave Marius' @section problem for a minute and take a step back to a simpler situation. Assume we have the following XML:


<G_CUSTOMER>
 <CUSTOMER_NAME>A. C. Networks</CUSTOMER_NAME>
 <ZIP>84606</ZIP>
 <STATE>UT</STATE>
 <ADDRESS_LINE2/>
 <ADDRESS_LINE1>3405 East Bay Blvd.</ADDRESS_LINE1>
 <COUNTRY>US</COUNTRY>
 <CITY>Provo</CITY>
 <CUSTOMER_NUMBER>1143</CUSTOMER_NUMBER>
 <G_INVOICES>
  ...
 </G_INVOICES>
 <G_INVOICES>
  ...
 </G_INVOICES>
 <G_INVOICES>
  ...
 </G_INVOICES>
 <G_INVOICES>
  ...
 </G_INVOICES>
 </G_CUSTOMER>
<G_CUSTOMER>
 <CUSTOMER_NAME>Networks Inc</CUSTOMER_NAME>
 <ZIP>93934</ZIP>
 <STATE>CO</STATE>
 <ADDRESS_LINE2/>
 <ADDRESS_LINE1>8762 Rawlins Road</ADDRESS_LINE1>
 <COUNTRY>US</COUNTRY>
 <CITY>Castle Rock</CITY>
 <CUSTOMER_NUMBER>1143</CUSTOMER_NUMBER>
 <G_INVOICES/>
</G_CUSTOMER>


Notice the second CUSTOMER has a no INVOICES present. We can test for that and replace an invoice section with a 'No Data Found' string.



The 'if' statement in XSL does not have a 'then else' format i.e. all we have is 'if expr end if'. There is an alternative, the 'choose' statement, its a little verbose but we can use it.



<?choose:?>
 <?when:count(TRX_NUMBER) > 0?>
     Invoice Table
 <?end when?>
 <?otherwise:?>
     No Data Found
 <?end otherwise?>
<?end choose?>



Notice all we are doing is counting how many instances of TRX_NUMBER are present. If there are one or more present then the Invoice Table is rendered otherwise we get the 'No Data Found' string.



Getting back to Marius' situation, there we need to test the CUSTOMER level in our data, we can either test for a member of the CUSTOMER level or test for the CUSTOMER level itself.



<?for-each@section:G_CUSTOMER?>
<?choose:?>
<?when:CUSTOMER_NAME!=鋳?> or <?when:G_CUSTOMER>

    Customer Header Info

    Invoice Table

<?end when?>
<?otherwise?>
No Data Found
<?end otherwise?>
<?end choose?>



Sample files available here.

Join the discussion

Comments ( 3 )
  • Gareth Roberts Wednesday, June 20, 2007
    Another option to "choose" is multiple if statements. 'if expr end if' then 'if not expr end if'
    Gareth
  • Tim Dexter Wednesday, June 20, 2007

    Good point Gareth!

  • Rakesh Sreenivasa Monday, July 22, 2013

    Hello Tim,

    The No Data found do not work when we are using cross-tab report, it leads to exception as shown below. Please let me know for a solution if any.

    Caused by: oracle.xdo.parser.v2.XPathException: Extension function error: Method not found 'create_groups'

    at oracle.xdo.parser.v2.XSLStylesheet.flushErrors(XSLStylesheet.java:1534)

    at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:521)

    at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:489)

    at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:271)

    at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:155)

    at oracle.xdo.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.