SQL SSRS: creating multiple parameters and allowing search on single or multiple entries

Posted on Updated on

I built an SSRS report for one of my colleagues and it took me a little bit to figure how to properly setup the Parameters.  I am hoping this would help somebody out there that may run into same issue.

Here is the scenario:

I created 4 parameters named ‘Asset Barcode, Asset Serial, Purchase Order and Former Tag’.  And if all these 4 parameters are set to blank, then all data should be displayed.  And if any of the parameters are blank, then it will not be included in the query.

For the past couple of days, I have been testing and searching online on how to get this working, but it seems that a lot of people are suggesting to create a stored procedure which is very cumbersome.  Without the correct script, the blank parameters are still being included in the query and thereby not giving an accurate result.

Here is how my script looks like that worked:

NOTE: DO NOT copy and paste the code below, the commas and quotes are not formatted correctly in WordPress.

SELECT
Assets.CreatedBy
,Assets.ModifiedBy
,Assets.[General/Asset Name]
,Assets.[General/Site_id]
,Assets.[General/Loc_id]
,Assets.[Assets/Classification]
,Assets.[Assets/Usage]
,Assets.[Assets/Condition]
,Assets.[Assets/Inventory Date]
,Assets.[Assets/Data Source]
,Assets.[Assets/Serial Number]
,Assets.[Assets/Former Tag No]
,Assets.[Assets/Equipment/Model]
,Assets.DateCreated
,Assets.DateModified
,Assets.[General/Purchase Order]
,Assets.[General/Barcode]
,Assets.[Employee Details/Employee Notes]
,Assets.[Assets/Mfg]
,Assets.[Assets/Category]
FROM
Assets

WHERE

(@Asset_Barcode = or Assets.[General/Barcode] LIKE ‘%’ + @Asset_Barcode + ‘%’) and
(@Asset_Serial = or Assets.[Assets/Serial Number] LIKE ‘%’ + @Asset_Serial + ‘%’) and
(@Purchase_Order = or Assets.[General/Purchase Order] LIKE ‘%’ + @Purchase_Order + ‘%’) and
(@Former_Tag = or Assets.[Assets/Former Tag No] LIKE ‘%’ + @Former_Tag + ‘%’)

Advertisements

One thought on “SQL SSRS: creating multiple parameters and allowing search on single or multiple entries

    dineshramitc said:
    February 14, 2015 at 9:37 pm

    Reblogged this on Dinesh Ram Kali..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s