Home » Developer & Programmer » Reports & Discoverer » can anyone solve this problem
can anyone solve this problem [message #236769] Thu, 10 May 2007 23:40 Go to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
Hi All,
I have done a matrix report which shows the profit,loss and net for a currency and for different months.
When there is profit for say month april and when there is no profit or loss in the month may, then for the month may, all fields are joined as one and it shows nothing since there is no transactions done in the month of may... How to solve this problem,so that i want to show 0 in the fields of profit and loss.
Thanks in advance
Re: can anyone solve this problem [message #236781 is a reply to message #236769] Fri, 11 May 2007 00:32 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
can anyone solve this problem Are you giving a challenge to everyone or what?

Ashu
Re: can anyone solve this problem [message #236797 is a reply to message #236781] Fri, 11 May 2007 01:01 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
my title may insist like that. but i dont mean like that... Request you all to help me out.
Re: can anyone solve this problem [message #237039 is a reply to message #236797] Fri, 11 May 2007 12:43 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What product are you using (Report/Discoverer/other)? Version?
Maybe it would help if you provide us with some sample data and an example of the desired output.
Re: can anyone solve this problem [message #237076 is a reply to message #237039] Fri, 11 May 2007 15:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
... i want to show 0 ...


A blind guess: would the NVL function do any good?
SQL> create table test (id number, salary number);

Table created.

SQL> insert all
  2    into test values (1, 100)
  3    into test values (2, null)
  4    into test values (3, 300)
  5  select * from dual;

3 rows created.

SQL> -- this might be your current situation:
SQL> select id, salary from test order by id;

        ID     SALARY
---------- ----------
         1        100
         2
         3        300

SQL> -- this might be your improved query:
SQL> select id, nvl(salary, 0) salary from test order by id;

        ID     SALARY
---------- ----------
         1        100
         2          0
         3        300
Re: can anyone solve this problem [message #237271 is a reply to message #237076] Mon, 14 May 2007 00:56 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
The solution given does not fulfill my requirement. When you do a matrix report, we get no_data_found for some rows. how to show zero in those fields.,..
Re: can anyone solve this problem [message #237373 is a reply to message #237271] Mon, 14 May 2007 05:08 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you sure it is the reason for the NO-DATA-FOUND error? Do you, perhaps, have a formula column which does something like
select name into l_name
  from some_table 
  where id = <non-existent id from your matrix report>;
and this query really returns NO-DATA-FOUND?

If so, write an exception handler which will deal with this error. Or, use an aggregate function such as
select MAX(name) into ...
which will also do the job (so that you wouldn't need to write an exception handler) as it would return NULL instead of NO-DATA-FOUND. How to deal with NULL values, you've seen in my previous post.

However, if none of this applies to your problem, I'm afraid I don't have any other idea at the moment.
Re: can anyone solve this problem [message #237607 is a reply to message #237373] Tue, 15 May 2007 03:29 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
The problem is in a matrix report,i have to show the blank spaces with a value. For that, i created a text field and moved it backward and kept in the same level of the repeating frame.(As we see in the object navigator). But the values are displayed even in the places where it is not blank...

how to solve it....
Re: can anyone solve this problem [message #237612 is a reply to message #237607] Tue, 15 May 2007 03:53 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, if there's no value and you'd like to have one, use the NVL function. I don't know what else to say.
Re: can anyone solve this problem [message #237621 is a reply to message #237612] Tue, 15 May 2007 04:21 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
its ok... thanks for your patience..
Re: can anyone solve this problem [message #238125 is a reply to message #236769] Wed, 16 May 2007 14:52 Go to previous message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Chapter 15 of IvanBayross 's Book (ISBN 81-7656-068-5) present the solution of your problem.

It is something about typing text '0' below the "profit_loss" field and moving it back. Next, fill the "profit_loss" field with white color (given that report background is also white).

Now, if the value of "profit_loss" is calculated and result is non-zero then it will be come forward and will hide the text '0' behind it.
and in case when "profit_loss" is calculate and result is zero/null/blank then behind typed zero will be visible.

Hence your solution.

Previous Topic: Exporting to excel
Next Topic: Report Output in Second Page
Goto Forum:
  


Current Time: Fri Dec 09 07:53:30 CST 2016

Total time taken to generate the page: 0.12546 seconds