Parameters

• ronaldkrex

  Right there with Babe

  Points: 738

  More actions

  February 4, 2010 at 9:10 am

  #216334

  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?

• guitarrista18

  Newbie

  Points: 5

  More actions

  February 4, 2010 at 12:50 pm

  #1113946

  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.

• Jack Corbett

  SSC Guru

  Points: 184388

  More actions

  February 5, 2010 at 7:57 am

  #1114453

  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

• Rajesh Jonnalagadda

  Ten Centuries

  Points: 1032

  More actions

  February 9, 2010 at 5:43 am

  #1116054

  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