locked
Report Builder 3.0 SharePoint list - Optional parameter RRS feed

  • Question

  • Hi

    I would like to add optional parameter to my dataset however I am not sure how to set parameter as not mandatory.

    At the moment when report loads I am asked to provide parameter and then my values are returned. I would like to have all values returned by default and then if needed filter them using parameter.

    I looked through the following post  http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/d2dd4521-fedd-4501-a675-1ec3b4b87989 however I am not sure how to implement this using sharepoint list.

    Please see below sp list query:

    <RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <ListName>CPD Log</ListName>
      <ViewFields>
        <FieldRef Name="Attended" />
        <FieldRef Name="Booked" />
        <FieldRef Name="Staff_x0020_Member" />
      </ViewFields>
      <Query>
        <Where>
          <Contains>
            <FieldRef Name="Staff_x0020_Member" />
            <Value Type="Text">
              <Parameter Name="Staff Member" />
            </Value>
          </Contains>
        </Where>
      </Query>
    </RSSharePointList>

    Thanks for help in advanced.

    Marcin


    Regards, Marcin (Please mark as helpful or answered if it helps)

    Wednesday, May 30, 2012 1:44 PM

Answers

  • Hi Marcin,

    Thanks for your post.

    According to the screenshots, you need to do as follows:

    1. Remove the filter from the Query Designer.
    2. If the “Staff_Member” field is “Integer” type, please change the parameter data type to “Integer” as well. That is to say, the field “Staff_Member” and the parameter “Staff_Member” must have the same data type.
    3. Set the parameter “Default Values” retrieving data from the dataset query.
    4. Add a filter to the tablix like below:
      Expression: [Staff_ Member]
      Operator: In
      Value: [@Staff_Member]

    The screenshots below are for your reference:

    Regards,
    Mike Yin

    • Marked as answer by Marcin C Thursday, June 7, 2012 1:13 PM
    Wednesday, June 6, 2012 11:33 AM
  • Hi Kellyshl,

    Thanks for your posting.

    Yes, we can add another parameter as you expect. Please follow the steps below:

    1. Create a “text” type parameter “NamePart”, check “Allow null value”.
    2. Set both of the parameter “Available Values” and “Default Values” to “None”.
    3. Add a filter to the “Staff_Member” group from the grouping pane like below:
      Expression: [Staff_Member]
      Operator: LIKE
      Value: =”*” & Parameters!NamePart.Value & “*”

    In this way, the initial value for the NamePart parameter is Null at runtime, and we can uncheck the Null box and input a value to filter the Staff_Member data.

    The screenshot below is for your reference:

    Regards,
    Mike Yin 

    • Marked as answer by Marcin C Thursday, June 7, 2012 1:12 PM
    Thursday, June 7, 2012 11:20 AM

