none
How to display items for the (Select All) option in the multivalue parameter? RRS feed

  • Question

  • Hi I am currently working with sharepoint list and ssrs to do a report. SSRS and CAML query.

    The requiremnt is to display the report for the selected categories within the date of range.

    The are 3 parameter used:

    1. Category=multivalue which hold the value for New Student, Transfer and Termination categories. Data Type: text, multivalue

    2. StartDate= From ;Data Type:Date/Time

    3. EnfdDate= To ;Data Type:Date/Time

    Right now only can diplay the data for each of the category selection within a date of range.

    When I choose more than 1 category, it only displays the first category selection. Although i have removed the value (0)

    Below are the code that i configued for the filyter in the dataset:-

    =Fields!Category.Value=Parameters!Category.Value(0)  to:

    =Fields!Category.Value=Parameters!Category.Value--------for the filter expression

    Operator:In

    value: =Parameters!Category.IsMultiValue

    the results are still the same. I could not display all the data

     for any 2 category or 3 category selection. Please help me what is the CAML code should be used to display all the data in the filter.

     

    I did the filter at the dataset.

     

    Thursday, December 1, 2011 12:06 PM

Answers

  • You don't need to use any JOIN logic or the IsMultiValue evaluation.

    Simply leave the filter expression to reference your field (Fields!Category.Value), leave the Operator set to In, and then the filter value will reference your parameter that is setup to Allow Multiple Values which is Parameters!Category.Value.

    That is all you need to do, SSRS will properly pass in the multiple selections to filter your dataset.

    I replied to your comment on my blog posting as well - http://denglishbi.wordpress.com/2011/04/13/using-sharepoint-list-data-for-reporting-services-ssrs-parameter-source/.


    Dan English's BI Blog
    • Marked as answer by Shree Veethia Sunday, December 4, 2011 1:28 PM
    Saturday, December 3, 2011 6:25 PM
  • A. In the Shared dataset, under Filter Tab:

    I added 2 filters:

    1. To filter the data according to the avvailbale values in the Report Parameter

    Expression: =Fields!Category.Value=Join(Parameters!Category.Value,",")
    Operator: In   ; Data Type: Text  ; Value=Parameters!Category.IsMultiValue

    Change above expression as

    Expression: =Fields!Category.Value=Parameters!Category.Value Operator: In   ; Data Type: Text  ; Value=Parameters!Category.Value

    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!



    Friday, December 2, 2011 7:39 AM

