Make Filter = False

I have a stored proc to return the main report data. I have another dataset1 to return the distinct values for my parameter. I filter the main data based on the parameter selected by user. I wanted to add 'ALL' option to the parameter drop down. I have added an UNION to the dataset1 to include this option. I now want to change my filter expresion from '=Fields!FRole.Value = Parameters!PRole.Value' to include ALL option and basically ignore the filter. Is it possibleMulti-valued parameters are not supported RS 2000. Here's a related post
with a solution that might work for you:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ONnNRtsBEHA.3064%40tk2msftngp13.phx.gbl&rnum=2&prev=/groups%3Fq%3D%2522in%2Bclause%2522%2Bgroup:microsoft.public.sqlserver.reportingsvcs%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DONnNRtsBEHA.3064%2540tk2msftngp13.phx.gbl%26rnum%3D2
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"vrodkar" <vrodkar@.discussions.microsoft.com> wrote in message
news:A6CBA50D-2C8F-4A97-B925-CE5DE0378347@.microsoft.com...
> I have a stored proc to return the main report data. I have another
dataset1 to return the distinct values for my parameter. I filter the main
data based on the parameter selected by user. I wanted to add 'ALL' option
to the parameter drop down. I have added an UNION to the dataset1 to include
this option. I now want to change my filter expresion from
'=Fields!FRole.Value = Parameters!PRole.Value' to include ALL
option and basically ignore the filter. Is it possible|||I'm curious on this as well as I am also trying to
implement this on a report. Has anyone founnd a
workaround?
>--Original Message--
>I have a stored proc to return the main report data. I
have another dataset1 to return the distinct values for
my parameter. I filter the main data based on the
parameter selected by user. I wanted to add 'ALL' option
to the parameter drop down. I have added an UNION to the
dataset1 to include this option. I now want to change my
filter expresion from '=Fields!FRole.Value =Parameters!PRole.Value' to include ALL option and
basically ignore the filter. Is it possible
>.
>|||Yes I use an "(All)" option in most of my reports.
It's easier if you're using queries instead of stored procedures.
In your parameter list have an item labelled "(All)" give it a Value of "%".
In your main data query have criteria or where clause using the 'LIKE' operator against the parameter, so in SQL;
SELECT * FROM tblData WHERE Country LIKE @.Country
% is the SQL wildcard character, but must be used with the like operator.
Regards
Chris McGuigan
"BiggieSize" wrote:
> I'm curious on this as well as I am also trying to
> implement this on a report. Has anyone founnd a
> workaround?
> >--Original Message--
> >I have a stored proc to return the main report data. I
> have another dataset1 to return the distinct values for
> my parameter. I filter the main data based on the
> parameter selected by user. I wanted to add 'ALL' option
> to the parameter drop down. I have added an UNION to the
> dataset1 to include this option. I now want to change my
> filter expresion from '=Fields!FRole.Value => Parameters!PRole.Value' to include ALL option and
> basically ignore the filter. Is it possible
> >.
> >
>

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.

Make a SELECT DISTINCT in Reporting Services

Hi,
I have a stored procedure, that can't be modified.
It return data like these (the real one is more, and more complex of
course)
A ...
A ...
A ...
B ...
B ...
B ...
C ...
C ...
I want to make a table with only theses entries :
A
B
C
How can i do it in reporting services ? I have tested
=Distinct(Fields!Qu_HA_LIB.Value, "GESCCNS")
But Distrinct seems to be not recognized by RS 2000
Thank you in advance
MassanuCan somebody help me, please ...