OraFAQ Forum: Reports & Discoverer » Matrix Report Help .......!!!!1

Home » Developer & Programmer » Reports & Discoverer » Matrix Report Help .......!!!!1

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Matrix Report Help .......!!!!1 [message #154364]

Sat, 07 January 2006 08:51

sameer9448
Messages: 6
Registered: November 2005

Junior Member

Hi All,

Please help me. I have a problem with my matrix report.

I want to display Hire DATE wise --> DEPT wise --> how many employees have joined and what is there Sum of salary.

My problem is that suppose on a given particular date, for a particular dept if no employees have joined then display it as
0 (Zero).

--------------------------------
hiredate (row),
dname (column),
count of emp & Sum of sal (Cell)
--------------------------------

Current Output which i am getting

Accounting Research Sales

HIRE DATE Count Count Count
01-JAN-2006 2 1
02-FEB-2006 1 1
04-MAR=2006 5

Desired Output
Accounting Research Sales

HIRE DATE Count Count Count
01-JAN-2006 0 2 1
02-FEB-2006 1 0 1
04-MAR=2006 0 5 0

Regards

Sameer

Report message to a moderator

Re: Matrix Report Help .......!!!!1 [message #154387 is a reply to message #154364]

Sat, 07 January 2006 15:27

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How did you get NULL instead of 0? COUNT function returns 0 if no records are found:

SQL> select count(*) from emp where deptno = 10 and hiredate = '08.06.05';

COUNT(*)
----------
0

As you didn't provide query (not that I'd be able to say anything smart seeing it), would it help if you include a NVL function?
Or, perhaps create a function column which will return 0 if column value is NULL?

Report message to a moderator

Re: Matrix Report Help .......!!!!1 [message #155319 is a reply to message #154387]

Fri, 13 January 2006 17:06

RJ.Zijlstra
Messages: 104
Registered: December 2005
Location: Netherlands - IJmuiden

Senior Member

create table t( nr number, vc varchar2(10) );

INSERT INTO t
(nr, vc
)
VALUES (NULL, 'j'
);
INSERT INTO t
(nr, vc
)
VALUES (NULL, 'e'
);
INSERT INTO t
(nr, vc
)
VALUES (NULL, 'e'
);
COMMIT ;
-------------
select sum(t.nr) from t ==========> NULL

select nvl(sum(t.nr),-1) from t =========> -1

----------------

So the sum can give a NULL.

Now THE question is:
what EXACTLY do you mean by the phrase 'count of emp & Sum of sal (Cell) '

Is this something like SUM and COUNT ??

Waiting for your answer,

Report message to a moderator

Re: Matrix Report Help .......!!!!1 [message #164471 is a reply to message #154364]

Thu, 23 March 2006 09:58

spinky
Messages: 21
Registered: March 2006

Junior Member

Hi Sameer,

Use srw.set_field_num(0,'0') in the format trigger of the item,in which u want to display 0(zero)..
This procedure sets the value of a number field. This is useful when you want to conditionally change a field's number value.
Hope this helps..

Thanks,
Pinky

Report message to a moderator

Previous Topic:	How to display numer in money format in reports 6i
Next Topic:	Sub Report in Oracle Report 10G

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 16:02:10 CDT 2024