locked
Can you use a mutlivalue parameter to filter a group RRS feed

  • Question

  • I want to filter a group based on a multivalue parameter. However, selecting the IN operator disables the expression editor. Can anyone show some code on how to parse the multivalue parameter so that the filter works?
    • Moved by Mike Dos Zhang Wednesday, July 13, 2011 12:06 PM ssrs question (From:Windows Forms Data Controls and Databinding)
    Sunday, July 10, 2011 1:34 AM

Answers

  • Hi Toppin,

    Based on your requirement, there are two methods to solve this issue: Add a filter and SQL expression.
    Method 1: Add a filter on a dataset.
    1. Create a multiple-parameter. The parameter is named Category which is includes Accessory, Bike, Clothing, Component.
    2. Right-click on the main dataset and select “Dataset Properties”.
    3. Click “Filters” page, and click “Add”. Then set the filter like this:
        Expression: [Category]
        Operator: IN
        Value: [@Category]
    4. Click OK to close the dialog.

    Method 2: Using dynamic query in the main dataset to achieve this.
    1. Create a multiple-parameter. The multi-parameter is named Category.
    2. Right-click on the main dataset and select “Dataset Properties”.
    3. In the “Query:” section, click “fx” button.
    4. Set the expression like this:
        ="SELECT Category, SubCategory, Year FROM MyTable WHERE Category IN (‘” + Join(Parameters!Category.Value, “’,'") + "')"
    5. Click OK to close the dialog.

    If you have any more questions, please feel free to ask.

    Thanks,
    Bin Long

    Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    • Marked as answer by Elvis Long Monday, July 18, 2011 10:14 AM
    Friday, July 15, 2011 1:52 AM

All replies

  • It seems that this is a SSRS aspect question, so I moved this thread here for more expert help.

    Best wishes,


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 13, 2011 12:05 PM
  • Hi Toppin,

    Based on your requirement, there are two methods to solve this issue: Add a filter and SQL expression.
    Method 1: Add a filter on a dataset.
    1. Create a multiple-parameter. The parameter is named Category which is includes Accessory, Bike, Clothing, Component.
    2. Right-click on the main dataset and select “Dataset Properties”.
    3. Click “Filters” page, and click “Add”. Then set the filter like this:
        Expression: [Category]
        Operator: IN
        Value: [@Category]
    4. Click OK to close the dialog.

    Method 2: Using dynamic query in the main dataset to achieve this.
    1. Create a multiple-parameter. The multi-parameter is named Category.
    2. Right-click on the main dataset and select “Dataset Properties”.
    3. In the “Query:” section, click “fx” button.
    4. Set the expression like this:
        ="SELECT Category, SubCategory, Year FROM MyTable WHERE Category IN (‘” + Join(Parameters!Category.Value, “’,'") + "')"
    5. Click OK to close the dialog.

    If you have any more questions, please feel free to ask.

    Thanks,
    Bin Long

    Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    • Marked as answer by Elvis Long Monday, July 18, 2011 10:14 AM
    Friday, July 15, 2011 1:52 AM