I have a report which I have to complete every quarter. The report is done
in Excel and is called, "Agency Training Metrics Report". There are 20
categories which our training courses fall in. The report is broken into 2
sides, Supervisor and Non-Supervisor and lists all the categories in rows
and has 6 columns, A, B, C, D, E, F which represent race codes,

A= White
B= Black
C= Hispanic
D= Asian/Pacific Islander
E= American Indian
F= Unknown

It also looks for Male and Female,

In essence, the report is for showing the totals between all the variables,
i.e. All Black Male Non-Supervisors who have had a training course in
Executive Development category OR All Hispanic Female Supervisors who have
had a training course in Business Practices, Quality and Competition
category.

This report is cumbersome and long, but I developed 2 Stored Procedures to
give me this data and outputted it into a Access ADP file for reporting. I
would like to skip that step and try to get the data directly into the Excel
file instead.

The stored procedures that I developed are the same, except one looks for
Supervisors and the other looks for Non-Supervisors. Currently my SP only
returns the Category and Employee # for each course taken. I'm not sure how
to output all the data needed. Here are my SP's,

CREATE PROCEDURE spMetricsCollection_Supervisors
@EmpSex varchar(1),
@EmpRace char(1),
@StartDate datetime,
@EndDate datetime
AS
SELECT E.EmpID, CAT.Cat
FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
ClassesTaught CT ON C.CourseTitle = CT.Title
INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
WHERE EP.EmpSex = @EmpSex AND
(CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
@StartDate AND @EndDate) AND
EP.EmpRace = @EmpRace AND P.PosSupervisor = 1
ORDER BY EmpLName
GO

CREATE PROCEDURE spMetricsCollection_NonSupervisors
@EmpSex varchar(1),
@EmpRace char(1),
@StartDate datetime,
@EndDate datetime
AS
--Supervisors
SELECT E.EmpID, CAT.Cat
FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
ClassesTaught CT ON C.CourseTitle = CT.Title
INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
WHERE EP.EmpSex = @EmpSex AND
(CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
@StartDate AND @EndDate) AND
EP.EmpRace = @EmpRace AND P.PosSupervisor IS NULL
ORDER BY EmpLName
GO

Can I accomplish this with DTS? After my primer (with mounds of help from
this group) I feel pretty comfortable with DTS, but still am not sure how to
go about doing this. The Excel file has calculations and is formatted, so
it's not like a blank file.

Thanks,
Drew Laing

Drew, Yes, DTS should be able to do this. Do you have a hotmail, yahoo, aol,
or gmail account? I need a little more information about requirement before I
can propose a solution. Thank you.

"Drew" wrote:

Yes... I have a gmail account, dr00bertNOSPAMgmailDOTcom (note: two zeros
instead of o's)

replace NOSPAM and DOT

Drew

"frank chang" <frankchang (AT) discussions (DOT) microsoft.com> wrote