none
using like operator with parameter in ssrs RRS feed

  • Question

  • hey every one. i was working on one report which i had to connect to oracle server using all the tcp/ips and service name etc i have two parameters one is comapany and other is customer. i am having almost 8 data sets. so what i have done is that i have placed one dataset in my main report and created 7 sub reports. i did this because each table region is dedicated to one data set and if we mention fields from other data sets they would try to give us first( value) all the time so i had to go for subreports. now i have a problem i want to give user the option of typing in any part of the customer parameter and should be able to generate the report for all possible values for that parameter. say like if the customer parameter is birla then if the user type bi and leave the remaining blank they should get the report running for parameter birla and also if they have any other customers by the name have bi say cibiling  then that should also be considered as the input and it should generate the report for that customer also. ok i think i am making this akward. so i need to use a customer parameter using like % customer %  donot get into the way how it should be written. but what i am having problem with is that when i run the main report dataset which has like parameter then it gives me list of values but when i preview them i am not able to get any values.  i donot know why this is happening but i have values for the parameters that should be passed in to the remaining datasets(subreports) and they also should be generating the list of values but its not happening. please help out with this. i would love to give the example in here but i donot have the code with me. sorry guys. 

     

    Friday, April 2, 2010 12:35 AM

Answers

  • select * from mytable where name like '%' + @myparam + '%'

    or in case of oracle i think use this

    select * from mytable where name like '%' + :myparam + '%'

    i'm not sure this is what you are looking for


    mark it as answer if it answered your question :)
    • Proposed as answer by Kalman Toth Friday, April 2, 2010 6:12 AM
    • Marked as answer by SSRSnewbee Monday, April 12, 2010 2:52 AM
    Friday, April 2, 2010 4:56 AM
  • sure, your subreport query should have the same syntax as main report query. i'm sure that is the reason. good luck and let me know, how did it go?


    mark it as answer if it answered your question :)
    Friday, April 2, 2010 10:36 PM

All replies

  • select * from mytable where name like '%' + @myparam + '%'

    or in case of oracle i think use this

    select * from mytable where name like '%' + :myparam + '%'

    i'm not sure this is what you are looking for


    mark it as answer if it answered your question :)
    • Proposed as answer by Kalman Toth Friday, April 2, 2010 6:12 AM
    • Marked as answer by SSRSnewbee Monday, April 12, 2010 2:52 AM
    Friday, April 2, 2010 4:56 AM
  • nope i have already mentioned i am using  (:myparam, '%') it is giving me results in the data pane of the report but when i preview the report and try to do the same its not giving me any result. that is why i want to know how we can make it work.
    Friday, April 2, 2010 2:37 PM
  • what is your actual query? Would you mind puttng parameter on the report and see what is the parameter value and then run your query using that value.
    mark it as answer if it answered your question :)
    Friday, April 2, 2010 4:24 PM
  • i am running the query against oracle server. my question is that my main query contains "where customer.code like (:customer,'%') " 

    i am able to get the result set. now in my sub report queries do i have to mention the parameter in the same fashion "where customer.code like (:customer,'%') "  or can i use where customer.code=:customer. i am able to run my main report query succesfully in the data pane where we write the query for the report. but when i try to preview it is not working. do you think this is the problem.

     

    i cannot put the query here since its so long and does contain so many subqueries in the same query. so i would give you an example. 

     

    select customer name, details, address from customer_table where customer.code like (:customer,'%')  

     and i am asking you if in my subreport should i be mentioning the where clause in the same fashion or do i have to mention in another fashion like where customer.code = :customer. i think this is the reason. i have to try this on monday.

    Friday, April 2, 2010 5:51 PM
  • sure, your subreport query should have the same syntax as main report query. i'm sure that is the reason. good luck and let me know, how did it go?


    mark it as answer if it answered your question :)
    Friday, April 2, 2010 10:36 PM
  • I'm not sure if this is going to help you, but from what I got out of reading your posted problem you need to use the LIKE operator in an expression inside SSRS, not SQL.  If that's the case, then the wildcard character would be an asterisk (*) and not a percent (%).  

    So, the expression would be something like this:

    =Parameters!Data_Field.Value LIKE "ci*"

    If this doesn't help you with this problem, it may help you in the future.

    Tuesday, April 13, 2010 6:46 PM
  • ok . Now i have made every thing  working fine. my query works fine and also my report works fine. the case now is with performance.

    Since i am using subqueries the report is taking me 4 mins to execute and our lead says that it should be with in 6 secs previously they had Telerik reporting with .net in that the report worked in 6 secs to get data of 500 customers. but for me since i am using 7 sub queries for each customer i am executing 8 queries. so for 500 customers i am executing the 7 queries plus one main query total 8 queries which makes it up to 4000 queries per execution. so its taking me almost 4 to 5 mins for the report to execute. so i dont know what to do. even if i want to merge the query there are no possibilities of making it into a single one because i am using say same query for shipping and receiving with only difference that one is having an input flag in the where clause and one is having output flag in the where clause but the fiels are same. like this i am having trouble with different queries since they come from different tables. so if i do this i may get multiple rows for each query. say if one query gets me 2 rows and other query gets me only 1 row then when i try to make the two queries into one query i would get empty rows. but still i am trying to consider to make them into one query but its a big pain since there are 7 queries and we need to now figure out all the dependencies between the tables since each query is using different conditions for join and we need all those conditions in the merged query. 

    So here is what i want to know. how can we reduce the execution time of the report.? 

    How can we figure out the relationship between the different tables so that we can join them. i need to make atleast 15 joins which again will make my performance hurt. So i am basically in a kind of mess. why does reporting services doesn't have the features we have in telerik or others.  right now i am pissed off 

    Friday, April 16, 2010 1:53 AM
  • hey SSRSnewbee,

     

    i'm currently coding this too against oracle db.  I was trying your code "where customer.code like (:customer,'%')" it wouldn't let me. 

     

    SELECT *

    FROM tablename

    WHERE tablename.code like (:customer,'%')

     

    i also tried "select * from mytable where name like '%' + :myparam + '%'" i did however get a 0ra-01722 invalid number error..my parameter is a string..how did you resolve this?  any reply would be appreciated.  thank you.

    Monday, April 26, 2010 8:48 PM
  • you should try using || instead of +  . oracle doesnot allow + as concatenation symbol. use ||  instead. 

     

    let me know if you have any other issues

    Monday, April 26, 2010 11:01 PM
  • thank you it worked. =)
    Monday, April 26, 2010 11:34 PM
  • Thanks, It worked for DB2 too

    Thursday, October 5, 2017 1:38 PM
  • For Oracle, it's select * from mytable where name like '%' || :myparam || '%'
    Wednesday, January 22, 2020 1:44 PM