Sunday, January 15, 2017

  A Report With No Data

  

       In the name of Allah, Most Gracious, Most Merciful 
Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that,

           The Oracle Report is a visual representation of the company data stored in one or more database tables or views. Running a report with no data does not mean you win a correct report. Today's task is to inspect the reasons for displaying a report with no data.


Problem Definition:   

      A report with no data.


Problem Reasons:

 7 Steps Reasons Leads To A Report With No Data:

1.        No data stored into the database.
2.        Invalid report query conditions.
3.        Incompatible parameter name.
4.        Incompatible parameter types.
5.        Unequal parameter numbers.
6.        Invalid data input.
7.        Invalid report name.


Problem Task Solution:

7 Steps to inspect a solution to a report with no data:

1.        Execute the report query  into SQL*PLUS or Toad.

2.        Comment each suspected query condition line by line.Then re-execute the query.      Repeat these steps until you get the data displayed.
                
            Start inspection by answering yourself these questions:
                                                    i.   Is the tables' relationships are correct.?
                                                 ii.   Is there 'null' or 'not null' condition incorrectly filter data retrieved.?                             iii.   Is  the query parameters are logically correct.?
                    iv.   Is the  query parameters format mask correct.?  
                                 v.   Is the specified value for the parameter date has data.?
                   vi.   Is there any flag that may prevent specific data from displaying.?


3.        Incompatible parameter name

               This means the report parameter name is not the same report parameter          name called in the parameter form. You may have:

                ·        A spelling mistake; Then you have to use copy & paste.
                ·       One single extra space; Due to the copy process of the report                                  parameter & the paste process into the parameter form, you may                                    mistakenly have one or more extra character space or you may copy the report parameter preceding colon ':'  in the report query as follow:


 SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'paramform=no'
                                      ||' :P_DEPT_ID='|| TO_CHAR(:DEPTS.DEPT_ID));

 Note:  The report will display empty just in case the report parameter has no                      initial value. Otherwise, it will display data upon the initial value.


4.        Incompatible parameter types

        The report parameters MUST BE compatible with the form's parameters according to Oracle Explicit & Implicit Data Type Conversion rules, e.g. Oracle treats 1500 the same as '1500'. But using TO_NUMBER  function in a  string contains non-numeric characters, the function returns an error. 


 SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'paramform=no'
                                               ||' :P_DATES='|| TO_CHAR(:EMP.HIRE_DATE, 'DD-MM-RRRR'));


Note:  All parameters in the parameter form called especially date & number parameters data type MUST BE in character data Type. Otherwise no data will be displayed. If  NOT the form item datatype is not defined as character data type you MUST convert it using TO_CHAR()  as giving in the example.


5.        Unequal parameter numbers: 

      The number of report parameters MUST BE equal to  the form's parameter numbers. This involves one or more  extra parameter report not includes in the parameter  form or the vice versa .You have to count them and remove any redundant or extra parameters.


6.        Invalid data input:

       The application user or the developer may input the wrong data in the parameter form, e.g. if the report query contains a range of  date  parameters as
  

WHERE INVOICE_DATE BETWEEN :P_START_DATE AND :P_END_DATE

  
The following is an example of wrong date input:
From: 15-01-2017   To: 01-01-2017   
  
In this case the user should enter with the following range:
From:   01-01-2017    To: 15-01-2017

You can handle the solution of  this problem  from the Oracle parameter form to enforce the user to input the correct logical date validation.

Note:  If you initially have wrong data input from the input form, then the user has to re-input the correct data to adjust your query condition.

7.        Invalid report name:

         This case rarely happens, but it may happen if you have a report name with one or two (characters or numbers ) differs from another report. So you have to pay attention to this mistake. You may also have one single space in the parameter form from coping its name in the design time this will not call the proper report name you are intending to run.  


Learn more about:

·        Calling Oracle Report 10G
·     


Hope this helps...

My success only comes from Allah, pls. note your comments and suggestions are great help for me in progress thanks in advance.