Solved

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

Posted on 2014-11-11
19
564 Views
Last Modified: 2014-11-20
Hi, I have a text box parameter that accepts productID.

I want it to display word 'ALL' by default and include all productIDs in the query search but if I put a single product ID, then it only search for that productID.

How can be done both in query and in default property setting??

Thanks in advance
0
Comment
Question by:shmz
  • 10
  • 6
  • 2
  • +1
19 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40436542
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?
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40436550
>I want it to display word 'ALL' by default and include all productIDs in the query search but if I put a single product ID, then it only search for that productID.
Easy.  Write a Stored Procedure that goes something like this:
CREATE PROC rsp_cbo_products AS 

-- Assumes that -1 can be used as a 'dummy' id, as there is no id value of -1. 
SELECT a.product_id, a.product_name
FROM (
   SELECT -1 as product_id, '<ALL>' as product_name, 1 as sort_order
   UNION
   SELECT product_id, product_name, 2 as sort_order
   FROM your_products_table)
ORDER BY a.sort_order, a.product_name
GO

Open in new window

This gives a set of id's and names, with <ALL> up top and sorted by name.

Then create a Data Set in SSRS, and attach it to the above SP.

Then modify the SP that is the main data set in your SSRS report to add this:

CREATE PROC rsp_your_report (@product_id int) AS

/*
Handy code comments go here
*/

SELECT blah, blah, blah
FROM your_products_table
WHERE (product_id = @product_id OR @product_ID = -1) 
GO

Open in new window


Then in SSRS, Parameters, find the @product_id parameter, double-click to open, choose Available Values, Get values from a query, and select the rsp_cbo_products.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40436554
UNION ALL

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

SELECT a.product_id, a.product_name
FROM (
   SELECT -1 as product_id, '<ALL>' as product_name, 1 as sort_order
  UNION ALL
   SELECT product_id, product_name, 2 as sort_order
   FROM your_products_table)
ORDER BY a.sort_order, a.product_name

no points please
0
 

Author Comment

by:shmz
ID: 40436813
JIM HORN

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

Author Comment

by:shmz
ID: 40436822
Koen,
thanks. here is the query from adventureworks

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

and parameter is @salesOrderID
0
 

Author Comment

by:shmz
ID: 40436824
above example is different from initial sample in Q. i.e. simply being search all salesorderids or typed in salesorderid
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40436833
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

Open in new window


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

Author Comment

by:shmz
ID: 40436876
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.)
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40436915
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

Open 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)

Open 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)

Open in new window


Sorry, I don't have an example handy to confirm this.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40437170
>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.
0
 

Author Comment

by:shmz
ID: 40439133
Jim Horn, Thank you . I appreciate it. I will ask for more details if I go ahead with this solution.
0
 

Author Comment

by:shmz
ID: 40439134
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
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40439177
What's the datatype of your SalesOrderID in your sales table?
0
 

Author Comment

by:shmz
ID: 40439508
its int, not null.
just a note that users should be able to type in ALL in the textbox...
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40439511
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

Open 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.
0
 

Author Comment

by:shmz
ID: 40441611
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
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 400 total points
ID: 40441677
Ok, that changes things. In that case a conversion would not be required.
The problem I see here is distinguishing a valid order number from a non-valid one. If your SalesOrderId is nvarchar, and your order ID's actually contain non-numerical characters, then how do you know when an order number is actually of the correct format?
The only way I can think of right now is to check first if the entered ID actually exists in the table. If it does, set @orderID to the given value. If not, set it to '' and return all records. Here's the syntax:

CreATe PROCEDURE salestest
(
@SalesOrderID as nvarchar(10)
)
AS

Declare @OrderId nvarchar(50)

If Exists (select 1
		from Sales.SalesOrderDetail
		where SalesOrderId = LTRIM(RTRIM(@SalesOrderID)))
Begin
Set @OrderId = @SalesOrderID
End

Else
Begin
Set @OrderId = ''
End


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

Open in new window


The only drawback of this method is that your SalesOrderDetail table is queried twice, which of course doesn't improve performance. But if you have no other way to verify that the orderID entered is valid, then I can't think of another way.
0
 

Assisted Solution

by:shmz
shmz earned 0 total points
ID: 40444927
Thank you for your help. It worked fine.
0
 

Author Closing Comment

by:shmz
ID: 40454651
Excellent, Thank you.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now