sql report not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

sql report not working

Author

Topic

Dek
Starting Member

7 Posts

Posted - 2001-12-05 : 11:30:21

Why is this query not producing the sum total on department as expected. Quite clearly I have developed the report and using the compute command as asked. I want to compute the sum salary for each department.

SET NULL 0.00
COL my_column1 format 9,999.99
col my_column2 format 9,999.99
col total format 99,999.99
col my_column1 HEADING 'Monthly Salary'
col my_column2 HEADING 'Annual Comm'
TTITLE -
SKIP 1 CENTER 'Emplyee Report' -
SKIP 2
BREAK on deptno skip page /* Needed as per compute command */
compute sum LABEL 'total' of my_column1, my_column2, total ON deptno
BTITLE 'Company Confidential'
select deptno "Dept", job "Job", ename "Name", sal my_column1 , commission my_column2, sal*12+(nvl(comm,0)) "Total" from employer
order by deptno;

The report that I want to produce is something similar to this:

Date Page 1
Employee Report

Monthly Annual
Dept Job Name salary Comm Total
---- ----- ---- --------- ------ ----------
10 CLERK MILLER 20,000.00 0.00 240,000.00
MANAGER Clark 30,000.00 0.00 360,000.00

... ... ... ... ... ...
***** ******** -------- ------ -----------
900,000.00 200,000 1,100,000.00
Company Confidential

DATE Page 2

Employee Report

Monthly Annual
Dept Job Name salary Comm Total
---- ----- ---- --------- ------ ----------
20 SALES MILLER 30,000.00 0.00 360,000.00
MANAGER Clark 40,000.00 0.00 480,000.00

... ... ... ... ... ...
***** ******** -------- ------ -----------
360,000.00 100,000 460,000.00

company confidential

Dek
Starting Member

7 Posts

Posted - 2001-12-05 : 16:01:07

Here is cut down version of the above quer to calculate the sum total on department:

col my_column1 HEADING 'Monthly | Salary'
col my_column2 HEADING 'Annual | Comm'
BREAK on deptno skip 1
compute SUM OF my_column1, my_column2, total ON deptno
/* note left out deptno column heading
select deptno , job "Job", ename "Name", sal my_column1 , comm my_column2, sal*12+(nvl(comm,0)) "Total" from emp
order by deptno;

/*
this query calculates the total for my_column1,
my_column2??? why not???
it calculates total for total

note left out the column heading for deptno
*/

mono
Starting Member

36 Posts

Posted - 2001-12-05 : 16:31:50

Dek,

COL, BREAK, LABEL, etc: this isn't TSQL. What is it?

mono

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-05 : 17:16:32

Smells like Oracle to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources