SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Report Parameters and Nulls		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NSalinas
Starting Member

2 Posts
Posted - 04/22/2005 :  14:58:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3564 Posts
Posted - 04/22/2005 :  15:03:22  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com
Imperfection living for perfection -- http://jhermiz.blogspot.com/
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts
Posted - 04/22/2005 :  15:07:27  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com
Imperfection living for perfection -- http://jhermiz.blogspot.com/
Go to Top of Page

NSalinas
Starting Member

2 Posts
Posted - 04/29/2005 :  17:42:04  Show Profile  Reply with Quote
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 :)
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts
Posted - 04/29/2005 :  19:50:55  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com
Imperfection living for perfection -- http://jhermiz.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000