All replies

  • Shree,

    If you are using multivalue parameters then you need to take care of few things. As you mentioned value(0) in multivalue parameter this means multivalue parameters are kind of array. if you want all selected values then try following expression.

    Basically you need to join all selected values to make delimited string out of it.

    =Fields!Category.Value=Join(Parameters!Category.Value,",")


    Thanks, Sandip Please "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
    • Proposed as answer by Aftab Ansari Thursday, December 1, 2011 1:19 PM
    Thursday, December 1, 2011 12:11 PM
  • Hi Sandip,

    It is only displaying data for each one  of the category now. Even it never display any  data  for the first selected category when I select all or 2 category, after I  changed the code =Fields!Category.Value=Join(Parameters!Category.Value,",").

    I need to display the all the data when I Select All for the category parameter.

     

    Kindly please help. I have been working for this issue for the pass 2 weeks. really need some help here.


    veethia
    Thursday, December 1, 2011 3:38 PM
  • Veethia,

    I got your point. You have a dataset with three parameters says Category, Start Date & End Date. Along with this you have a above three Report Parameters. Now you have to pass "Select All" categories to your dataset.

    Plesae refer following URL where you understand it clearly. If still you have any issues write me back. will create step by step document & share with you.

    http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/


    Thanks, Sandip Please "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
    Thursday, December 1, 2011 3:54 PM
  • Hi Sandip,

    I followed the steps in the steps from the above link,yet i still get error. let me explain how did I do it, then suggest me how to solev this issue.

    I created a dataset for the main report and another separated shared dataset to create the report parameters.

    In the Solution Explorer:

    Here is the code that I used for the main dataset:

    <RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <ListName>ESR</ListName>
      <ViewFields>
        <FieldRef Name="Category" />
        <FieldRef Name="Employee_x0020_ID" />
        <FieldRef Name="Title" />
        <FieldRef Name="First_x0020_Name" />
        <FieldRef Name="Employee_x0020_Classification" />
        <FieldRef Name="Manager_x0020_Name" />
        <FieldRef Name="Manager_x0020_ID" />
        <FieldRef Name="Previous_x0020_Manager" />
        <FieldRef Name="Previous_x0020_Manager_x0020_ID" />
        <FieldRef Name="New_x0020_Manager" />
        <FieldRef Name="New_x0020_Manager_x0020_ID" />
        <FieldRef Name="Transfer_x0020_Effective_x0020_D" />
        <FieldRef Name="Termination_x0020_Effective_x002" />
        <FieldRef Name="Notes" />
        <FieldRef Name="LinkTitleNoMenu" />
        <FieldRef Name="LinkTitle" />
        <FieldRef Name="Hire_x0020_Date" />
        <FieldRef Name="IT_x0020_Tracking_x0020_Ref" />
        <FieldRef Name="Cost_x0020_Center" />
        <FieldRef Name="Location" />
        <FieldRef Name="Department" />
        <FieldRef Name="Previous_x0020_Cost_x0020_Center" />
        <FieldRef Name="New_x0020_Cost_x0020_Center" />
        <FieldRef Name="SAP_x0020_ID" />
        <FieldRef Name="ID" />
        <FieldRef Name="ContentType" />
        <FieldRef Name="Modified" />
        <FieldRef Name="Created" />
        <FieldRef Name="Author" />
        <FieldRef Name="Editor" />
        <FieldRef Name="_UIVersionString" />
        <FieldRef Name="Attachments" />
        <FieldRef Name="Edit" />
        <FieldRef Name="DocIcon" />
        <FieldRef Name="ItemChildCount" />
        <FieldRef Name="FolderChildCount" />
      </ViewFields>
      <Query>
        <Where>
    <Or>
          <IsNull>
            <FieldRef Name="Category" />
    <Parameter Name="Category"/>
          </IsNull>
    </Or>
    <In>
    <FieldRef Name="Category">
    <Value Type="Text">@Category</Value>
    <IfNot>(Select All)</IfNot>
    </FieldRef>
    </In>
        </Where>
      </Query>
    </RSSharePointList>

     

    then in the main dataset under parameter tab,

    There are 3 parameter were created:

    1. Category, Data Type:text, Allow Multiple Values, Default Value:NEW HIRE

    2. StartDate,DataType:Date/Time,Default Value:1/1/2011

    2. EndDate,DataType:Date/Time,Default Value:11/1/2011

    In the Report Data Pane

    created 3 parameters which is:

    1. Category, DataType:Text, Allow Multiple Values,

    Available Values:Specify Value-  Label:NEW HIRE, fx:="NEW HIRE" ; Label:TRANSFER, fx:="TRANSFER"  ;  Label:TERMINATION, fx:="TERMINATION"

    Default Value:Specify values: NEW HIRE

    2.  StartDate,DataType:Date/Time,Default Value:1/1/2011

      3. EndDate,DataType:Date/Time,Default Value:11/1/2011

     

    A. In the Shared dataset, under Filter Tab:

    I added 2 filters:

    1. To filter the data according to the avvailbale values in the Report Parameter

    Expression: =Fields!Category.Value=Join(Parameters!Category.Value,",")
    Operator: In   ; Data Type: Text  ; Value=Parameters!Category.IsMultiValue

    2. To filter the category within a date of range.

    Expression: =Fields!Created.Value
    Operator: Between   ; Data Type: Date/Time  ; Value=Parameters!StartDate.Value ; Value=Parameters!EndDate.Value

     

    B. In the Shared dataset, under Parameter Tab:

    There are 3 query parameters:

    1. Parameter Name:Category
    Parameter Value:=Parameters!Category.IsMultiValue

    . 2. Parameter Name:StartDate
    Parameter Value:=Parameters!StartDate.Value

    .3.  Parameter Name:EndDate
    Parameter Value:Parameters!EndDate.Value

     

    This is what i have done, but there is no result all now. I need to display the data according for the category selection (either for 1, 2 or 3 category) within a date of range. kindly please help.I really need a guide to coach me ;)..

     

     

    Hi tried by modifying the code for the Category parameter as follows: 

    1. Category, DataType:Text, Allow Multiple Values,

    Available Values:Specify Value-

     Label:NEW HIRE, fx:=Split(Code.RemoveDups(Join(Parameters!Category.Value, ",")),","); Label:TRANSFER, fx:=Split(Code.RemoveDups(Join(Parameters!Category.Value, ",")),",")  ;  Label:TERMINATION, fx:==Split(Code.RemoveDups(Join(Parameters!Category.Value, ",")),",")

    Default Value:Specify values: =Split(Code.RemoveDups(Join(Parameters!Category.Value, ",")),",").(0)

    Yet i still did not get to display all the category items together, it displays zero as result.


    veethia
    Friday, December 2, 2011 2:34 AM
  • Hi,

    Try with the yellow highlighted changes in your report :

    ·         A. In the Shared dataset, under Filter Tab:

    I added 2 filters:

    1. To filter the data according to the avvailbale values in the Report Parameter

    Expression: =Fields!Category.Value
    Operator: In   ; Data Type: Text  ; Value= Parameters!Category.Value

    2. To filter the category within a date of range.

    Expression: =Fields!Created.Value
    Operator: Between   ; Data Type: Date/Time  ; Value=Parameters!StartDate.Value ; Value=Parameters!EndDate.Value


    Aftab Ansari
    Friday, December 2, 2011 7:35 AM
  • A. In the Shared dataset, under Filter Tab:

    I added 2 filters:

    1. To filter the data according to the avvailbale values in the Report Parameter

    Expression: =Fields!Category.Value=Join(Parameters!Category.Value,",")
    Operator: In   ; Data Type: Text  ; Value=Parameters!Category.IsMultiValue

    Change above expression as

    Expression: =Fields!Category.Value=Parameters!Category.Value Operator: In   ; Data Type: Text  ; Value=Parameters!Category.Value

    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!



    Friday, December 2, 2011 7:39 AM
  • hI,

    The result is still the sAME. Only displays the each of the category.

    The date filter works fine.

    I need to select all the category and display the results together. please help.

     


    veethia
    Friday, December 2, 2011 9:49 AM
  • hI,

    The result is still the sAME. Only displays the each of the category.

    The date filter works fine.

    I need to select all the category and display the results together. please help.


    veethia
    Friday, December 2, 2011 9:49 AM
  • Hi Shree,

    Kindly open your report (.Rdl) file in notepad and search for filter keyword. You will find something as below.

       

       <Filters>
            <Filter>
              <FilterExpression>=Fields!Mon.Value</FilterExpression>
              <Operator>In</Operator>
              <FilterValues>
                <FilterValue>=Parameters!ReportParameter1.Value</FilterValue>
              </FilterValues>
            </Filter>
          </Filters>
    

    Please post your filter xml here.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Friday, December 2, 2011 9:58 AM
  • =Fields!Category.Value=Join(Parameters!Category.Value,",") In =Parameters!Category.IsMultiValue =CDate(Fields!Created.Value) Between =Parameters!StartDate.Value =Parameters!EndDate.Value
    <Filters>
            <Filter>
              <FilterExpression>=Fields!Category.Value=Join(Parameters!Category.Value,",")</FilterExpression>
              <Operator>In</Operator>
              <FilterValues>
                <FilterValue>=Parameters!Category.IsMultiValue</FilterValue>
              </FilterValues>
            </Filter>
            <Filter>
              <FilterExpression>=CDate(Fields!Created.Value)</FilterExpression>
              <Operator>Between</Operator>
              <FilterValues>
                <FilterValue>=Parameters!StartDate.Value</FilterValue>
                <FilterValue>=Parameters!EndDate.Value</FilterValue>
              </FilterValues>
            </Filter>
          </Filters>


    veethia
    Saturday, December 3, 2011 2:57 AM
  • You don't need to use any JOIN logic or the IsMultiValue evaluation.

    Simply leave the filter expression to reference your field (Fields!Category.Value), leave the Operator set to In, and then the filter value will reference your parameter that is setup to Allow Multiple Values which is Parameters!Category.Value.

    That is all you need to do, SSRS will properly pass in the multiple selections to filter your dataset.

    I replied to your comment on my blog posting as well - http://denglishbi.wordpress.com/2011/04/13/using-sharepoint-list-data-for-reporting-services-ssrs-parameter-source/.


    Dan English's BI Blog
    • Marked as answer by Shree Veethia Sunday, December 4, 2011 1:28 PM
    Saturday, December 3, 2011 6:25 PM
  • Hi,

    As Dan said, you do not have to use JOIN. Make sure your filter look like below:

    <Filter>
          <FilterExpression>=Fields!Category.Value</FilterExpression>
          <Operator>In</Operator>
          <FilterValues>
                <FilterValue>=Parameters!Category.Value</FilterValue>
          </FilterValues>
    </Filter>
    
    


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Sunday, December 4, 2011 9:42 AM
  • Hi Dan,

    Thanks a lot for the reply. your suggestion work for my problem when i made changes as what you said and chnages in the main dataset as follows:

    <Query>
        <Where>
    <Or>
          <IsNull>
            <FieldRef Name="Category" />
    <Parameter Name="Category"/>
          </IsNull>
    </Or>
    <In>
    <FieldRef Name="Category">
    <Value Type="Text">@Category</Value>
    <IfNot>(Select All)</IfNot>
    </FieldRef>
    </In>
        </Where>
      </Query>

     

    changed as below:

    <Query>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
    <Parameter>
    <Parameter Name="Category"/>
    <Parameter Name="StartDate"/>
    <Parameter Name="EndDate"/>
    </Parameter>
     </Query>

     

    So when i click preview button the reports seems to work fine as i could display 1 , 2 or category selection perfectly with the date of range. But when I click on the run report, the report only displays output same as previous result, for the first category selection only. Kindly please advise.

     

    Do i have to do the filter on the main dataset also? Or filter on the shared dataset is already enough? Please advise.

     


    veethia
    Sunday, December 4, 2011 1:27 PM
  • Hi Asim,

    Thanks a lot for the reply. your suggestion work for my problem when i made changes as what you said and chnages in the main dataset as follows:

    <Query>
        <Where>
    <Or>
          <IsNull>
            <FieldRef Name="Category" />
    <Parameter Name="Category"/>
          </IsNull>
    </Or>
    <In>
    <FieldRef Name="Category">
    <Value Type="Text">@Category</Value>
    <IfNot>(Select All)</IfNot>
    </FieldRef>
    </In>
        </Where>
      </Query>

     

    changed as below:

    <Query>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
    <Parameter>
    <Parameter Name="Category"/>
    <Parameter Name="StartDate"/>
    <Parameter Name="EndDate"/>
    </Parameter>
     </Query>

     

    So when i click preview button the reports seems to work fine as i could display 1 , 2 or category selection perfectly with the date of range. But when I click on the run report, the report only displays output same as previous result, for the first category selection only. Kindly please advise.

     

    Do i have to do the filter on the main dataset also? Or filter on the shared dataset is already enough? Please advise.


    veethia
    Sunday, December 4, 2011 1:29 PM
  • Your main dataset should have the filter logic so that it limits the categories for your report.  You shouldn't have to do anything special for the (Select All) option since that simply passes all of the values, so it is a bit different than the setup I have where I added in an extra 'All' value that I can reference since it wasn't originally a multivalue setup.

    In doing the multivalue setup my query looks like the following with the filter logic added:

    <DataSet Name="StoreInformation">
          <Query>
            <DataSourceName>myBI10</DataSourceName>
            <CommandText>&lt;RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
      &lt;ListName&gt;Store Information&lt;/ListName&gt;
      &lt;ViewFields&gt;
        &lt;FieldRef Name="Name1" /&gt;
        &lt;FieldRef Name="AddressLine1" /&gt;
        &lt;FieldRef Name="AddressLine2" /&gt;
        &lt;FieldRef Name="City" /&gt;
        &lt;FieldRef Name="StateProvinceName" /&gt;
        &lt;FieldRef Name="PostalCode" /&gt;
        &lt;FieldRef Name="CountryRegionName" /&gt;
      &lt;/ViewFields&gt;
    &lt;/RSSharePointList&gt;</CommandText>
            <rd:DesignerState>
              <QueryDefinition xmlns="http://schemas.microsoft.com/ReportingServices/QueryDefinition/Relational">
                <SelectedColumns>
                  <ColumnExpression ColumnOwner="Store Information" ColumnName="Name1" />
                  <ColumnExpression ColumnOwner="Store Information" ColumnName="AddressLine1" />
                  <ColumnExpression ColumnOwner="Store Information" ColumnName="AddressLine2" />
                  <ColumnExpression ColumnOwner="Store Information" ColumnName="City" />
                  <ColumnExpression ColumnOwner="Store Information" ColumnName="StateProvinceName" />
                  <ColumnExpression ColumnOwner="Store Information" ColumnName="PostalCode" />
                  <ColumnExpression ColumnOwner="Store Information" ColumnName="CountryRegionName" />
                </SelectedColumns>
              </QueryDefinition>
            </rd:DesignerState>
          </Query>
          <Fields>
            <Field Name="Name1">
              <DataField>Name1</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="AddressLine1">
              <DataField>AddressLine1</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="AddressLine2">
              <DataField>AddressLine2</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="City">
              <DataField>City</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="StateProvinceName">
              <DataField>StateProvinceName</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="PostalCode">
              <DataField>PostalCode</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="CountryRegionName">
              <DataField>CountryRegionName</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
          </Fields>
          <Filters>
            <Filter>
              <FilterExpression>=Fields!CountryRegionName.Value</FilterExpression>
              <Operator>In</Operator>
              <FilterValues>
                <FilterValue>=Parameters!CountryName.Value</FilterValue>
              </FilterValues>
            </Filter>
          </Filters>
        </DataSet>


    Dan English's BI Blog
    Sunday, December 4, 2011 1:57 PM
  • Hi dan,

    When I want to set the filter at the main datset, I could not find the parameters are available to be used in the Parametrs slection because it shows as "report has no parameters". Please advise.

    Even after i tried to add the filters at the main dataset there are additional parameters are created automatically.The previous parameters were created manually I am x clear what i should do now. .What i should do now? please advise.


    veethia
    Sunday, December 4, 2011 3:07 PM
  • You should define Parameters in the Report Data section if you have not done so already.  This is outside the Dataset and then you will reference those.  Take a look at my posting for the details on how to do that.


    Dan English's BI Blog
    Sunday, December 4, 2011 3:18 PM
  • Hi dan,

    Shall i follow the steps above? could you please send me a link to your post sir? I have already define 3 parameters as mentioned in the post above. Now if i want to recall those parameters in the main data set, the parameters and the field for filter, it says that the report items do not have parameters /fields. please advise. thanks.

     


    veethia

     

     

    Hi Dan,,

    It is done....I workaround all over again by creating new project. The result is done and meet the requirement....


    Thanks a lot everyone for the help...i really appreciate it..Kindly please advise incase there is any other way to achieve this requirement just for the prupose of sharing..;)
    Sunday, December 4, 2011 3:50 PM
  • You can review my post here - http://denglishbi.wordpress.com/2011/04/13/using-sharepoint-list-data-for-reporting-services-ssrs-parameter-source/. You can download my sample files that are based on the original posting along with the PDF version of the post for reference that are available at the bottom of the post.

    You don't need to setup any parameters in your main dataset, you simply reference the ones that you defined in the Report Data and you will reference these in your Filters area of the main dataset.


    Dan English's BI Blog
    Monday, December 5, 2011 2:43 AM
  • Hi Asim Bagwan,

     

    Please help me. my issue is in the link below.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e4be0cc9-8e60-49a6-a512-04ab0e85efbb

    Data is is not displayed for a publishes report. please help.

     


    veethia
    Tuesday, December 13, 2011 4:07 AM
  • Hi Shree,

    Unfortunately, I do not have Sharepoint setup here at my workplace. So I can not simulate your situation and give some workaround.

    Hopefully some experts here in forum can give some valuable input on the same.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Tuesday, December 13, 2011 8:01 AM
  • Tuesday, December 20, 2011 8:40 AM