Report formating

[vp]

Hi all,

I need to create a report by extracting data from a table. I am using
the utility EXPORT to pull data from table to a file.

I will need to get the column heading and also if I want to add some
text and report date ..etc

can you please let me know how to create a report..

thanks

[Tonkuma]

Easy way is add such information by yourself in select-statement.

For example:
export to report_formating_sample.txt of del modified by nochardel
coldel:
VALUES (' Created', CHAR(CURRENT DATE))
UNION ALL
VALUES (' EMPNO ', CHAR('Full Name',20))
UNION ALL
VALUES ('------ ', '--------------------')
UNION ALL
SELECT CHAR(empno,8), CHAR(lastname || ', ' || firstnme, 20) FROM
Employee
ORDER BY 1

This command produce like following text file.
Created:2006-02-26
EMPNO :Full Name
------ :--------------------
000010 :HAAS, CHRISTINE
000020 :THOMPSON, MICHAEL
000030 :KWAN, SALLY
000050 :GEYER, JOHN
000060 :STERN, IRVING
000070 :PULASKI, EVA
000090 :HENDERSON, EILEEN
000100 :SPENSER, THEODORE
000110 :LUCCHESSI, VINCENZO
000120 :O'CONNELL, SEAN
000130 :QUINTANA, DOLORES
000140 :NICHOLLS, HEATHER
000150 :ADAMSON, BRUCE
......

[Tonkuma]

Another example:
export to report_formating_sample2.txt of del modified by nochardel
VALUES (' Created:'||CHAR(CURRENT DATE))
UNION ALL
VALUES (' EMPNO Full Name Salary')
UNION ALL
VALUES ('------- -------------------- ----------')
UNION ALL
SELECT CHAR(empno,8) || CHAR(lastname || ', ' || firstnme, 20)
||SUBSTR(RIGHT('
'||TRANSLATE(LTRIM(TRANSLATE(RTRIM(CHAR(salary)),' ','0')),'0', '
'),11),1,11)
FROM Employee
ORDER BY 1;
------------------------------------------------------------------------------
SQL3104N The Export utility is beginning to export data to file
"report_formating_sample2.txt".

SQL3105N The Export utility has finished exporting "35" rows.

Number of rows exported: 35

Copy of output file "report_formating_sample2.txt" is followings.

Created:2006-02-26
EMPNO Full Name Salary
------- -------------------- ----------
000010 HAAS, CHRISTINE 52750.00
000020 THOMPSON, MICHAEL 41250.00
000030 KWAN, SALLY 38250.00
000050 GEYER, JOHN 40175.00
000060 STERN, IRVING 32250.00
000070 PULASKI, EVA 36170.00
000090 HENDERSON, EILEEN 29750.00
000100 SPENSER, THEODORE 26150.00
000110 LUCCHESSI, VINCENZO 46500.00
000120 O'CONNELL, SEAN 29250.00
000130 QUINTANA, DOLORES 23800.00
000140 NICHOLLS, HEATHER 28420.00
000150 ADAMSON, BRUCE 25280.00
......

[--CELKO--]

The basic principle of a tiered architecture is that you do reporting
and display function in the front end. This has nothing to do with the
database.

[kenfar]

Sure, but everyone should know how to make the occasional report out of
the database - if only because once in a while you'll want a single
fully automated report, and won't feel like trying to get a
cognos/brio/crystal/whatever purchase pushed through purchasing. In
this case the economics are clearly in favor of doing something small
mostly in the database.

But having said that, i still find it easier to do the formatting
outside of sql:
1. export data in pipe-delimited format
2. use a generic script (python, perl, etc) to convert data file to
html
3. wrap process in ksh/bash script that also emails
4. schedule script via cron

It's very low-tech, but extremely simple way to put together simple
static reports.

ken