Home » Developer & Programmer » Reports & Discoverer » Matrix report (reports 6i)
Matrix report [message #605620] Sun, 12 January 2014 06:27 Go to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
Dear
i want to display all dates(heading) according to months in matrix style report. and fill the all column with p and A. but in this report which is attached some columns are blank please advised how i can correct this problem..
./fa/11600/0/
Re: Matrix report [message #605632 is a reply to message #605620] Sun, 12 January 2014 14:32 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the problem, exactly? You said you want to fill "columns" with P or A. What prevents you from doing that?

Why is, for example, cell [ID = 1, Name = M. MUBEEN, Month Date = 05] (that is the first upper left cell) empty? If it is not P and it is not A, what is it, then?
Re: Matrix report [message #605634 is a reply to message #605632] Sun, 12 January 2014 21:35 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
05, 21, 22,29, 30 is the days of month and the student present in those days will be (P). the students which is not present in those days autometically assign (A). But when i run this query is will shows the result which i attached.
SELECT DISTINCT max(substr(inout.dated,1,2)) dat, student.stuid, max(intime) intime,student.name
FROM student,inout 
WHERE student.stuId= inout.stuID(+)
group by student.stuid,NAME
order by stuid

Student is Master table and inout is detail table.
./fa/11601/0/
Re: Matrix report [message #605638 is a reply to message #605634] Mon, 13 January 2014 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

students which is not present in those days autometically assign (A)


Who assigns that? Your application, or is report supposed to do so?
Re: Matrix report [message #605643 is a reply to message #605638] Mon, 13 January 2014 01:13 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
a formula column as shown in previous picture.
Re: Matrix report [message #605646 is a reply to message #605643] Mon, 13 January 2014 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What code did you write into the formula column?
Re: Matrix report [message #605647 is a reply to message #605646] Mon, 13 January 2014 01:50 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
function statFormula return Char is
a number;
begin
  if :dat is not null  and :intime is not null then
  	 return('P');
  Else
  	 return('A');
  end if;
  end;

i want on specific :dat the student which have teir attendance assign (P) and which have not attendance on that specific date autometically assign (A).. but the students haveing presence on that day it shows (p) accurately but the (A) present in another column which has no date as shown in the figure.
./fa/11602/0/
Re: Matrix report [message #605649 is a reply to message #605647] Mon, 13 January 2014 02:08 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure what INTIME is; how about
  retval varchar2(1);
begin
  if :dat is not null then
     if :intime is not null then
        retval := 'P';
     else
        retval := 'A';
     end if;
  end if;

  return (retval);
end;
Re: Matrix report [message #605650 is a reply to message #605649] Mon, 13 January 2014 02:24 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
intime is the attendance time of the student on distinct date. please check that main query;

SELECT DISTINCT max(substr(inout.dated,1,2)) dat, student.stuID, max(intime) intime,student.name
FROM STUdent,inout 
WHERE student.stuId= inout.stuID(+)
group by student.stuid,NAME
order by stuid

please check that main query.

the code you have send is the showing nothing.....why the file is not attached... i have uploded.

[Updated on: Mon, 13 January 2014 02:27]

Report message to a moderator

Re: Matrix report [message #605651 is a reply to message #605650] Mon, 13 January 2014 02:29 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, "main query" (or any code) you posted doesn't mean much as we don't have your tables nor data stored in them.
Re: Matrix report [message #605652 is a reply to message #605651] Mon, 13 January 2014 02:34 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
attached table structure...
SQL> desc student
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STUID                                     NOT NULL NUMBER(8)
 STATUS                                             VARCHAR2(30)
 CLASS                                              VARCHAR2(25)
 SECTION                                            VARCHAR2(30)
 SESION                                             VARCHAR2(30)
 ROLLNO                                             NUMBER(4)
 ADM_NO                                             NUMBER(7)
 STUDENTID                                          VARCHAR2(15)
 NAME                                               VARCHAR2(250)
 F_NAME                                             VARCHAR2(250)
 MNAME                                              VARCHAR2(250)
 GENDER                                             VARCHAR2(15)
 FCNIC                                              VARCHAR2(25)
 MCNIC                                              VARCHAR2(25)
 MOB                                                VARCHAR2(20)
 RES_PH                                             VARCHAR2(20)
 ADDRES                                             VARCHAR2(450)
 PROFESSION                                         VARCHAR2(120)
 DOA                                                DATE
 DOB                                                DATE
 PRVSCHOOL                                          VARCHAR2(100)
 PRVCLASS                                           VARCHAR2(30)
 LEAV_DATE                                          DATE
 BOARD                                              VARCHAR2(100)
 GRUP                                               VARCHAR2(50)
 BRDREGNO                                           VARCHAR2(35)
 GURD_NAME                                          VARCHAR2(250)
 GMOBILE                                            VARCHAR2(20)
 TUITION                                            NUMBER(5)
 TRNID                                              NUMBER(4)
 TRANSP                                             NUMBER(6)
 COMENT                                             VARCHAR2(130)
 BAYFORM                                            VARCHAR2(25)
 ADM_CLAS                                           VARCHAR2(30)
 BRDROLL                                            NUMBER(11)
 CONS_TYPE                                          VARCHAR2(60)
 MOB1                                               VARCHAR2(25)
 COMENT1                                            VARCHAR2(300)
 RELIGION                                           VARCHAR2(50)
--------------------------
SQL> desc inout;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 STUID                                              NUMBER(8)
 INTIME                                             VARCHAR2(60)
 OTIME                                              VARCHAR2(60)
 DATED                                              DATE

[Updated on: Mon, 13 January 2014 02:34]

Report message to a moderator

Re: Matrix report [message #605655 is a reply to message #605652] Mon, 13 January 2014 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
SELECT DISTINCT max(substr(inout.dated,1,2)) dat, student.stuID, max(intime) intime,student.name
FROM STUdent,inout 
WHERE student.stuId= inout.stuID(+)
group by student.stuid,NAME
order by stuid


This query is fundamentally flawed in a few ways:
1) It'll only give at most one record per student.
2) The returned intime may belong to a different record to the returned dated
3) You're relying on the default date format to get the day of the month.

If you've got a function that must return a value (and a simple if/else must) and you're still getting nulls that must mean the function isn't always getting run.
It's not getting run becuase of issue 1.

You need to join the students table to a calender query that will return all the dates you are interested in, e.g.
SELECT trunc(sysdate, 'MON') + rownum FROM dual connect by level < 31


Issue 3 can be fixed by using to_char instead of substr.
Re: Matrix report [message #605664 is a reply to message #605655] Mon, 13 January 2014 04:28 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
please advised me.
Re: Matrix report [message #605673 is a reply to message #605664] Mon, 13 January 2014 06:11 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you think my post is if it's not advise?

If there's something you don't understand you need to say what.
If you have further questions you need to ask them.
Re: Matrix report [message #605674 is a reply to message #605673] Mon, 13 January 2014 06:15 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
how i can sort out this thing. wether i cnage the query or chnage the formula column function.(why the file is not attached). with orafaq.???
Re: Matrix report [message #605676 is a reply to message #605674] Mon, 13 January 2014 06:47 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to change the query so that it returns one row per student per date.
Re: Matrix report [message #605680 is a reply to message #605676] Mon, 13 January 2014 08:30 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
Dear sir,
i have changed the query. the result is as follows: i have only one problem is that it is showing P and A at the same time. i want if one stuid has intime present in ths school then no A is shown. ./fa/11612/0/
Re: Matrix report [message #605681 is a reply to message #605680] Mon, 13 January 2014 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
I gave you a general suggestion for changing the query not actual code.
So I have no idea what you changed the query to.
It's really hard to help if you don't tell us what you've actually done.
Re: Matrix report [message #605682 is a reply to message #605681] Mon, 13 January 2014 08:45 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
select max(to_char(dated,'DD')) dat,count(intime),student.stuid,student.name
from inout, student
where student.stuid=inout.stuid(+)
group by student.stuid,name
order by student.stuid

Re: Matrix report [message #605684 is a reply to message #605655] Mon, 13 January 2014 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Mon, 13 January 2014 08:55
You need to join the students table to a calender query that will return all the dates you are interested in, e.g.
SELECT trunc(sysdate, 'MON') + rownum FROM dual connect by level < 31



[Updated on: Mon, 13 January 2014 09:07]

Report message to a moderator

Re: Matrix report [message #605724 is a reply to message #605684] Mon, 13 January 2014 21:04 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
please tell me how i can join this calender query; i have create a serprate query or it will be created in main query.
Re: Matrix report [message #605728 is a reply to message #605724] Mon, 13 January 2014 23:48 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The way I understood it, I think that the problem isn't "how to display all dates in a month" (i.e. how to fetch those that are missing, i.e. calendar-type query), but how to display A or P for a certain person on a certain date.

Of course, I might be wrong.

Therefore: forget about formula columns. Forget about matrix report. In SQL*Plus, write a query which will return 3 columns:
- student name (future matrix report rows)
- date (future matrix report columns)
- status (A or P) (future matrix report cells)

Once you manage to do that in SQL*Plus, copy/paste that report into Reports Builder, let Wizard create matrix layout and - once you make it work - feel free to make it fancy.
Re: Matrix report [message #605760 is a reply to message #605728] Tue, 14 January 2014 07:23 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot wrote on Tue, 14 January 2014 05:48
The way I understood it, I think that the problem isn't "how to display all dates in a month" (i.e. how to fetch those that are missing, i.e. calendar-type query), but how to display A or P for a certain person on a certain date.

Of course, I might be wrong.


OP wants there to be an A or P for every single student for every single relevant date. Unless matrix reports do something I don't know about to fill in the gaps (possible) he needs a query that returns a row per student per relevant date. The blanks in the output will be where there is no intime record for that student for that date.


@shahzad-ul-hasan - This can be done in a single query. As LF says get something working in sqlplus - a query that outputs one row per stundent per date and indicates if they're present or absent - you can then put that query in the matrix report and it should just work.

Re: Matrix report [message #605767 is a reply to message #605760] Tue, 14 January 2014 07:55 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
select distinct s.stuid,s.name,max(i.dated),count(i.intime)
from student s, inout i
where i.stuid(+)=s.stuid
group by s.name,s.stuid
order by s.stuid

this query works fine in the sql *plus but in report is shows as follow:
./fa/11622/0/
Re: Matrix report [message #605775 is a reply to message #605767] Tue, 14 January 2014 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
It may run in sqlplus but I guarantee it doesn't give one row per student per date.
The number of rows the query needs to return = number of students x number of dates you want to display.
inout doesn't contain one row per student per date you want to display does it? It's missing rows for some students for some dates, presumably the dates the students didn't attend.

As previously stated you need to join this query to a select that gets all the dates you are interested in.

Simple example

SQL>   CREATE TABLE emp AS SELECT ROWNUM ID FROM dual CONNECT BY LEVEL < 4;
 
Table created
 
SQL> select * from emp;
 
        ID
----------
         1
         2
         3
 
SQL> --now a query to get some dates
SQL> SELECT trunc(SYSDATE) + ROWNUM FROM dual CONNECT BY LEVEL < 5;
 
TRUNC(SYSDATE)+ROWNUM
---------------------
15/01/2014
16/01/2014
17/01/2014
18/01/2014
 
SQL> --join the two
SQL> 
SQL> WITH cal AS (SELECT trunc(SYSDATE) + ROWNUM dat FROM dual CONNECT BY LEVEL < 5)
  2  SELECT ID, dat
  3  FROM emp, cal
  4  ORDER BY 1,2;
 
        ID DAT
---------- -----------
         1 15/01/2014
         1 16/01/2014
         1 17/01/2014
         1 18/01/2014
         2 15/01/2014
         2 16/01/2014
         2 17/01/2014
         2 18/01/2014
         3 15/01/2014
         3 16/01/2014
         3 17/01/2014
         3 18/01/2014
 
12 rows selected
 
SQL> 


Once you've got a query that gets one row per student per date you then need to outer join inout to it to see if the student was present on a given date.
Re: Matrix report [message #605885 is a reply to message #605775] Wed, 15 January 2014 21:02 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
please see the attached file../fa/11630/0/
Re: Matrix report [message #605890 is a reply to message #605885] Thu, 16 January 2014 00:45 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your database version is ...?

You might need to wrap that code into
select result
from (select trunc(sysdate) + rownum result
      from dual
      connect by level < 5
     );
Re: Matrix report [message #605899 is a reply to message #605890] Thu, 16 January 2014 02:37 Go to previous messageGo to next message
cookiemonster
Messages: 10904
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also what's your sqlplus version?
Re: Matrix report [message #605911 is a reply to message #605899] Thu, 16 January 2014 04:14 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jan 16 15:13:43 2014

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Re: Matrix report [message #605912 is a reply to message #605911] Thu, 16 January 2014 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So? Did you do what I told you to?
Re: Matrix report [message #605913 is a reply to message #605912] Thu, 16 January 2014 04:29 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
yes it shows variant dates.now further...
Re: Matrix report [message #605922 is a reply to message #605913] Thu, 16 January 2014 05:18 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Further what? Cookiemonster told you:

Quote:

Once you've got a query that gets one row per student per date you then need to outer join inout to it to see if the student was present on a given date.
Re: Matrix report [message #605970 is a reply to message #605922] Thu, 16 January 2014 21:49 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
how it(outer join query) can be used..
Re: Matrix report [message #605975 is a reply to message #605970] Fri, 17 January 2014 00:58 Go to previous message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Joins.
Previous Topic: Provide download link to blob in database on Oracle Reports?
Next Topic: Broken Images are displayed in discoverer 11g
Goto Forum:
  


Current Time: Thu Aug 21 23:54:51 CDT 2014

Total time taken to generate the page: 0.08799 seconds