| Ora Rpts, show text message if no data found [message #78376] |
Mon, 18 February 2002 05:59  |
Steve Jackson
Messages: 5
Registered: February 2002
|
Junior Member |
|
|
Hello all, I would like to show a boilerplate text message on my report if the query returns no rows. Oracle training material says to add a summary column, then check the value of it in a format trigger, and make the message visible if summary column = zero.
But when I try that, I get wrong-frequency error. Summary column is inside repeating frame, and boilerplate text is outside the repeating frame, which seems to cause the frequency error.
Any ideas how to solve this? Report is simple one table tabular query, it is not master-child.
Thanks, Steve Jackson
p.s. I searched the archives, did not find the answer. If this has already answered before, pls refer me to right date or url. Thx.
|
|
|
|
| Re: Ora Rpts, show text message if no data found [message #78378 is a reply to message #78376] |
Mon, 18 February 2002 06:48  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
1.create a summary column. let us say cs_1 on count on some column of your query
2.create a place holder column let us say cp_dummy of type char 20
3.create a field just beneath the repeating frame (not inside it ..outside) let us say f_1
4.set property of f_1 as : from =cp_dummy
5.right click on f_1 and choose program unit editor and write following code
-----------
function F_1FormatTrigger return boolean is
begin
if :cs_1 = 0 then
srw.set_field_char(0, 'No data available');
return (TRUE);
else
return (FALSE);
end if;
end;
---------
|
|
|
|
| Re: Ora Rpts, show text message if no data found [message #78383 is a reply to message #78376] |
Mon, 18 February 2002 10:57 |
Steve Jackson
Messages: 5
Registered: February 2002
|
Junior Member |
|
|
I followed your suggestions, added the summary and placeholder columns, and still get this error message:
REP-1213: Field 'f_1' references column 'CP_DUMMY' at a frequency below its group.
I am running Oracle Report Builder 6.0.8.8.3 on Sun Solaris.
Any help will be appreciated. Thank you, Steve Jackson
|
|
|
|
|
|
|
|
| Problem Solved!!, Re: show text message if no data found [message #78391 is a reply to message #78386] |
Tue, 19 February 2002 05:21 |
Steve Jackson
Messages: 5
Registered: February 2002
|
Junior Member |
|
|
Pratap, thank you for example. I found the problem.
My summary column cs_1 was in the wrong place. When I first created it, I put it in the box with the rest of the query columns in the data. This was in the Data Model of the Report Editor. The cs_1 summary column did not show under "summary columns" in the object navigator, it was listed under a group associated with the query.
So I deleted the summary column, and created a new one. But this time I clicked an area on the data model outside the box with the query. Then the summary column is listed under "summary columns" in the object navigator. After that my code worked with no error msg.
For anyone else reading through this thread, I was also able to simplify pratap's solution. I did not create or use a placeholder. I just created a boilerplate text outside of the repeating frame, and placed code in the format trigger to check :cs_1 value. If :cs_1 = 0 then return (true) else return(false). And the reverse on the frame holding the column headings.
(actually I named the summary column cs_count_rows, but in this example I refer to it as cs_1 for anyone following the thread).
Thank you, I appreciate your help greatly, Steve J
|
|
|
|
|
|
|
|
| Show 0 if no data found [message #146677 is a reply to message #78393] |
Sat, 12 November 2005 02:49 |
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
|
Senior Member |
|
|
I want to show 0 if the query does not return an value. How to do it. I have four different query in my query editor out of which one is not returning value but others are returning value. I want to show that value which is not returning value to zero.
Please help it is urgent
Samit Gandhi
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
