Hiding a column in matrix Report

Hi ,

I have a matrix report and it works fine.

07/31/200708/01/2007 07/30/2007 Adams , William1 1 00 Alqueseva , JC000 0 Anderson , David2 001 Asseng , Steve1 000 Bangerd , Michael1 2 01 Barnaskas , Joe000 0 Barry , Timothy2 1 00 Berry , Jerry01 01 Bowen , Nels000 0 Breakall , Jeff03 01 Brockel , Robert1 2 00 Campbell , Bryan000 0 Castro , Carlos000 0 Childs , Michael3 1 00 Clawson , Donald000 0 Conklin , Terry2 2 02

The above is the report generated.

I need to hide the column with null date... Is it possible?

Hi Lavanya,

You can use theFilter options for the matrix to remove such columns -http://msdn2.microsoft.com/en-us/library/ms187062.aspx

or of the dataset -http://msdn2.microsoft.com/en-us/library/ms183549.aspx

|||

Thanks for replying.That didnt solve my problem.

I will explain the requirement I have.

I am developing a compliance report. For a particular day I am developing the compliance report.

My Query to generate that goes like this.

Insert Into Temp_ParamTable SELECT dt.usr_name, SUBSTRING(ds.arrival, 1, 10) AS ADate, COUNT(SUBSTRING(ds.arrival, 1, 10)) AS arrival FROM dbo.disp_tech dt LEFT OUTER JOIN dbo.disp_time ds ON dt.usr_name = ds.technician WHERE (dt.role = 'dispenser technician' OR dt.role = 'Hybrid') AND (SUBSTRING(ds.arrival, 1, 10) = ''07/30/2007' ) GROUP BY dt.usr_name, SUBSTRING(ds.arrival, 1, 10)

Anderson , David 07/30/2007 1
Bangerd , Michael 07/30/2007 1
Berry , Jerry 07/30/2007 1
Breakall , Jeff 07/30/2007 1
Conklin , Terry 07/30/2007 2
DeLeon , Carlos 07/30/2007 3
Dorn , Craig 07/30/2007 1

I have many technicians but the above query returns only people who are compliant on that day. But my client wants to see all the technicians and if the technician has not worked on that day they want a zero across their name.

So what I did was I joined the above table with the table that has all the technician name.

Insert into Temp_Compliance SELECT TOP 100 PERCENT dt.usr_name, isnull( t.arrival, 0) AS arrival , isnull(t.ADate, 0) AS ADate FROM dbo.disp_tech dt LEFT OUTER JOIN dbo.Temp_ParamTable t ON dt.usr_name = t.usr_name WHERE (dt.role = 'Dispenser Technician') OR (dt.role = 'Hybrid') ORDER BY dt.usr_name

Adams , William 0 0
Alqueseva , JC 0 0
Anderson , David 1 07/30/2007
Asseng , Steve 0 0
Bangerd , Michael 1 07/30/2007
Barnaskas , Joe 0 0
Barry , Timothy 0 0
Berry , Jerry 1 07/30/2007
Bowen , Nels 0 0
Breakall , Jeff 1 07/30/2007
Brockel , Robert 0 0
Campbell , Bryan 0 0
Castro , Carlos 0 0
Childs , Michael 0 0
Clawson , Donald 0 0
Conklin , Terry 2 07/30/2007
Contractor , Contractor 0 0
Cooke , Jackie 0 0
DeLeon , Carlos 3 07/30/2007
Dorn , Craig 1 07/30/2007

So now I got all the technician names and for the techs who have not worked on 7/30/2007 I got a zero across

Now I develop my report using a matrix.

I drag and drop the usr_name in the row. Then I drag and drop the ADate in the column and for the data I create an expression like this =IIF(Fields!Count_Arrival.Value is nothing,"0",Fields!Count_Arrival.Value)

Now my report looks like this with a 0 column . How can I eliminate that. Is there any other way i ll have to follow?

007/30/2007 Adams , William0 0 Alqueseva , JC0 0 Anderson , David01 Asseng , Steve0 0 Bangerd , Michael01 Barnaskas , Joe0 0 Barry , Timothy0 0 Berry , Jerry01 Bowen , Nels0 0 Breakall , Jeff01 Brockel , Robert0 0 Campbell , Bryan0 0 Castro , Carlos0 0 Childs , Michael0 0 Clawson , Donald0 0 Conklin , Terry02 Contractor , Contractor0 0 Cooke , Jackie0 0 DeLeon , Carlos03 Dorn , Craig01