asked on 4/1/2013

Need to add a new parameter to existing SSRS 2005 report

I have an existing report the allows 3 parameters.

Start Date, End Date and Customer. Start Date and End Date are required.

I need to add the opion of searching by the Invoice Number field only.

Invoice Number field. is a varchar (20).

When I added the invoice number paramter and set the start date to allow null value or blank values data does not get returned.

I know there is a data for a specific invoice #, but when I placed this as the paramter no data gets returned. When I place this value plus a start and end date I get a record bank.

I declared my invoice parameter as a string.

Microsoft SQL Server 2005 SSRS

Last Comment

ValentinoV

4/8/2013

Jim Horn

4/1/2013

>I need to add the opion of searching by the Invoice Number field only.
Ok, but that means all of the parameters will have to be defined as optional, and you'll need a stored procedure as the main data set that has logic to handle the 'either invoice number, or other three parameters' value logic.

Sanjeet

4/1/2013

ASKER

Couldn't I accomplish this with just regular SQL and not create a stored proc?

Attached existing SQL I am using for my report.
Add-Parameter.txt

Jim Horn

4/1/2013

You could, but i'd recommend against it, as SQL inside an .rdl file (afaik) does not allow for non-parameter local variables, or #temp tables, that come in real handy when dealing with logic such as 'either invoice number, or other three parameters'.

That, and you can't compile SQL inside an .rdl, so no execution plan to help with performance tuning.

Sanjeet

4/1/2013

ASKER

Could you let me know how to do it as a stored proc or in the SQL?

ASKER CERTIFIED SOLUTION

Jim Horn

4/1/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Sanjeet

4/1/2013

ASKER

Ok how would I do it without the stored proc, may need to do it this way since it is quicker.

ValentinoV

4/8/2013

Why are you so much against using a stored procedure? Just like Jim, I'd also advice to put your query inside a stored proc. It's more interesting for maintenance, performance and troubleshooting reasons.

In case you're new to SPs in combination with SSRS, have a look at the following articles:
Reporting On Data From Stored Procedures (part 1)
Reporting On Data From Stored Procedures (part 2)

@Jim: you can actually declare variables in the dataset query window. But of course I wouldn't really do that, if the query is not just a simple SELECT statement then I always try to use a stored proc.