Report Parameters and Nulls - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Report Parameters and Nulls

Author

Topic

NSalinas
Starting Member

2 Posts

Posted - 2005-04-22 : 14:58:13

I am trying to create a drop down list of counties, so that a report can be generated either for the county selected or if the parameter is left NULL, to create a statewide report. I've gone into the Report Parameters Menu and created a stored procedure to give me this list of counties. However when I try to run the report without selecting a county, it will not generate the report. It is not allowing me to select NULL as a value to generate my statewide report, even though I have "Allow Null Value" checked.

Can you help me with allowing a NULL value to be included in my drop down list?!
Thank you,
Nora

jhermiz

3564 Posts

Posted - 2005-04-22 : 15:03:22

That is correct you need to allow for nulls..but you have to change your stored procedure to allow for an 'All' values:


CREATE PROCEDURE rsp_customer_sites @ClientID integer,
					    @CustomerID bigint
AS
SET NOCOUNT ON
SELECT     dbo.ClientCustomerSites.SiteID, dbo.Site.Site
FROM         dbo.ClientCustomerSites INNER JOIN
                      dbo.Site ON dbo.ClientCustomerSites.SiteID = dbo.Site.SiteID
WHERE dbo.ClientCustomerSites.ClientID=@ClientID AND dbo.ClientCustomerSites.CustomerID=@CustomerID
UNION SELECT Null, 'All'
ORDER BY dbo.Site.Site
SET NOCOUNT OFF
GO

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-04-22 : 15:07:27

Thats for the combo box BTW, your resultant stored procedure which the report is based on should have something like this:

...
WHERE (CountryID=@CountryID OR @CountryID IS NULL)

Boolean logic with an or, if the parameter has a value take the left hand side, else take the right hand side.
Dont forget to set @CountryID=NULL as a default parameter.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

NSalinas
Starting Member

2 Posts

Posted - 2005-04-29 : 17:42:04

Thank you Jon, for your help in figuring out my drop down boxes! I got that part of my report working! Yeyy!!!! Thank you!

But now I'm getting the following error:
Procedure expects parameter @BeginDate, which was not supplied, even though the begin and end dates were entered prior to trying to preview my report.

I noticed you had a lengthy discussion with tduggan (from postings in 11/2004)... about a similar problem you were having. But even after reading all of your postings, I am still having problems. :(
I am very new at this and would appreciate any advice you could give me.

This is basically what I have:

Under my DATA tab:
1) Stored Procedure - uses BEGINDATE and ENDDATE parameters.

2) Data Source dsValidRegions - gives me list of valid Regions in
Texas. Code in dsValidRegions =
SELECT DISTINCT Region
FROM TBL_County c
WHERE (Region BETWEEN '01' AND '11')
UNION SELECT ' All'
ORDER BY Region

3) Data Source dsValidCounties - gives me list of valid Counties in
Texas. Code in dsValidCounties =
SELECT DISTINCT CountyName
FROM TBL_County
WHERE (@Region = Region)
UNION SELECT ' All'
ORDER BY CountyName
With @Region listed under the Parameters Menu.

Under my LAYOUT TAB / REPORT PARAMETERS MENU:

1) Parameter Name: BeginDate
Prompt: Begin Date:
Data Type: String
Non-queried:
Default Value: None

2) Parameter Name: EndDate
Prompt: End Date:
Data Type: String
Non-queried:
Default Value: None

3) Parameter Name: Region
Prompt: Region:
Data Type: String, Allow Null Value Checked
From Query: Dataset = dsValidRegions
Value Field = Region
Label Field = Region
Default Value: From Query: Dataset = dsValidRegions
Value Field = Region

4) Parameter Name: CountyName
Prompt: County Name:
Data Type: String, Allow Null Value Checked
From Query: Dataset = dsValidCountyNames
Value Field = CountyName
Label Field = CountyName
Default Value: From Query: Dataset = dsValidCountyNames
Value Field = CountyName

Any help would be greatly appreciated! Thank you.
Nora :)

jhermiz

3564 Posts

Posted - 2005-04-29 : 19:50:55

Glad to see you got it working. Your other problem is simple, you need to do two things:

1) In your stored procedure you have a parameter for start and end date, make sure you set these parameters with NULL defaults in case the user does not provide one:

In addition...

@StartDate datetime=NULL,
@EndDate datetime=NULL

2) Finally in your reporting services go to your report parameters.
Make sure you have allowed for null values for these parameters.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources