Need some SSRS advice

[Solarion]

I have a form with a drop down called Customer. The report itself uses a stored proc to retrieve the customer's details with Customer as the parameter.

What happens when they don't select a customer and leave it as "All"? How will the stored proc handle the "All" value as a parameter?

I'm absolutely terrible with stored procs by the way. Please help!

 

[Pho3nix]

I have a form with a drop down called Customer. The report itself uses a stored proc to retrieve the customer's details with Customer as the parameter.

What happens when they don't select a customer and leave it as "All"? How will the stored proc handle the "All" value as a parameter?

I'm absolutely terrible with stored procs by the way. Please help!

Funny enough, this is pretty simple but for the life of me can't remember how to do it. Will look at some legacy code and let you know
Small change on the stored proc though

 

[Solarion]

Funny enough, this is pretty simple but for the life of me can't remember how to do it. Will look at some legacy code and let you know
Small change on the stored proc though

Thanks a million

 

[Raithlin]

Perhaps this will help?

The "Select ALL" parameter option [SSRS]

 

[adam1234]

Perhaps this will help?

The "Select ALL" parameter option [SSRS]

Depends, is the param multiple select? if so then in the StoredProc you need to do a split into temp table and the where clause "where col in (select col1 from temp table)".

if the param in ssrs is not multiple select then in the StoredProc you need to do this before the query select "If Param = 'ALL' set Param = NULL. Then in the select

"where col = isnull(@param, col)"

 

[diabolus]

Simplest way:

Set your SSRS report parameter to accept null (basically allowing the user not to select anything from the dropdown). And make this change in your SP:

CREATE PROCEDURE rpt_test
(
@CUSTOMERID int = null <--- this allows the SP to run with a "default" value if it's not supplied, you can set this to anything, i.e. @CUSTOMERID int = 0
)
AS
...

SELECT *
FROM Customer
Where CustomerID = CASE WHEN @CUSTOMERID is null THEN CustomerID ELSE @CUSTOMERID END <---- this checks what the parameter is, by setting "CustomerID = CustomerID" implies returning everything

That will return everything if the user did not select something.

 

[Solarion]

Perhaps this will help?

The "Select ALL" parameter option [SSRS]

Thanks man. I'll go over it and post the stored proc a bit later to show you guys what I'm trying to accomplish.

 

[Solarion]

Simplest way:

Set your SSRS report parameter to accept null (basically allowing the user not to select anything from the dropdown). An make this change in your SP:

That will return everything if the user did not select something.

Cool thanks Diabolus I'll try that.

 

[Solarion]

I've got to run, leaving the office. I'll continue with this a bit later at home.

 

[Pho3nix]

Cool thanks Diabolus I'll try that.

This was going to be my suggestion. Sorry I took too long.

 

[Solarion]

I haven't forgotten this thread. I'm back on the project today and will post the solution I'm using a little later.