Making a row not show

I have one column that is an expression that either returns a value or
returns zero. I would like if it is zero to not show at all. How is this
done?
Thanks,
RyanGo to properties for that column, click on Visibility tab. Click on
Expression radio button and enter this formula:
=iif (Fields!<expression>.Value = 0, True, False)
you may have to map the expression to a data set row before this will work.
I have made it work with parameter and field values.
"Ryan Mcbee" wrote:
> I have one column that is an expression that either returns a value or
> returns zero. I would like if it is zero to not show at all. How is this
> done?
> Thanks,
> Ryan|||Carl,
I tried this and it didn't work. I think because I am using a matrix
report. Would have to put this formula in every column since it is a matrix
report?
Thanks,
Ryan
"Carl Henthorn" wrote:
> Go to properties for that column, click on Visibility tab. Click on
> Expression radio button and enter this formula:
> =iif (Fields!<expression>.Value = 0, True, False)
> you may have to map the expression to a data set row before this will work.
> I have made it work with parameter and field values.
> "Ryan Mcbee" wrote:
> > I have one column that is an expression that either returns a value or
> > returns zero. I would like if it is zero to not show at all. How is this
> > done?
> >
> > Thanks,
> > Ryan

Making a matrix display zeroes and not blank cells

My matrix cell will only display blanks when the variable evaluates to a
zero. I need to zero to display.
I confirmed the problem is a display issue by forcing all the output to zero
as follows:
iif(1 = 1,0,Fields!MyField.Value)
In case it might be applicable, I am also using a formating string on the
cell as follows:
=iif(Fields!Category.Value <> "NoShow%","###","P")
Thanks in advance for any pointers.
--
John ShahanI solved it by modifying the formatting as follows:
=iif(Fields!Category.Value <> "NoShow%","##0","P")
John Shahan
"jp" wrote:
> My matrix cell will only display blanks when the variable evaluates to a
> zero. I need to zero to display.
> I confirmed the problem is a display issue by forcing all the output to zero
> as follows:
> iif(1 = 1,0,Fields!MyField.Value)
> In case it might be applicable, I am also using a formating string on the
> cell as follows:
> =iif(Fields!Category.Value <> "NoShow%","###","P")
> Thanks in advance for any pointers.
> --
> John Shahansql

making a date field read as MMDDYYYY

Hello:
I am trying to get a field that reads as 6/29/1983 as 06291983, including a
leading zero where any month fields are not double digit months. It needs t
o
read as MMDDYYYY.
Is there a way to do this? I tried the following statement but for some
reason it returned only a bogus year and not even the month, day, or even th
e
correct year.
Please help!
select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
'+DATEPART(yyyy,BRTHDATE) from UPR00100Here's one method (assuming datatype is datetime):
SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 105), '-', '')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
a
> leading zero where any month fields are not double digit months. It needs
to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||select replace(convert(varchar(10), getdate(), 101), '/', '')
Your Query doesn't work because the Datepart function returns Integers.
Even if you Cast them as Varchar, leading zeros would be dropped.
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
> a
> leading zero where any month fields are not double digit months. It needs
> to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
> the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||DATEPART returns an int, so you need to convert it to char or varchar to
create the string.
"childothe1980s" wrote:

> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
a
> leading zero where any month fields are not double digit months. It needs
to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||Come from VBScript? I sympathize. In T-SQL, you can't just add integers to
strings and expect SQL Server to know what you are trying to do... T-SQL is
slightly more strongly typed than VBScript.
Give the following a try instead:
SELECT RIGHT(d,4) + LEFT(d, 4) FROM
(SELECT d = CONVERT(CHAR(8), GETDATE(), 112)) x;
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
> a
> leading zero where any month fields are not double digit months. It needs
> to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
> the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||Try:
select
replace (convert (varchar (20), getdate (), 101), '/', '')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
Hello:
I am trying to get a field that reads as 6/29/1983 as 06291983, including a
leading zero where any month fields are not double digit months. It needs
to
read as MMDDYYYY.
Is there a way to do this? I tried the following statement but for some
reason it returned only a bogus year and not even the month, day, or even
the
correct year.
Please help!
select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
'+DATEPART(yyyy,BRTHDATE) from UPR00100

make Count(*) return zero

hey guys

is there a way to make the count(*) return zero . because the default behavoir is that it will skip this value if there is no records
so a simple query like this

select userid,count(*) as count from users where userid in (select val from sometable)
group by userid

if userid 1 has no records . it wont be returned in the query
instead i want it to show zero
so
userid count
1 0
2 3
3 1
4 0

thanks in advance
hi

you can try this

select userid,count(*) as [count] from testDB where
userid in (select userid from testdb1)
group by userid
union
select userid,0 as [count] from testDB where
userid not in (select userid from testdb1)
group by userid

