none
SSRS using sharepoint list with multi select paramater RRS feed

  • Question

  • I have an SSRS report that uses Sharepoint 2007 list, one of the parameters in the report is a dropdown, for some reason when I run the report it generates an error saying "Failed to evaluate the FilterExpression of the DataSet". If I remove the "Allow multiple value" for the parameter the report runs without any issues. Is this is a limitation on SSRS and Sharepoint? The report rdl dataset is uses this tag <RSSSharepointList>. The multiselect parameter has it's own dataset as well. Some folks on the web are having the same issue. The filter expression and filter expression uses IIF there is no code behind. Is that possible to use code behind in the filter express and how?
    Wednesday, May 8, 2013 5:24 AM

Answers

  • Hello,

    According to your description, you would like to create an OR filter for result plus "All" values. Although, there is no IN operator in expression builder to accommodate multi values, but we could use Join function tocontained multi values in an array, and then use Instr function to check if the field value exists , for more details please refer to steps as below.
    1. Filter Expression =IIF(Instr(Join(Parameters!pQueue.Value), Fields!Queue.Value)>0 or Instr(Join(Parameters!pQueue.Value), "All")>0,true,flase).
    2. Select “=” from Operator drop-down list.
    3. Type in “=true” in the Value textbox.
     
    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny LiuModerator Monday, May 13, 2013 1:32 AM
    • Marked as answer by JohnWeb Monday, May 13, 2013 4:41 PM
    Friday, May 10, 2013 7:41 AM
    Moderator

All replies

  • Hello,

    How did you specify the filter expression on dataset? If the parameter allow multiple values, please refer to the following expression:
    =IIF(Fields!fieldname.Value IN Join(Parameters!parametername.Value,","),truepart,falsepart)
    Reference:http://msdn.microsoft.com/en-us/library/aa337293(v=sql.100).aspx

    If the issue presists, please post the filter expression for further analysis.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Thursday, May 9, 2013 2:26 AM
    Moderator
  • There mulitple values/items in the drop down that can be selected individually or "All".

    This is the error when I run the report using my original code.

    This is the code in the filter expression

    "All", "All", Fields!Queue.Value)

    This is the code in the Filter Expression Value

    ",")

    When I tried your code in the FilterExpression I got this error - "The FilterExpression for the dataset DataSet1 contains an error: [BC30455] Argument not specified for parameter 'TruePart' of Public IIF(Expression as Boolean, TruePart as Object, FalsePart as Object) as Object"

    ","), "All", Fields!Queue.Value)

    Any help is highly appreciated.


    =IIF(Fields!Queue.Value IN Join(Parameters!pQueue.Value,

    =join(Parameters!pQueue.Value,

    =IIF(Parameters!pQueue.Value=

    • Edited by JohnWeb Monday, May 13, 2013 4:41 PM
    Thursday, May 9, 2013 2:48 PM
  • Hello,

    According to your description, you would like to create an OR filter for result plus "All" values. Although, there is no IN operator in expression builder to accommodate multi values, but we could use Join function tocontained multi values in an array, and then use Instr function to check if the field value exists , for more details please refer to steps as below.
    1. Filter Expression =IIF(Instr(Join(Parameters!pQueue.Value), Fields!Queue.Value)>0 or Instr(Join(Parameters!pQueue.Value), "All")>0,true,flase).
    2. Select “=” from Operator drop-down list.
    3. Type in “=true” in the Value textbox.
     
    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny LiuModerator Monday, May 13, 2013 1:32 AM
    • Marked as answer by JohnWeb Monday, May 13, 2013 4:41 PM
    Friday, May 10, 2013 7:41 AM
    Moderator