Report Paramaters and WildCards

I've created a report parameter which i've had to concatenate and modify because of the way in which the information is entered into the database - consequently I need my query parameter (in my database query) to only match the beginning of of each report parameter. For instance - my parameters (in my drop down list) state "Pentium 4 CPU" whereas within the database this entry is stored as "Pentium 4 CPU 2.23GHz" or "Pentium 4 CPU 1.85GHz". I would like the user to be able to select "Pentium 4 CPU" from the drop down list and get both entries... I have tried to play around with the wildcard character (%), single quotations (''), and the LIKE statement however, nothing seems to work ... Examples include things such as, WHERE sys.table LIKE '%@parameter%'... however, nothing seems to work. If anyone can help it would be greatly appreciated.

Thanks

Answer this question

Report Paramaters and WildCards

CJDuva
Hi,

the solustion is actually in help books of Reporting Services.

For example you have query: select name from emplyees where name like 'ro%'

and You want to have parameter instead ro%. The modified query into Visual Studio type into by having parameter @NAME is:

="select name from emplyees where name like '" & Parameters!NAME.Value & "%'"

so you are not writing query directly but puzzling query text by syntax usage of visual studio, which can include everything.
Derek W.
Hello ykagoma.
I've made several reports that need a situation similar tou the one you're handling. What I do is to create a more awful query, so it is composed by IF statements. I mean, IF the parameter has certain value I use a query with certain WHERE clauses. IF the parameter has another value then I use a query with another WHERE clauses and so on.
For example:

IF @param = -5
BEGIN
   SELECT *
   FROM tbl_employee
END
ELSE
BEGIN
   SELECT *
   FROM tbl_employee
   WHERE id_boss = @param
END

I hope it helps.
KLomax
A good practice is to test the query in the appropiate enviroment. Then when you successfully retrieve the data put into report.
Try to print parameters in the report.
Be carefull with case sensitive of your database, mine is case sensitive.
Report Paramaters and WildCards

Answer this question