Avatar of Sanjeet
Sanjeet
Flag for United States of America asked on

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 2005SSRS

Avatar of undefined
Last Comment
ValentinoV

8/22/2022 - Mon
Jim Horn

>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

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

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sanjeet

ASKER
Could you let me know how to do it as a stored proc or in the SQL?
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sanjeet

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.