SSRS 2008 / text box parameter and default 'ALL' fields

There's no native way to do this in SSRS. A workaround is to add "all" to your list of parameter values, preferably at the top. You can then modify your report query to return all records if "all" is selected, or only the selected records if "all" is not selected. There are a few ways to do this, but it depends on your underlying query. Possible options are a Case statement, updating a variable at the beginning which contains the correct value for the where clause, a stored procedure with 2 separate segments for each scenario, etc.
If you can provide your query we can assist you with this. Do you use a text query or a stored procedure? By the way, is your parameter a multi-value or single-value parameter?

UNION ALL

when there is no need to remove duplicates from a list then use UNION ALL

no points please

JIM HORN

thanks.
there is no productName involved. Just a simple textbox to put salesorderID which is int or display word All.

Koen,
thanks. here is the query from adventureworks

select top 100 salesOrderID
, OrderQty
, ProductID
,LineTotal
, UnitPrice
from Sales.SalesOrderDetail

and parameter is @salesOrderID

above example is different from initial sample in Q. i.e. simply being search all salesorderids or typed in salesorderid

Then just use this as the stored procedure code for the parameter value procedure:

Select		a.SalesOrderId
		,	a.Label
From	(
		select		-1 as 'SalesOrderID'
				,	'<All>' as 'Label'
				,	1 as 'SortOrder'
				
		union all

		select		SalesOrderId
				,	Cast(SalesOrderId as nvarchar)
				,	2 as 'SortOrder'
		From	yourTable) as a
order by	a.SortOrder
		,	a.Label

Select allOpen in new window

Replace @product_id with @SalesOrderId in the second procedure of Jim, and you should be good to go.

Koen

I tried the solution but it displays dropdown box and no drop downbox should appear for SalesOrderID, it is a textbox field.
 users do not want to select a salesorderID. they just type it in if they want to (there are many other parameters to select from the in the report too), otherwise by default it returns all salesordersids in the query result. (we can ignore display of word ALL in texbox but functionality should be as I explained.)

Oh, ok. Now I get it.
Change your report definition as follows. Assuming you're using a stored procedure, after the part where the parameters are declared, before you start the main select statement, add something like this:

	
Declare @OrderId	int,

If	LEN(ISNULL(@SalesOrderID,'')) > 0 and @SalesOrderId <> 'All'
Begin

Set @OrderId = CAST(@SalesOrderID as int)

End

Else
Begin

Set @OrderId = 0

End

Select allOpen in new window

This assumes your report has a parameter @SalesOrderId which is a varchar or nvarchar field. It checks if this is filled, and if so, casts the value to an int and fills the @orderId variable with it. If not, it sets the @orderId variable to 0.
Next, in your Where clause, filter the order ID like Jim already showed with his example:

Where (SalesOrderID = @OrderId OR @OrderId = 0)

Select allOpen in new window

This either selects the single sales order, or does nothing when @OrderId is set to 0.

There is still a risk that a user enters an alpha character in the text box, and if that happens an error will be thrown because that can't be cast to an Int. If you can leave out the "All" word from the text box, you can create the parameter as an Integer parameter instead. That way, I think the default value will be 0, and you can just use the same logic for the Where clause directly using the @SalesOrderId parameter which is filled by the report. If the default value is not 0, it's probably Null and then you can rewrite the where clause like this:

Where (SalesOrderId = @SalesOrderId or @SalesOrderId IS NULL)

Select allOpen in new window

Sorry, I don't have an example handy to confirm this.

>there is no productName involved
Fine, then delete all references to productName

>Just a simple textbox to put salesorderID which is int or display word All.
The original question said product id, so that's what I used.

If you have any other comments please let me know, as I posted a full answer to the question.

Jim Horn, Thank you . I appreciate it. I will ask for more details if I go ahead with this solution.

Koen,

Here is what I have done so far:

CreAT PROCEDURE salestest
(
@SalesOrderID as nvarchar(10)
)
AS
BEGIN
Declare @OrderId      int
If      LEN(ISNULL(@SalesOrderId,'')) > 0 and @SalesOrderId <> 'All'
Begin
Set @OrderId = @SalesOrderID
End

Else
Begin
Set @OrderId = 0
End

select top 100 salesOrderID
, OrderQty
, ProductID
, LineTotal
, UnitPrice
from Sales.SalesOrderDetail
where (SalesOrderID = @OrderId OR @OrderId = 0)

END

I ticked the @SalesOrderID property of allow Null.

Report is calling above stored proc.

When I run the report, by default it is set to accept Null so it displays all records.
If I untick the null and insert a salesOrderID, it does display that record only correctly.
If I insert 'ALL' however, it does not work.

I declared @salesOrderID in the stored proc as nvarchar. The error msg is: A value selected for the report parameter @salesorderId is not valid for its type.
any workaround?

I thought alternatively if I can create a checkbox that acts like NULL checkbox or change the word NULL beside checkbox to ALL, it will make my day! :)

Thanks in advance

What's the datatype of your SalesOrderID in your sales table?

its int, not null.
just a note that users should be able to type in ALL in the textbox...

Can you try this? I've modified the setting of @OrderId, it should be cast to an int. Surprised it worked in your earlier tests to be honest. Also double check that the parameter in the report itself is set to text and not int.

Create PROCEDURE salestest

@SalesOrderID nvarchar(10)

AS
BEGIN
Declare @OrderId      int
If      LEN(ISNULL(@SalesOrderId,'')) > 0 and LTRIM(RTRIM(@SalesOrderId)) <> 'All'
Begin
Set @OrderId = CAST(@SalesOrderID as int)
End

Else
Begin
Set @OrderId = 0
End

select top 100 salesOrderID
, OrderQty
, ProductID
, LineTotal
, UnitPrice
from Sales.SalesOrderDetail
where (SalesOrderID = @OrderId OR @OrderId = 0)

END

Select allOpen in new window

Not sure if this is stating the obvious, but you should also type All in your parameter text box without the single quotes.

Hi Koen,

Thanks for info. here is what actually happening:

My actual datatype at work is in fact 'not int' but nvarchar.
I could use your instruction to get the report working for ALL and for individual salesorderID(when it  is of type int only)

lets think SalesOrderID has nvarchar data type in the table.

2 entry is possible by users:

-  if users try to input any valid nvarchar SalesOrderID like 111-234-111 then I get error message: 'conversion failed when converting nvarchar 111-234KL to datatype of type int.'

- if user try to enter invalid salesorderId like  ksdhlkdsgh which could happen by users, I like to ignore them and show ALL records.

how can I get around this problem?

Many Thanks in Advance

Excellent, Thank you.