Post reply

Working with report models and query parameters

  • February 23, 2012 at 9:49 am

    #268932

    I'm trying to test run a query built off of a report model.

    The model's query uses a parameter with the value of In a List

    When this converts to a report parameter, it becomes a multi-select drop down parameter (good).

    But when I'm running it in the query designer, I can't seem figure out what to type in as a parameter value to specify multiple options. If I type in a single value it works fine, if I try to enter more than 1, it treats them all as the same string and returns nothing.

    I've tried surrounding parts of a string with single quotes, double quotes, separating the options with commas and semi-colons, and none of these have helped.

    Any assistance is greatly appreciated.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • February 23, 2012 at 11:31 am

    #1451050

    This is what I usually do:

    'value1,value2,value3,value4'

    Depending on where exactly you are running it from you may need double quotes instead of the singles I show above.

  • February 23, 2012 at 2:14 pm

    #1451105

    Having no luck with that.

    Tried

    'Bill Smith;John Doe'

    "Bill Smith;John Doe"

    "Bill Smith,John Doe"

    'Bill Smith,John Doe'

    Each returns nothing when I run the query, but if I just specify either Bill Smith or John Doe individually I get back results in the Run query pane. If instead of using a Parameter with in this list selected for the filter, I instead select Bill Smith and John Doe, I get back results.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • February 24, 2012 at 7:10 am

    #1451375

    In the parameter settings for the query, use an expression like this:

    =JOIN(Paramters!ParameterName.Value,",")

  • February 24, 2012 at 7:18 am

    #1451381

    Do you mean in the filter itself? Won't that change how it functions when I'm not testing it?

    Right now if I click on the filter and edit the formula for the parameterized part I have

    IN(Employee Name,Parameter:Employee Name)

    Should I change that to JOIN?


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • February 24, 2012 at 3:53 pm

    #1451636

    there is work around in ssrs when you want to select multiple values. If you declare a parameter i think there ia function to accept that parameter. i am not sure for report model. google it for selecting multiple values using function. i hope it may help you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply