Ok, so here is the issue that I have. I'm a DBA. I know SQL very well. I wrote a T-SQL script that adds employee's salary based on the months selected. It works perfectly in SQL. We have a Crystal Report Manager who knows Crystal Reports "kind of" :-). She doesn't know SQL at all. So, between the two of us, we are as lost as last year's easter egg! Basically, I need to run my t-sql script inside of Crystal, in order to generate a report. So, here is my T-SQL script:
DECLARE @StartMonth INT,
@EndMonth INT,
@Year INT
SET @StartMonth = 1
SET @EndMonth = 12
SET @Year = 2009;
WITH EmployeeCte(EMPLOYID,MONTH,YEAR,GROSWAGS)
AS (SELECT EMPLOYID,
1,
YEAR1,
GROSWAGS_1
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
2,
YEAR1,
GROSWAGS_2
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
3,
YEAR1,
GROSWAGS_3
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
4,
YEAR1,
GROSWAGS_4
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
5,
YEAR1,
GROSWAGS_5
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
6,
YEAR1,
GROSWAGS_6
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
7,
YEAR1,
GROSWAGS_7
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
8,
YEAR1,
GROSWAGS_8
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
9,
YEAR1,
GROSWAGS_9
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
10,
YEAR1,
GROSWAGS_10
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
11,
YEAR1,
GROSWAGS_11
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
12,
YEAR1,
GROSWAGS_12
FROM [UPR00900])
SELECT cast(sum(GROSWAGS) AS MONEY) AS TotalWage
FROM EmployeeCte
WHERE [Month] BETWEEN @StartMonth AND @EndMonth AND [Year] = @Year
group by EMPLOYID;
I need to be able to select in Crystal to set the varible @StartMonth and @EndMonth, and @Year, based on the selection from the user. Then show me what the TotalWage is.
Is what I'm wanting to do possible?
Thanks,
Jordon Shaw
IP Logged
