Report parameters

I have a report that reads from a view and in the report parameters I am
using "like <param value>%". The problem is that some of the values in the
datbase are nulls and I need those returned as well as the non null values.
Any help on this would be greatly appreciated.
--
JerryJust change your SQL
select * from sometable where somefield like 'blah%' or somefield is null
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jerry" <Jerry@.jerry.com> wrote in message
news:AFF5F532-3E8A-4573-9B80-F329BD53B04A@.microsoft.com...
> I have a report that reads from a view and in the report parameters I am
> using "like <param value>%". The problem is that some of the values in
the
> datbase are nulls and I need those returned as well as the non null
values.
> Any help on this would be greatly appreciated.
> --
> Jerry|||I am using a view in Sql Server and adding a where clause in the view is no
problem and it works fine when you run the view. However I am using URL
Access in Sql Reporting Services and it is not working. IN the books online,
under "parameters, Reporting Services" is says the following:
If one of the values is null (that is, isnull), all other values specified
for that same parameter are ignored.
So if I enter &SomeField=%, it ignores the records with null values.
If use &SomeField=%&SomeField:isnull=true it returns nothing.
Is there a way to "OR" these together using URL Access?
Thanks for your help.
Jerry
"Bruce L-C [MVP]" wrote:
> Just change your SQL
> select * from sometable where somefield like 'blah%' or somefield is null
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jerry" <Jerry@.jerry.com> wrote in message
> news:AFF5F532-3E8A-4573-9B80-F329BD53B04A@.microsoft.com...
> > I have a report that reads from a view and in the report parameters I am
> > using "like <param value>%". The problem is that some of the values in
> the
> > datbase are nulls and I need those returned as well as the non null
> values.
> > Any help on this would be greatly appreciated.
> > --
> > Jerry
>
>|||You have two different things here. One is URL and passing the parameter. If
your parameter allows nulls then you should be able to do this. Next is
getting your report to work with a null value. My suggestion is to first get
this to work from Report Manager before you try with URL. You have to have
two things for this to work. First, the parameter needs to allow nulls.
Second, the query itself that needs to be modified to work with Null. What I
showed you below was how to modify the query to work with null values.
I'm not sure why you are trying to send two values with the URL. You should
only be sending one value. I am a little confused on what you are wanting. I
thought what you wanted was to provide a value and see all records that have
that value for the field AND show all records where the value for the field
is null (For instance, lets say I had a field call Color, I want to see all
values where the color is blue or the color is not specified (is null)).
Then the query would be:
select * from mytable where color = @.colorparam or color is null
Note that the field name and the parameter name can be the same but they do
not have to be. There is a mapping between query parameter and report
parameter, make sure you understand this point.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jerry" <Jerry@.jerry.com> wrote in message
news:B163FD9B-BFF9-46B7-A8F9-7A5863E43F88@.microsoft.com...
> I am using a view in Sql Server and adding a where clause in the view is
no
> problem and it works fine when you run the view. However I am using URL
> Access in Sql Reporting Services and it is not working. IN the books
online,
> under "parameters, Reporting Services" is says the following:
> If one of the values is null (that is, isnull), all other values specified
> for that same parameter are ignored.
> So if I enter &SomeField=%, it ignores the records with null values.
> If use &SomeField=%&SomeField:isnull=true it returns nothing.
> Is there a way to "OR" these together using URL Access?
> Thanks for your help.
> Jerry
>
> "Bruce L-C [MVP]" wrote:
> > Just change your SQL
> >
> > select * from sometable where somefield like 'blah%' or somefield is
null
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jerry" <Jerry@.jerry.com> wrote in message
> > news:AFF5F532-3E8A-4573-9B80-F329BD53B04A@.microsoft.com...
> > > I have a report that reads from a view and in the report parameters I
am
> > > using "like <param value>%". The problem is that some of the values
in
> > the
> > > datbase are nulls and I need those returned as well as the non null
> > values.
> > > Any help on this would be greatly appreciated.
> > > --
> > > Jerry
> >
> >
> >sql