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.
A report with no data.
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:
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.
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
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:
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.