Report Builder - Search Parameters

• Erin.

  SSCertifiable

  Points: 5237

  More actions

  August 15, 2011 at 8:53 am

  #390195

  We need a way for users to search a particular SQL table. The request is for the ability to search using 15 different fields. Would setting up this type of report in Report Builder be a good idea? The report could have between 1 to 15 different search possibilities. I'm not sure how I would setup the parameters in the WHERE clause so that one field could be blank and another could have a particial search. For example, foldername LIKE '%' +@foldername + '%'. What if the user left that field blank. 'Allow blank value' in the parameter properties in Report Builder is not enough.

• Daniel Bowlin

  SSC-Dedicated

  Points: 34566

  More actions

  August 15, 2011 at 10:09 am

  #1368704

  In a stored procedure, you should just set a default value of '' on your input parameters and that will probably do. Hope this helps.

• Erin.

  SSCertifiable

  Points: 5237

  More actions

  August 17, 2011 at 9:27 am

  #1369685

  This worked.

  isnull(foldername ,'') LIKE '%' + isnull(@foldername ,isnull(foldername ,'')) + '%'

  Also set the parameter properties to allow blank value.