On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, June 8, 2008

Report Headings

To create report titles you can use Ttitle, Repheader, or Prompt.

TTITLE

With Ttitle, you can use system variables such as sql.pno. xToday and xTime is set in ‘GetDate.Sql.' These variables print at the top of every ‘page', which in this case, because of the break on Deptno is at the top of every department.
Define xTitle = 'Employees By Department'
Define xSpool = TitleTest3.Txt
@GetDate
------------------------------------------------------------------------
Ttitle -
Left 'User: ' SQL.USER Col 20 xTitle Col 50 Xtoday Xtime Skip 1 -
Left 'Page: ' Format 999 sql.pno Col 50 ' Spool: ' xSpool Skip 2 -
Left 'Dept: ' Format 99 xDeptno' - 'Format a20 xDept Skip 2
------------------------------------------------------------------------
@TitleRpt


REPHEADER
Repheader displays on the first page of the report. You can still use Ttitle to make subtitles. This would be great except that it insists on printing the title first (see highlighted lines).
Define xTitle = 'Employees By Department'
Define xSpool = TitleTest4.Txt
@GetDate

----------------------------------------------------------------------
repheader page -
Left 'User: ' SQL.USER Col 20 xTitle Col 50 Xtoday Xtime Skip 1 -
Left 'Page: ' Format 999 sql.pno Col 50 ' Spool: ' xSpool Skip 2

ttitle -
Left 'Department: 'Format 99 xDeptno' - 'Format a20 xDept Skip 2
-----------------------------------------------------------------------

Spool &xSpool
@TitleRpt


PROMPT

The Prompt will display on the screen (if Term is on) and also to the report if you are spooling at the time. You can't use the system variables. You have to use the '&' in front of the variables.
Define xTitle = 'Employees By Department'
Define xSpool = TitleTest5.Txt
@GetDate
---------------------------------------------------------------
Spool &xSpool

Prompt User: &xUser &xTitle &Xtoday&Xtime
Prompt Spool: &xSpool
Prompt

ttitle Left 'Department:'Format 99 xDeptno' - ' xDept Skip 2
---------------------------------------------------------------
@TitleRpt


GETDATE.SQL
Set Term Off
Column User New_Value xUser
Column ThisDay New_Value xToday
Column ThisTime New_Value xTime

Select User,
To_Char(Sysdate,'fm Month dd, yyyy Day') as ThisDay,
To_Char(Sysdate,' HH24:mi') as Thistime
From Dual;
Set Term On


TITLERPT.SQL
Break On DeptNo Skip Page

Column DeptNo New_Value xDeptNo NoPrint
Column Dept New_Value xDept NoPrint

Select EmpNo, EName, Job, Sal, HireDate, Mgr,
DeptNo, DName||', '||Loc as Dept
From Emp
Join Dept
Using (DeptNo)
Order By DeptNo;

Spool Off

No comments:

Post a Comment