Month: February 2015
SQL SSRS: creating multiple parameters and allowing search on single or multiple entries
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 + ‘%’)
Merging two tables in a non-conventional way
I found this an easy way to merge two tables together without using UNION, UNION ALL. Here is a summary that one of our admins are having that led me into creating this script:
We are using a hardware asset manager software, and the vendor did all the data migration from the old version to the new version which also included a migration of data to a new database with different table schemas. The migration went fine, but our admin noticed some values that were not migrated correctly. So on the new database, there are some missing data.
Instead of using the UNION or UNION ALL to merge the old and new database. I decided to create my own script. Which also made more sense for us, since we only wanted to update the data that are on the new database and not add junk to it. The old DB has 10,000 records and the new DB has only 7,000. The old DB included the retired assets that we no longer want.
So here is how I did it in a script: (I will leave the table and database name as is)
— You may have to fix the single and double quotes after pasting it to SMC as wordpress formatted the characters differently
use vam_master
DECLARE @T TABLE (SQL varchar(1000))
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET AssetSerialNo=”’ + [assets/serial number] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET PurchaseOrderNo=”’ + [general/purchase order] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET Model=”’ + [assets/equipment/model] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET mfg=”’ + [assets/mfg] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
select * from @T
Running this script will output results like ‘UPDATE dbo.assets SET AssetSerialNo=’blah’ WHERE Barcode=’blah’. So you can then run this against the new DB and it will update the table with missing values for what you have selected and just ignore those entries that do not exist on the new DB.
I hope this helps, if you are on the same situation as me.
WIKIPinoy 