NULL default for query-based parameter

I'm using DevStudio 2005 with Reporting Services and SQL Server 2005. I have
a report with two parameters - @.StoreID and @.ParkName.
@.StoreID is a "non-queried" parameter with "Allow null value" checked.
@.ParkName is a "From query" parameter with "Allow null value" checked.
"Null" is checked under Default value for both parameters.
The stored procedure behind the report is setup to interpret "NULL" as "all
values" for both parameters, so you could select all stores and all parks, or
one park or one store, etc.
When I run the report, the @.StoreID parameter fields comes up with a NULL
checkbox next to it; that checkbox is checked by default and the parameter
field itself is blank and grayed out. If I uncheck NULL, I can enter a
specific store ID. This is exactly what I expect.
However, the @.ParkName parameter field comes up with "<Select a value>" as a
default and no NULL checkbox. If I leave the "<Select a value>" and click
View Report, I get an error about specifying a value for Park Name. If I
select a specific value from the drop-down-list, it works correctly.
Since both parameters are configured the same way with respect to NULL
values, I expected them to behave the same way (NULL checkbox checked by
default), but they don't. Is this an issue specifically with query-based
parameters? If so, how do I get the query-based parameter @.ParkName to
default to and accept a NULL input the same way @.StoreID does?Hi Mike,
The most common workaround I've found for the problem you are
encountering is to UNION an 'All' entry into the @.Parkname parameter such as
follows.
SELECT DISTINCT 1 as Sort, ParkName FROM Parks
UNION ALL
SELECT 0, 'All'
Order By Sort
You then also have to configure your dataset WHERE to have an entry that
includes
WHERE Parkaname in (@.ParkName) OR 'All' in (@.ParkName)
Michael C
"Mike Miller" wrote:
> I'm using DevStudio 2005 with Reporting Services and SQL Server 2005. I have
> a report with two parameters - @.StoreID and @.ParkName.
> @.StoreID is a "non-queried" parameter with "Allow null value" checked.
> @.ParkName is a "From query" parameter with "Allow null value" checked.
> "Null" is checked under Default value for both parameters.
> The stored procedure behind the report is setup to interpret "NULL" as "all
> values" for both parameters, so you could select all stores and all parks, or
> one park or one store, etc.
> When I run the report, the @.StoreID parameter fields comes up with a NULL
> checkbox next to it; that checkbox is checked by default and the parameter
> field itself is blank and grayed out. If I uncheck NULL, I can enter a
> specific store ID. This is exactly what I expect.
> However, the @.ParkName parameter field comes up with "<Select a value>" as a
> default and no NULL checkbox. If I leave the "<Select a value>" and click
> View Report, I get an error about specifying a value for Park Name. If I
> select a specific value from the drop-down-list, it works correctly.
> Since both parameters are configured the same way with respect to NULL
> values, I expected them to behave the same way (NULL checkbox checked by
> default), but they don't. Is this an issue specifically with query-based
> parameters? If so, how do I get the query-based parameter @.ParkName to
> default to and accept a NULL input the same way @.StoreID does?|||Michael,
Thanks for the tip. I had considered that previously, but was hoping there
was a simpler solution. It seems strange that Reporting Services treats a
non-queried parameter differently than a queried parameter with respect to
NULL values.
Mike
"Michael C" wrote:
> Hi Mike,
> The most common workaround I've found for the problem you are
> encountering is to UNION an 'All' entry into the @.Parkname parameter such as
> follows.
> SELECT DISTINCT 1 as Sort, ParkName FROM Parks
> UNION ALL
> SELECT 0, 'All'
> Order By Sort
> You then also have to configure your dataset WHERE to have an entry that
> includes
> WHERE Parkaname in (@.ParkName) OR 'All' in (@.ParkName)
> Michael C
> "Mike Miller" wrote:
> > I'm using DevStudio 2005 with Reporting Services and SQL Server 2005. I have
> > a report with two parameters - @.StoreID and @.ParkName.
> >
> > @.StoreID is a "non-queried" parameter with "Allow null value" checked.
> > @.ParkName is a "From query" parameter with "Allow null value" checked.
> > "Null" is checked under Default value for both parameters.
> > The stored procedure behind the report is setup to interpret "NULL" as "all
> > values" for both parameters, so you could select all stores and all parks, or
> > one park or one store, etc.
> >
> > When I run the report, the @.StoreID parameter fields comes up with a NULL
> > checkbox next to it; that checkbox is checked by default and the parameter
> > field itself is blank and grayed out. If I uncheck NULL, I can enter a
> > specific store ID. This is exactly what I expect.
> >
> > However, the @.ParkName parameter field comes up with "<Select a value>" as a
> > default and no NULL checkbox. If I leave the "<Select a value>" and click
> > View Report, I get an error about specifying a value for Park Name. If I
> > select a specific value from the drop-down-list, it works correctly.
> >
> > Since both parameters are configured the same way with respect to NULL
> > values, I expected them to behave the same way (NULL checkbox checked by
> > default), but they don't. Is this an issue specifically with query-based
> > parameters? If so, how do I get the query-based parameter @.ParkName to
> > default to and accept a NULL input the same way @.StoreID does?