TSQL LIKE in Reporting Service

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

TSQL LIKE in Reporting Service

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2007-09-17 : 21:53:25

Hi there

I've created Report Parameter called AssetCode. In quiry for database, I've created like this:

SELECT a.id1, a.deliverydate, a.retirementdate, b.organisationname, c.costcentre,a.id4,mm.hierarchydescription 
FROM Asset as a (NOLOCK)
           LEFT JOIN makemodel as mm (NOLOCK) ON a.makemodelid = mm.makemodelid 
           LEFT JOIN OrganisationTree as b (NOLOCK) ON a.physicalLocationOrgID = b.orgid 
           LEFT JOIN AssetChargeSplit as c (NOLOCK) ON a.assetid = c.assetid 
WHERE a.status = '2' 
             AND a.id1 LIKE @AssetCode
             AND (mm.assettype = 1 or mm.assettype = 6) 
ORDER BY a.id1 ASC, 
                  c.costcentre DESC

But when you run a report, if you put for instance "NA196" as keyword, it doesn't return the data for "NA1961", "NA19602", "NA1965", etc etc. But if you put in textfield such as "NA196%", it get the data. ?!?! So how do replace this behaviour?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 02:20:23

WHERE a.status = '2'
AND a.id1 LIKE @AssetCode + '%'
AND mm.assettype IN (1, 6)

E 12°55'05.25"
N 56°04'39.16"