Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSRS - Blank parameter

Posted on 2009-04-21
3
Medium Priority
?
1,216 Views
Last Modified: 2012-05-06
Hi there,
I have a parameter in a report @postcode. What I would like is for the report user to enter the first x characters of the postcode and the report use that entery to filter results. I have that working no problem with

j.postcode like @PostCode + '%' in the stored procedure

What I would like is the option for the report user to leave this parameter option blank, and the report return ALL values. Any help is appreciated,

Rog
0
Comment
Question by:Rogero
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
tpi007 earned 2000 total points
ID: 24192429
You need to allow nulls for Postcode value on RS form (Report > Report Parameters in BIDS) and also modify SP to set default for parameter and set logic in SP to show all results if parameter is NULL. In the dataset type ensure the CommandType is stored procedure.  
CREATE PROCEDURE sel_Postcodes
(@Postcode varchar(8) = NULL)
AS 
BEGIN
IF (@Postcode IS NULL) 
	BEGIN
		SELECT * FROM Postcodes
	END
ELSE
	BEGIN
		SELECT * FROM Postcodes where Postcode LIKE @Postcode + '%'
	END	
end

Open in new window

Postcodes.JPG
0
 
LVL 8

Expert Comment

by:tpi007
ID: 24192442
'Allow Blank Value' option will work with SP above  
0
 

Author Closing Comment

by:Rogero
ID: 31572633
That's great, thanks!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question