none
String / Quote on query - multi value parameter

    Question

  • Hi Guys,

    I am using Microsoft SQL Report Builder.

    My dilemma comes on the following query

    ..... Where partno IN (@Par)

     

    @Par is a multi value parameter which is the result of =("'"&Join(Parameters!Get_Equiv_PN_Only.Value(), "','") &"'")

    So the value of this, for example will be ('a', 'b', 'c'),

    The query gives me 0 results , but when i replace it with the same information

    ..... Where partno IN  ('a', 'b', 'c')  it does in fact work

    So, what i believe is happening is for some reason the parameter @Par when is passed to the query it adds the "@par" double quote

    so what is actually happening is ..... Where partno IN  ("'a', 'b', 'c'") this is why it does not work

    I have tested this and i am sure this is the problem, so how the hell i overcome this issue? i do not think i can control how the parameter is passed to the query. does sql has a function to remove "" ?

     

    I have also tried:

    ....Where partno IN (Replace(@Par, """, ""))

     

    But i has not worked, it gives me an error

     

    Any help will be greatly appreciated

    Cheers!

     

    Thursday, November 24, 2011 9:25 PM

All replies

  • Hi,

    Please try dynamic query, goto query window and try this:

    ="Select * from table Where partno IN ( " + @Par + ")"

    Hope this helps.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Friday, November 25, 2011 8:49 AM
  • Do i put this directly to the query ?

    My query gives me an error when i try to put the  quotes

     

    Thanks

     

    Friday, November 25, 2011 1:31 PM
  • Hi,

    Sorry for the above answer. Please try these steps:

    1. I assume you are not using any SP and writing TSQL directly into report.
    2. Your query is correct - Select * from table Where partno IN ( @Par ). Use this only... Now @Par becomes your query parameter.
    3. Goto Parameter tab in Dataset property, you will see same parameter in left side. Select report parameter in right side... Report parameter, i think you already added and marked as Allow multiple value.
    4. You are not required to use JOIN for this... simple map query parameter and report parameter...

    Hope this helps.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Friday, November 25, 2011 1:55 PM
  • Hi Asim,

     

    Yes, this is a multi value parameter - actually when i do an out put of this parameter in the report it gives me all the values.

     

    I also tried it without the Join - that is Parameters!Get_Equiv_PN_Only.Value and gives me the correct result as well when i output it alone in the report

     

    For some reason, when i pass the parameter inot the query, the query is adding double quotes at the beginning and end of the parameter. That is , the query is reading this as follows :

    Select * from table Where partno IN ( " @Par " ) , hence i do not get any results. the only time when i get results is only when the parameter contains only 1 value and using Join(Parameters!Get_Equiv_PN_Only.Value(), because this will not add quote to the beginning or end of the result, hence with one value it does work

     

    Hope this is not confusing you

    Thanks again

     

    Friday, November 25, 2011 2:15 PM
  • Hi,

    I have simulated this situation:

    1. Created one dataset, query = Select * from test where name in (@par)
    2. Added one multi value parameter, mapped that parameter to query parameter. I see expression as  =Parameters!par.Value in mapping tab of dataset (by clicking fx button)
    3. Selected two values from parameter and executed report.
    4. In profiler, i got this call - select * from test where name in (N'Asim', N'Vivek')

    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Friday, November 25, 2011 2:33 PM
  • Thanks Asim.

    It seems you are now understanding my problem, unfourtunalty, there is more to it.

     

    The parameter values are not selected by the user, instead, they are passed from another dataset that queeries another data source.

    So the results of this query, which can be : nothing, one or more, are passed to this parameter. Once the parameter has the values, is then inserted into the query of another data set querying another data source, and there realys my problem

    Thanks a million, once again

    Friday, November 25, 2011 2:43 PM
  • Hi,

    I am sure, I understood it correctly...

    I have tried below dynamic query which is working

    Put this query in query expression.. on the click of OK it may give some working... just proceed with it

    ="Select * from test where name in ('" + REPLACE(JOIN(Parameters!par.Value, ","), ",","','") + "')"

    Change as per your field name, table name and parameter name.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Friday, November 25, 2011 3:05 PM
  • Hi Asim,

    I copy paste your statement === > ('" + REPLACE(JOIN(Parameters!Get_Equiv_PN_Only.Value, ","), ",","','") + "')"

    No when i do that and i rund the query, it gives me a syntax error, it seems it does not like the quotes on the query

    so i left it at Select * from test where name in (?)

    Now for the parameter - when i get the values i put the following to set up the value of the parameter

    ==== > =REPLACE(JOIN(Parameters!Get_Equiv_PN_Only.Value, ","), ",","','")

     

    Which it works as long as the value of the parameter is only one value - the moment it contains 2 or more it does not give me any results at all

     

    Thanks again!

     

    Friday, November 25, 2011 3:53 PM
  • Hi Fab,

    If you pass a string value to the query, SSRS will make certain that the SQL Server will interprete this as a string value. This is by design. You do not need to - cannot - use any JOIN expression here.

    As Asim already said, if you use parameters with multiple values, you just need to do that:

    ... Where partno IN (@Par)

    In the query parameters you should see that @Par takes its value from [@Get_Equiv_PN_Only]

    Just in case, the proper way to fill a parameter from another query is:

    1. go to the properties for @Get_Equiv_PN_Only
    2. go to Default Values
    3. select Get values from a query
    4. choose the relevant values for dataset and value field
    Friday, November 25, 2011 5:21 PM
  • Hello Laurent,

     

    I have done that.

     

    I have set my parameter as you specified.

    I know i am getting the right values, i am not using the Join.

     

    There are 2 cases to this :

    1) Using Join in parameter

     -- Whne u use the Join in the parameter and pass this to the query i have the following scenarios

             a) values is only one, if this is the case, the parameter value will be ===> Value and the query will process as follows

                 ... Where partno IN (value), like you said the query adds the double quotes so this will be treated as ... Where partno IN ("value")

                in this case, THIS WORKS!

             b) when the parameter has 2 or more values, in this case, with the join the value of the parameter will be  ===> value1'.'value2','value3

                 When this is passed to the query, it will process as follows ... Where partno IN (value1'.'value2','value3), as you mentioned before  the

                query adds double quotes so it will treat it as ... Where partno IN ("value1'.'value2','value3")   THIS GIVES ME 0 RESULTS ON QUERY

    2) Not using Join in parameter

         When this happeens the value of the parrameter for one value is 'value1' ===?... Where partno IN ("'value1'") == THIS GIVES ME 0 RESULTS ON QUERY

       wHEN THE PARAMETER CONTAINS MORE THEAN 1 VALUE == 'value1'.'value2','value3'  then is passed to the query as ... Where partno IN  ("'value1'.'value2','value3'") ==THIS GIVES ME 0 RESULTS ON QUERY

     

    Conclusion == it seems that no matter what i passed - it treats everything as 1 single string, and this is my big issue! - i think i am going to get drunl tonight since i cannot find a solution - if you can find on a work around for this, it will be great, i do not know what else to do

    Thank you!

    Friday, November 25, 2011 5:50 PM
  • Hi Fab,

    your analysis is totally correct. SSRS does put double quotes, as you said.

    If you apply the Join function or any other SSRS expression to your report parameter, you will build a string and pass it as a value for your query parameter. SSRS will make certain that this string value will interpreted as a string by the DB engine.

    This is by design, in order to avoid SQL injections. There is no way around this.

    That is why you cannot use the join function.

    You have to fill a multi-valued report parameter from a query and then pass it as it is to the query parameter.

    The question is why you absolutely want to manipulate this parameter. I see two possibilities:

    1. You cannot let a piece of software stand in your way, and will not be at rest until you found a solution based the Join function. This probably happened to all of us once. If this is the case, then you can quietly go home, knowing the reason why it does not is that it cannot work :-)
    2. You have a real business need for manipulating the parameter from the first query before you pass them to the second query. If this is the case, then please provide more information about what you are trying to achieve.

    Cheers,

    Laurent.

     

    Friday, November 25, 2011 7:23 PM
  • Hi Laurent,

     

    So here is my situation, what i am trying to do is counting the occurrences of a part number in 2 databases.

    One thing that is worth mention, is that a part number can or cannot contain equivalent part numbers, in the case where it does contain equivalent part numbers i have to count those as well.

     

     

    So, the counting for the first database is not problem

    The counting for the same part number (and equivalents if this is the case) for the second database is the problem 


    The report allows the user to enter a part number - base on this part number - my first dataset calculates the occurrences of this instance (together with any equivalent part numbers) by year and displays it

    The second database, contains a column in one table, called P.N., the big issue is that in this database, there is not a table that can link the part number to its equivalent like i have one the first database

    This is the main reason why i have a parameter.

    The objective of the parameter was to search for all equivalent pars number (if any) from the first database and then use these value to query the table for the second database. With this, i will be able to count how many instance of the Part number searched and its equiavlent are present in the second database

     

    Hope is not confusing

    Thanks again

    Friday, November 25, 2011 8:30 PM
  • Hi,

    assuming you have such a query somewhere:

    SELECT

     EquivalentPartNumber

    FROM DB1.SomeSchema.SomeEquivalenceTable

    WHERE OriginalPartNumber = @PN

    Then you can set it to as the source for the default value of the report parameter @Get_Equiv_PN_Only.

    Then you create the query parameter @Get_Equiv_PN_Only in your query from DB2 linked to the first one. It should like this:

    In your second query, you will have something like this:

    SELECT ...

    FROM DB2.SomeOtherSchema.TheOtherTable

    WHERE partno IN (@Get_Equiv_PN_Only)

    And it should be fine.

    Friday, November 25, 2011 9:37 PM
  • Hello,

     

    This is exactly what i have

    I have one parameter that gets its value from a dataset that gets all the equivalent part numbers(from first database)

    This parameter is then use in the query for the dataset of the second datbase.

    This is exactly whati have done but it does not resolve my problem due to mutli values - this can work no problem if i am only passing one value, but as i stated above, sometimes i will be passing 2 or more values and this is not recognized by the ....Where part in (@var) query

    Thanks

     

     

    Friday, November 25, 2011 9:50 PM
  • Did you say that the list of valid values comes from a query against a table?

    If so, I suggest NOT using a parameter but leverage the TSQL IN expression's ability to use values generated by a query that returns a table (a TVF). Something to the effect of :

    SELECT x, y where z IN (MyTVF)

     


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Friday, November 25, 2011 11:55 PM
  • My dilemma comes on the following query

    ..... Where partno IN (@Par)

    @Par is a multi value parameter which is the result of =("'"&Join(Parameters!Get_Equiv_PN_Only.Value(), "','") &"'")

    From your first post, my understanding was you had an expression to translate the value of [@Get_Equiv_PN_Only].

    My suggestion was to get rid of it. If I do as I described above. It works for me.


    Alternatively Asim suggested you pass the whole query as an expression. In that case, you would need the Join expression. (Near the query text box, you have an expression button.)

    @William: the data are spread on two servers. I doubt distributed queries would be a better choice (if they are an option, anyway.)


    • Edited by Laurent Couartou Monday, November 28, 2011 8:36 AM Picture re-uploaded.
    Saturday, November 26, 2011 1:22 AM
  • Hi William,

     

    i have to take the results from one query (one data source) and take this result and use it in another query (second data sorce)

    Both data source are different database, this is why i use a parameter. I i am not quite familiar with your method, can you explain in more details please?

     

    Laurent, i believe that i have to use a parameter either way, otherwise how can i pass this values ot the other query

    Please remember that the values passed could be one Ex Mother, or could be several, Ex Father, Mother, Son

    Please read my earlier posts about this, when i pass 1 value only, it does work, when i pass several, it does not, since sql takes the parameter as "Father, Mother, Son" instead of "Father", "Mother", "Son". This is my problem

    Thanks again

     

    Saturday, November 26, 2011 1:58 PM
  • Laurent, i believe that i have to use a parameter either way, otherwise how can i pass this values ot the other query

    Please remember that the values passed could be one Ex Mother, or could be several, Ex Father, Mother, Son

    Please read my earlier posts about this, when i pass 1 value only, it does work, when i pass several, it does not, since sql takes the parameter as "Father, Mother, Son" instead of "Father", "Mother", "Son". This is my problem

    Thanks again

     

    Saturday, November 26, 2011 1:58 PM
  • Hi again guys,

     

    I might have found a workaround for my problem, but now i have encountered another problem 

    Let me know if you can help me with this, thanks a million once again

    I have one dataset that its filtered with cetain data

    In another dataset, i would like to show how many times the year "2009" repeats on the column year from the first dataset

    I believe the lookup cannot apply into filtered dataset, and i do not know if the count function can do this ?

    any workaround to get this working ? (each dataset has a different data source)

    Below is an example of what i have for dataset 1 and what i want dataset 2 to look like - how would i be able to get the qty 2 on dataset # 2

    Dataset 1 (Filtered)

    Year       P.N.

    2009      A

    2009      B

    2008       C

    2007      D

    2008     E

     

    Data set # 2

    Year     Qty

    2009    2

    Thank you in advance

    Sunday, November 27, 2011 1:36 PM