February 4, 2010 at 9:10 am
I am going to try to explain this the best way I can. I want parameters on my report such as a date range, last name, department, and location. The end user is telling me that theywould like the ability to do a search on just each 1 of the fields that I mentioned. Or on a combination while leaving the others blank. My question is how would I structure my query as well as my parameters to satisfy those requirements. I know that the query should use the OR clauses but I believe I can make some settings on my report parameter's so that my report will have this functionality. Thanks for any help. I really appreciate it. After having some time to think about this I thought that maybe an IFF function would be useful. And use the AND/OR Clause based upon wheteher or not they enter a value for a parameter. For example if they enter a value then I could use AND and if they don't I could use the OR Clause. Does this make sense?
February 4, 2010 at 12:50 pm
you should be able to do the following in the WHERE Clause
WHERE (FromDate >= ISNULL(@BegDate, FromDate)),
AND (ToDate <=ISNULL(@EndDate, ToDate)),
AND (name = ISNULL(@paramname, name))
AND (department = ISNULL(@paramdept, department))
AND (location = ISNULL(@paramloc, location))
Then go into your report properties and set each parameter to allow NULL
The only problem here is with the From and To date, these would both have to be left Null when the user is running the report.
February 5, 2010 at 7:57 am
Are you calling a stored procedure or doing ad hoc SQL in the report?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 9, 2010 at 5:43 am
Hi,
Try this
Step 1: Stored Procedure
CREATE PROCEDURE SampleProcedure
(
@daterange NVARCHAR(100) = NULL,
@lastname NVARCHAR(100)= NULL,
@department NVARCHAR(100)= NULL,
@location NVARCHAR(100) = NULL
)
AS
BEGIN
SELECT * FROM TableName
WHERE (lastname = @lastname OR @lastname IS NULL)
AND (department = @department OR @department IS NULL)
AND (location = @location OR @location IS NULL)
AND (daterange = @daterange OR @daterange IS NULL)
END
STEP 2: Report Parameters Properties
Select "Allow Null Values" Check box for Parameters
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply