Matrix hide zeros

Hi,
I have built a matrix report that counts the number of orders in a day by
customer and displays all dates regardless of whether any orders were placed
then. I would like to hide the zeros for customers without orders, but I
cannot figure out how to filter those out or alternately make the zeros
display as white on a white background.
I want it to look like this:
1/1/07 1/2/07 1/3/07 1/4/07 1/5/07 Totals
Acme 1 1 4
7
Bernett 2 1 3 2
7
Chapmen 5 1 2
8
Totals 3 6 5 0 8
22
Any help is greatly appreciated!
KathyI can think of three options.
1. Alter the query to return NULL where the value is 0
2. Place a filter expression on the Dataset in Reporting Services. This is
accessed from the Data tab
3. Place an expression on the cell in the Matrix layout to test for 0 and
return nothing or a blank space
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:2C3FB54D-8014-4771-A15E-C22551196B0D@.microsoft.com...
> Hi,
> I have built a matrix report that counts the number of orders in a day by
> customer and displays all dates regardless of whether any orders were
> placed
> then. I would like to hide the zeros for customers without orders, but I
> cannot figure out how to filter those out or alternately make the zeros
> display as white on a white background.
> I want it to look like this:
> 1/1/07 1/2/07 1/3/07 1/4/07 1/5/07
> Totals
> Acme 1 1
> 4
> 7
> Bernett 2 1 3
> 2
> 7
> Chapmen 5 1 2
> 8
> Totals 3 6 5 0
> 8
> 22
> Any help is greatly appreciated!
> Kathy|||Did you try to don't select the lines at zero ?
SELECT * FROM [table] WHERE
[field]>0
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:2C3FB54D-8014-4771-A15E-C22551196B0D@.microsoft.com...
> Hi,
> I have built a matrix report that counts the number of orders in a day by
> customer and displays all dates regardless of whether any orders were
> placed
> then. I would like to hide the zeros for customers without orders, but I
> cannot figure out how to filter those out or alternately make the zeros
> display as white on a white background.
> I want it to look like this:
> 1/1/07 1/2/07 1/3/07 1/4/07 1/5/07
> Totals
> Acme 1 1
> 4
> 7
> Bernett 2 1 3
> 2
> 7
> Chapmen 5 1 2
> 8
> Totals 3 6 5 0
> 8
> 22
> Any help is greatly appreciated!
> Kathy

Matrix Help with Columns

sql server 2005

Hi all,

I have a finacial report that I need to show all periods 1-12 (columns) regardless if there is data or not. what i am getting is

account--1--5--6

a#1235--#-" "-#

a#2346-" "-#--#

what i want is

account--1--2--3--4--5--6--7--8--9--10--11--12

even if the accounts 1235 and 2346 only have data for a couple of periods i want all periods to show on the report.

is this possible? can someone help me please, tell me what to do or point me to an article?

Thanks in advanced,

Kerrie

Kerrie,

Is there a reason you're not using a table instead? With a table, you specify exactly what columns you want to show, and the number of rows is variable based on the number of accounts that are returned in your dataset.

-Jessica

|||

You could use a common table expression and an outer join to make sure that there are always periods 1-12

The query would look like this:

with period_cte (period) as
(select 1 as period
union
select 2 as period
union
select 3 as period
union
select 4 as period
union
select 5 as period
union
select 6 as period
union
select 7 as period
union
select 8 as period
union
select 9 as period
union
select 10 as period
union
select 11 as period
union
select 12 as period)

select p.period, <other fields> .... from period_cte p left outer join SomeTable t on p.period = t.period