All replies

  • Hi Marcin,

    When using a SharePointlist data source, there is no “In” operator in the Query Designer. To achieve your goal, you can add a multi-value parameter in the Report Builder like what we usually do using a SQL Server data source. The parameter retrieves available values and default values from the SharePointlist query, and then you can add a filter to the target group in the tablix like below:
    Expression: [Staff_x0020_Member]
    Operator: In
    Value: [@Staff_Member]

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    • Proposed as answer by Sandip Shinde Monday, June 4, 2012 3:12 PM
    Monday, June 4, 2012 2:16 PM
  • Hi Mike

    It didn't work for me. I created parameter using "Add Parameter" link and then tried to filter tablix.

    Expression: [Staff_x020_Member]

    Operator: Like

    Value: [@Staff_Member]       //parameter created via "Add Parameter" link.

    Am I doing something wrong?

    Also Staff_Member field holds text not int value.


    Regards, Marcin (Please mark as helpful or answered if it helps)


    • Edited by Marcin C Wednesday, June 6, 2012 9:38 AM
    Wednesday, June 6, 2012 9:23 AM
  • Hi Marcin,

    Thanks for your posting.

    Since the “Staff_Member ” is a multi-value parameter, you should use the “In” operator.

    According to your description, the data type of “Staff_x020_Member” filed is “Integer” while the parameter’s data type is text, right? If so, you can change the parameter to be an “Integer” parameter which retrieves values from the target dataset “Staff_x020_Member” field. Then, you can use the “In” operator in the filter.

    Regards,
    Mike Yin

    Wednesday, June 6, 2012 10:08 AM
  • Hi Mike

    Thanks for your posts, however I am still not able to get this to work.

    Did you actually got this to working with SharePoint lists?

    I cannot use multivalue parameter because filter is changing to dropdown instead of text box.

    The only way to set up the filter at the moment is to go to dataset properties -> Query -> QueryDesigner.

    And then under parameter properties set things up as follow:

    I don't have any filters on tablix.

    My initial screen is blank but then when I provide value I got data visiable.

    I am still not able to get all values returned on initial screen.

    From this point no matter what I change I either got error or my data are not display.

    Many thanks for help.


    Regards, Marcin (Please mark as helpful or answered if it helps)

    Wednesday, June 6, 2012 10:51 AM
  • Hi Marcin,

    Thanks for your post.

    According to the screenshots, you need to do as follows:

    1. Remove the filter from the Query Designer.
    2. If the “Staff_Member” field is “Integer” type, please change the parameter data type to “Integer” as well. That is to say, the field “Staff_Member” and the parameter “Staff_Member” must have the same data type.
    3. Set the parameter “Default Values” retrieving data from the dataset query.
    4. Add a filter to the tablix like below:
      Expression: [Staff_ Member]
      Operator: In
      Value: [@Staff_Member]

    The screenshots below are for your reference:

    Regards,
    Mike Yin

    • Marked as answer by Marcin C Thursday, June 7, 2012 1:13 PM
    Wednesday, June 6, 2012 11:33 AM
  • Hi Mike

    Thanks for great post.

    One last question. I managed to get this to work almost as I would like to using checkboxes, however the way it works is still not perfect as I have more than 200 records. Therefore checking and unchecking tick boxes may be difficult for the users.

    Is there a way to introduce another parameter which will filter all fields via typed text.

    Many thanks Marcin


    Regards, Marcin (Please mark as helpful or answered if it helps)

    Wednesday, June 6, 2012 2:05 PM
  • Hi Kellyshl,

    Thanks for your posting.

    Yes, we can add another parameter as you expect. Please follow the steps below:

    1. Create a “text” type parameter “NamePart”, check “Allow null value”.
    2. Set both of the parameter “Available Values” and “Default Values” to “None”.
    3. Add a filter to the “Staff_Member” group from the grouping pane like below:
      Expression: [Staff_Member]
      Operator: LIKE
      Value: =”*” & Parameters!NamePart.Value & “*”

    In this way, the initial value for the NamePart parameter is Null at runtime, and we can uncheck the Null box and input a value to filter the Staff_Member data.

    The screenshot below is for your reference:

    Regards,
    Mike Yin 

    • Marked as answer by Marcin C Thursday, June 7, 2012 1:12 PM
    Thursday, June 7, 2012 11:20 AM
  • Hi Mike

    Thanks for your reply. It works as I would expect but when I save a report and try to open it using webpart viewer second parameter doesn't have null checkbox ticked despite everything was fine inside of Microsoft SQL Server Report Builder.


    Regards, Marcin (Please mark as helpful or answered if it helps)

    Thursday, June 7, 2012 1:40 PM
  • Hi Marcin ,

    Thanks for your posting.

    To avoid the issue, we can edit the parameter properties on the SharePoint site. Please open the “Manage Parameters” page of the report, and then refer to the following screenshot:

    Regards,
    Mike Yin

    Friday, June 8, 2012 12:27 PM
  • Great explanation Mike. Many Thanks.

    Regards, Marcin (Please mark as helpful or answered if it helps)

    Friday, June 8, 2012 12:47 PM