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.

Comments:

Another option to "choose" is multiple if statements. 'if expr end if' then 'if not expr end if' Gareth

Posted by Gareth Roberts on June 19, 2007 at 06:14 PM MDT #

Good point Gareth!

Posted by Tim Dexter on June 20, 2007 at 06:56 AM MDT #

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.

Posted by Rakesh Sreenivasa on July 22, 2013 at 04:35 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today