Crosstab null column/row removal

[gandugamerio]

 I am using crosstab in report .Let me put my problem with simple example.

Employee Table

FristName	DeptID
Ron	1
Sam	null

 

Department Table

DeptID	DepartmentName
1	Sales
2	Engineering

 

ReportQuery:

select FirstName,DepartmentName from employee full outer join department on employee.deptId = department.deptId

Note: I am using outer join as I need all employee and department even I don't have match.

I am using count measure so my crosstab report look like this,

 

	Sales	Engineering	null	Sum
Ron	1	0	0	1
Sam	0	0	0	0
null	0	0	0	0
Sum	1	0	0	1

 

How would I remove whole null column and row? I would like to have report like following

 

	Sales	Engineering	Sum
Ron	1	0	1
Sam	0	0	0
Sum	1	0	1

[/code]

[/code]

Post Edited by gandugamerio at 04/23/2010 18:44

Post Edited by gandugamerio at 04/23/2010 18:49