Hi
Do the following........ create a function in SQL as below
CREATE FUNCTION [dbo].[ufn_empnames] ( @companytype varchar(50) )
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @empnames VARCHAR(8000)
SELECT @empnames = ISNULL(@empnames + ', ', '') + [employee]
FROM cross_emp
WHERE companytype = @companytype
RETURN @empnames
END
Then code the sql as below
select
companytype,
[dbo].[ufn_empnames](companytype),--this is where you call the function created above
dept1amt+dept1amt as dept1,
dept2amt+dept2amt as dept2,
sum(dept1amt+dept2amt) as total
From cross_emp
group by companytype,dept1amt,dept2amt
companytype (No column name) dept1 dept2 dept1total
Ctype1 emp1, emp2 1000 1000 2000
Ctype2 emp4, emp5 2000 2000 4000
for vertical totals you can insert summary from crystal once the report is created.
Rahul Edited by rahulwalawalkar - 08 May 2009 at 1:10am
IP Logged