|||

How about this query..

Select

Val

,Count(UserId)

from

someTable A

Left Outer Join users B On A.val=B.userid

Group By

Val

|||you can also try this one..

Code Snippet

select *
into #users
from (
select 1 as userid union all
select 2 as userid union all
select 3 as userid
) users

select *
into #sometable
from (
select 1 as val
) sometable

select a.userid
, case when count(a.userid) = 1 and sum(b.sumcheck) = 0 then 0 else count(a.userid) end as count
from #users a left outer join
(
select userid
, isnull(val,0) as sumcheck
from #users left outer join
#sometable on #users.userid = #sometable.val
) b on a.userid = b.userid
group by
a.userid

drop table #users
drop table #sometable

|||

RamezR,

Those rows like [userid] = 1, are being excluded from the result set because of the filter in the "where" clause. The is a keyword in the "group by" clause (ALL), that let you see those rows excluded by the filter in the "where" clause.

select userid,count(*) as count

from users

where userid in (select val from sometable)

group by userid ALL

go

AMB

|||when i try to run the query i get Incorrect syntax near the keyword 'ALL'.

i was not aware of this "ALL" keyword but when i looked up the msdn i found that its kinda irrelvant in that case.

so far i found that the best result is achieved using the left outer join

Regards

|||deleted my previous post.. i think what hunchback meant was..

select userid,count(*) as count

from users

where userid in (select val from sometable)

group by all userid|||

rh4m1113,

Thanks for jumping in. That is exactly what I meant, but did not test it.

AMB

|||hunchback,

actually i was also confused and thought only SQL 2005 had the GROUP BY ALL clause .. good thing i tested it.. i have actually learned from this one.. cool post hunchback..

rhamille|||

rh4m1ll3,

Glad you learned something from the post. The "left join" idea is a good one, but you have to be sure that the rows in the right side table has no duplicated rows by the columns used in the join, or you select distinct values from that table previous the join, if not you will get wrong result.

AMB

|||hunchback,

true, so far on some production codes that i have used a similar approach none of them had a requirement of having duplicate records, i'd prole' be taking note of your approach just in case a similar requirement comes. it's much more cleaner and straight forward as well as maximizes the the group by clauses's potential

rhamille|||

Hi hunchback & rh4m1ll3,

Let me understand your solution, (i accept the feature GROUP BY ALL in sql server ).

But I am really confused to accept GROUP BY ALL for this requirement.

See the bellow example, are you sure GROUP BY ALL will work for this requirement?

Code Snippet

Create Table #sometable (

[val] int

);

Insert Into #sometable Values('2');

Insert Into #sometable Values('2');

Insert Into #sometable Values('2');

Insert Into #sometable Values('3');

Insert Into #sometable Values('3');

Insert Into #sometable Values('5');

Insert Into #sometable Values('5');

Create Table #users (

[UserId] int

);

Insert Into #users Values('1');

Insert Into #users Values('2');

Insert Into #users Values('3');

Insert Into #users Values('4');

Insert Into #users Values('5');

Insert Into #users Values('6');

Select

UserId

,Count(Val) Counts

from

#users B

Left Outer Join #sometable A On A.val=B.userid

Group By

UserId

/*

UserId Counts

-- --

1 0

2 3

3 2

4 0

5 2

6 0

*/

Select

UserId

,Count(*) Counts

from

#users B

Where

UserId in (Select val From #sometable)

Group By ALL

UserId

/*

UserId Counts

-- --

1 0

2 1

3 1

4 0

5 1

6 0

*/

|||mani,

good and interesting point .. i have just recently realized that you might have misplaced the Val and UserId on your previous(first) post.. good approach for such requirement

rhamille|||

Manivannan.D.Sekaran,

Look at the original post and execute the stament using the sample data in your post

Select

UserId

,Count(*) Counts

from

#users

where

UserId in (select [val] from #sometable)

Group By

UserId

Result:

UserId Counts
2 1
3 1
5 1

If we want to include also the rows not matching the criteria in the "where" clause, but this time using "left join", then you need to be sure that we join to unique values from #sometable:

Select

u.UserId

,Count(s.[val]) Counts

from

#users as u

left join

(select distinct [val] from #sometable) as s

on u.UserId = s.[val]

Group By

u.UserId

Result

UserId Counts
1 0
2 1
3 1
4 0
5 1
6 0

As you can see, the result from the statement using "left join" and the statement using "GROUP BY ALL", yield same result for the rows returned by the original statement.

AMB

|||Please do NOT use GROUP BY ALL. It is a non-standard extension and will be deprecated in the future. It is marked for deprecation in SQL Serve 2005. So if you use it in your code now you may have to change it in the next version of SQL Server or the version after that.