none
Wildcards in report parameters.

    Question

  • How do I accomplish wildcards in report parameters?  With my requirement, I cannot simply concatenate '%' + @in_value + '%' because each @in_value might have a different set of wildcards.

    Example:

    SQL STATEMENT:

    SELECT  item
    FROM  items_table
    WHERE  item LIKE @in_value

    PARAMETER:

    @in_value
    
    Specify Values
    
    Display Value      Actual Value
    Bikes              B1-____
    Cars               CAR%
    Toasters           T_-kitchen

    As you can see, depending on which item is chosen, the wildcard is different.  Some are % and some are placements of _.

    Of course, due to parameterization, these will not be parsed for their wildcards.  I believe they are only handled as literals.

    How can I allow the report to pass in wildcards?

    Friday, April 05, 2013 2:59 PM

Answers

  • I more/less resolved my issue.  I appreciate your assistance.

    What it really boils down to is a combination of:

    sp_executesql

    original data column being CHAR(100)

    parameter being unicode N'@input'

    ... and how it evaluates wildcards and equality in this situation.

    Some caveats there.

    Thanks for your help.

    • Marked as answer by Mini Button Monday, April 08, 2013 7:56 PM
    Monday, April 08, 2013 7:56 PM

All replies

  • There is few options. Would like to highlight two options for you.

    One is to listout the options like "B1%", "Car%", "T_-%"

    then have them as list - so user can select.

    Othe option is to dynamically build your query inside SP based on the value passed to get the result .


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Friday, April 05, 2013 4:27 PM
  • I have the options in a list, but SSRS runs the SQL using RPC sp_executesql and passes those choices (B1%, Car%, etc.) as parameters - therefore due to parameterization, the underscores and percent signs are parsed as literals.

    Using SQL Server Management studio:  SELECT.... WHERE item LIKE Car%   will return call items where Car is at the start.

    Using SSRS (which uses sp_executesql and binds parameters) the same SELECT statement will only return "Car%" literally.  The % sign is not a wildcard here, it's a literal of the percent sign.

    Do you think a stored procedure is even an option?  Would be terrible to have to make procedures just to run a report.  I haven't tested this, yet.  Just curious if you had any additional thoughts.
    Monday, April 08, 2013 2:23 PM
  • Hi -

    Yes, a stored procedure is an option (usually, a better one). 

    Can you provide some more info?  Why is the % being handled as a literal?  Sounds like mabye that you are using dynamic sql?


    - will

    Monday, April 08, 2013 4:33 PM
  • I more/less resolved my issue.  I appreciate your assistance.

    What it really boils down to is a combination of:

    sp_executesql

    original data column being CHAR(100)

    parameter being unicode N'@input'

    ... and how it evaluates wildcards and equality in this situation.

    Some caveats there.

    Thanks for your help.

    • Marked as answer by Mini Button Monday, April 08, 2013 7:56 PM
    Monday, April 08, 2013 7:56 PM