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.
,Assets.[Assets/Former Tag No]
,Assets.[Employee Details/Employee Notes]
(@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 + ‘%’)