none
"How to get distinct values of sharepoint column using SSRS"

    Question

  • Hi,

        I have integrated sharepoint list data to SQL Server reporting services. I am using the below to query sharepoint list data using sql reporting services.

    <Query>
       <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
       <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
          <Parameters>
             <Parameter Name="listName">
                <DefaultValue>{GUID of list}</DefaultValue>
             </Parameter>
             <Parameter Name="viewName">
                <DefaultValue>{GUID of listview}</DefaultValue>
             </Parameter>
             <Parameter Name="rowLimit">
                <DefaultValue>9999</DefaultValue>
             </Parameter>           
          </Parameters>
       </Method>  
    <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>

    By using this query, I am getting a dataset which includes all the columns of sharepoint list. Among these columns, I wanted to display only 2 columns (i.e Region and Sales type) using chart. I have created a Region parameter but when I click preview, the drop down box is giving me all the repeatative values of region like RG1,RG1,RG1,RG2,RG2,RG2,RG2,RG3.......... I wanted to display only distinct values of Region parameter so that whenever end user select region from the parameter drop down, it will display the respective value of Sales type column.
    Also when I select only RG1 parameter, it is giving me a chart including the sales type of all the Regions. (it should display me only the sales type of RG1) How can I link these 2 columns so that they will display the values respectively.
     
              I would really appreciate if anyone can help me out with this.

    Thanks,
    Sam.
    Wednesday, May 13, 2009 3:20 PM

Answers

  • Hi Sam,

     

    By code, the CAML language doesn’t have any reserved word (or tag) to set this particular filter to remove duplicate results.

     

    In this case, we could use the custom code to get distinct records.

    Here are the detailed steps:

    1.         Create a hidden parameter that gets all the records in one field.

    Note: Please create another dataset that is same of the main dataset. This dataset is used for the parameter.

    2.         Create a function that used to remove the duplicate records.

    Here is the code:

    Public Shared Function RemoveDups(ByVal items As String) As String
             Dim noDups As New System.Collections.ArrayList()
             
             Dim SpStr 
             SpStr = Split(items ,",") 
     
             For i As Integer=0 To Ubound(Spstr)
                       If Not noDups.Contains(SpStr(i).Trim()) Then
                                noDups.Add(SpStr(i).Trim())                        
                       End If
             Next
     
             Dim uniqueItems As String() = New String(noDups.Count-1){}
             noDups.CopyTo(uniqueItems)
             
             Return String.Join(",", uniqueItems)
    End Function

    3.         Create another parameter that will be used for filtering the maindata.

    Please set the available value to be =Split(Code.RemoveDups(JOIN(Parameters!ISSUE_STATUS_TEMP.Value, ",")), ",")

    And the default value to be the value you what such as the first value:

    =Split(Code.RemoveDups(JOIN(Parameters!ISSUE_STATUS_TEMP.Value, ",")), ",").(0)

    4.         Go to the main dataset. Open the property window of this dataset.

    5.         In the “Filters” tab, set the filter to be:

    Expression: <The field to be filter>

    Operator: =

    Value: =Parameters!Region.Value

     

    The parameter “Region” should be the parameter we created in the step3.

     

    Now, we should get distinct values of SharePoint columns.

     

    If there is anything unclear, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    Friday, May 15, 2009 4:48 AM
    Moderator

All replies

  • Hi Sam,

     

    By code, the CAML language doesn’t have any reserved word (or tag) to set this particular filter to remove duplicate results.

     

    In this case, we could use the custom code to get distinct records.

    Here are the detailed steps:

    1.         Create a hidden parameter that gets all the records in one field.

    Note: Please create another dataset that is same of the main dataset. This dataset is used for the parameter.

    2.         Create a function that used to remove the duplicate records.

    Here is the code:

    Public Shared Function RemoveDups(ByVal items As String) As String
             Dim noDups As New System.Collections.ArrayList()
             
             Dim SpStr 
             SpStr = Split(items ,",") 
     
             For i As Integer=0 To Ubound(Spstr)
                       If Not noDups.Contains(SpStr(i).Trim()) Then
                                noDups.Add(SpStr(i).Trim())                        
                       End If
             Next
     
             Dim uniqueItems As String() = New String(noDups.Count-1){}
             noDups.CopyTo(uniqueItems)
             
             Return String.Join(",", uniqueItems)
    End Function

    3.         Create another parameter that will be used for filtering the maindata.

    Please set the available value to be =Split(Code.RemoveDups(JOIN(Parameters!ISSUE_STATUS_TEMP.Value, ",")), ",")

    And the default value to be the value you what such as the first value:

    =Split(Code.RemoveDups(JOIN(Parameters!ISSUE_STATUS_TEMP.Value, ",")), ",").(0)

    4.         Go to the main dataset. Open the property window of this dataset.

    5.         In the “Filters” tab, set the filter to be:

    Expression: <The field to be filter>

    Operator: =

    Value: =Parameters!Region.Value

     

    The parameter “Region” should be the parameter we created in the step3.

     

    Now, we should get distinct values of SharePoint columns.

     

    If there is anything unclear, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    Friday, May 15, 2009 4:48 AM
    Moderator
  • Hi Jin,

            Thanks a lot for giving reply. I have resolved this problem by directly getting the distinct column value from sql database which has all the values of sharepoint list. But now I am facing below problem.

            I have Date column in my sharepoint list. I set Date column as a parameter. In my sharepoint list, I have all the repeatative values for the Date so I ceated another dataset for date directly from sql database which has values of all sharepoint list columns.But the problem is in database, the date value is in the datetime format like (10/12/2005 8:00:00 AM) and in my sharepoint list column it is in mm/dd/year format like (10/12/2005) . when I try to run report bu putting the date value from dataset, It is giving me "no report data" message. 

          so do you have any idea on this? which is the required format for date to retrieve data from sharepoint list?

    Thanks,
    Sam.   
    Friday, May 22, 2009 1:31 PM
  • Hi srpatel,

    The issue seems to be cuased by the time(without date) in the SharePoint list is  not equal to the time in the parameter. I assume you want to get the data in one day. If I have misunderstood, please don't hesitate to let me know.

    Based on the performance of the Transact-SQL, to solve the issue, you can use the following statement to get data:
         WHERE [DateColumn] > DateParameter AND [DateColumn] < DateAdd("day", 1, DateParameter)

    For more information, please see:
    Query Performance Tuning (SQL Server Compact): http://msdn.microsoft.com/en-us/library/ms172984.aspx (Find the key "SARG")
    datetime (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187819.aspx

    If there is anything unclear, please feel free to ask.

    Thanks,
    Jin
    Jin Chen - MSFT
    Monday, May 25, 2009 1:34 AM
    Moderator
  • I am trying to do the above steps to remove duplicate values, but the parameter is coming disabled. Any idea what i might be doing wrong.

    Shabih
    Wednesday, May 27, 2009 4:50 AM
  • Hi Jim,

              I am facing a new problem here. Suddenly, my report stops working.  I run the fiddler and I am getting the below output.

    <?xml version="1.0" encoding="utf-8"?>
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Body>
    <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
    <listName>{CA7A733B-F52E-4BB9-B898-61A12B3B837C}</listName>
    <viewName>{601F5FAD-473F-49FA-9BFA-AA1F8B9FE9C1}</viewName>
    <rowLimit>9999</rowLimit>
    </GetListItems>
    </soap:Body>
    </soap:Envelope>

    Output :
    <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetListItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/"><GetListItemsResult><listitems xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
    <rs:data ItemCount="0">
    </rs:data>
    </listitems></GetListItemsResult></GetListItemsResponse></soap:Body></soap:Envelope>

    Actually yesterday Shared Services Provider stopped working and I made the below changes

    http://servername/ssp/admin/_layouts/reghost.aspx  and select “Reset all pages in this site to site definition version” option and click “Reset”. and SSP starts working.
    I have no idea weather this has created any problem.


    can you plz help me out with this?

    Thanks,
    Sam.

    Friday, May 29, 2009 7:26 PM
  • Hello

    I am able to retrieve listitems. I made one changes in following line, replaced ListGUID with listname as mentioned below

    <listName>Invoices</listName>

    But still I would like to know why it was working with ListGuid earlier?

    If anyone know the cause, please let me know.

    Sam
    Friday, May 29, 2009 9:03 PM
  • Hi Jin

    Can you please clarify Step 3.

    Where do I set this value (Report Parameter Properties -> Available Values -> [None, Specify values, Get values from a query])?

    I'm using SSRS 2008. I tried "Specify values" and it does seem to work.

    Thanks in advance

    Thursday, June 18, 2009 9:37 PM
  • Hi Sridhar,

     

    To troubleshoot the issue, please follow these steps:

    1.       Please make sure the parameter that is created in Step1 is multi-values. It means the option “Allow multiple values” of the parameter is checked.

    2.       Drag a textbox in the report body, set the expression of the textbox to be:

    =JOIN(Parameters!ReportParameter1.Value, ",")

     

    Please make sure this is fine also.

    3.       Dray another textbox in the report body, set the expression of the textbox to be:

    =Code.RemoveDups(JOIN(Parameters!ReportParameter1.Value, ","))

     

    Please make sure this is fine also.

    4.       If the steps above are fine, the parameter created in Step3 will work fine.

    a)         Please make sure the parameter is allow multiple values.

    b)         In the “Available Values”, select “Specify Values”.

    c)         Click “Add”

    d)         In the “Label” textbox and the “Value” textbox, set the expression to be:

    =Split(Code.RemoveDups(JOIN(Parameters!ReportParameter1.Value, ",")), ",")

    e)         In the “Default Values”, select “Specify Values”

    f)          Click “Add”

    g)         In the “Value” textbox, set the expression to be:

    =Split(Code.RemoveDups(JOIN(Parameters!ReportParameter1.Value, ",")), ",")

     

    Now, the parameter should work fine.

     

    By the way, the forum does not alert me sometimes. I would like to suggest you to start a new question and refer to this thread in the question.

     

    Thanks,

    Jin Chen


    Jin Chen - MSFT
    • Proposed as answer by SQL MSBI Monday, March 18, 2013 12:38 PM
    Friday, June 19, 2009 2:13 AM
    Moderator
  • Hi Jin,

    I have same issue that Shabib Ahmed has posted earlier in this thread. I code worked for one of the Column, Duplicates removed and all unique items in the parameters but when I tried doing for another column of the sharepoint list, the Parameter was greyed.

    Can you please let me know where I am getting wrong?

    Thanks!
    Swapnil Sahu
    I am what I am...
    Wednesday, November 18, 2009 12:50 PM
  • Anyone,

    Who can help me out with this issue ?

    Thanks in advance!
    Swapnil Sahu
    I am what I am...
    Friday, November 27, 2009 4:33 AM
  • Hi Jin

    I am getting a "Unrocignized error when I am trying to set values for the report parameter created at step3. I followed the steps as said by you. i am using ssrs 2008. do i need to add any thing else?

    Monday, July 26, 2010 9:29 AM
  • Hi Jin,

    Can you be more descriptive   in this step---? In the “Filters” tab, set the filter to be:

    Expression: <The field to be filter>

    Operator: =

    Value: =Parameters!Region.Value

     

    The parameter “Region” should be the parameter we created in the step3.

    Thanks,

    RaghuRS

    Tuesday, September 21, 2010 8:55 PM
  • Hi Raghus,

     

    i believe what jin ment is the name of the parameter in the value --> Value:=Parameters!REGION.Value

    so i think you should rename the "REGION" according to the parameter name that you created.

    if you have ReportParameter1 then it should be "=Parameters!ReportParameter1.Value"

    CMIIW

     

    thx.

     

    -willy-

     

    Wednesday, September 29, 2010 3:37 AM
  • Hi, I get the same error: the Parameter is greyed out.  Any answer to this problem yet?
    Wednesday, September 29, 2010 11:20 AM
  • Jin,

    I implemented your solution and can see it working in Preview mode within BIDS. However when the report is deployed to the SP site, the parameters (the ones receiving the unique values) are grayed out. Any idea why this might be happening? Using SharePoint 2010.

     

    Thanks,

    Dhruv


    DR
    Thursday, June 02, 2011 2:38 PM
  • This works well as long as you do not run into one caveat.  I had alot of comma's in my values being returned.  In which case you need to change the commas to something else. I used semi colons and it worked wonderfully.  

     

    A couple of other notes to this.  

    1. adding a text box with the =Split(Code.RemoveDups(JOIN(Parameters!parameter1.Value, ",")), ",")

    In the Expression was a good code check

    2. If your Code.RemoveDups is underlined in an expression.  Don't worry about it and move on.  It will still run fine.

    3. on step 3 above, You are selection Specify Values. You are putting that line of code into both Label and the Values columns.  

     


    geek
    Monday, October 03, 2011 6:56 PM
  • Hi Jin,

     

    I try your walkthough but I get a message error back when I run the report:

    Unable to cast object of type 'System.String' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameter'.

    Have you got any idee how to fix it? 

     

    Thanks in advance.

    Sunday, December 11, 2011 5:54 PM
  • I have followed your steps as well as going through the walk through several times.  My problem is the I am getting nothing in the non-hidden parameter.  I unhid the first parameter and found that it is indeed collecting the records.  When I select some of the records in the dummy (hidden) parameter it then shows up in the parameter that I want to use.  My assumption then, is that the dummy parameter must start off with all items selected.  I set the default value of the dummy parameter as specified in the walkthrough to use the query and the field that is also used in the available fields.  What might I be doing wrong.

    THanks,

    Mark


    Mark A. Rupert
    Thursday, January 05, 2012 4:31 PM
  • This is a virus link, someone should delete this link.
    Thursday, February 02, 2012 10:26 PM
  • I would like to add one line of code to this for future people.  After the split add in this line

            Array.Sort(SpStr)

    That will sort the list correctly for the users. 


    geek

    Wednesday, February 08, 2012 8:30 PM
  • Hi Guys
    I followed all the steps and I succeeded, but the data are returned disordered.




    I tried using Asc and AscW like this:
    = Asc (Split (Code.RemoveDups (JOIN (Parameters! ReportParameter2.Value, "")), "")) but not work!

    How can I sort the values ​​of the parameters?


    Douglas Filipe http://douglasfilipe.wordpress.com

    Friday, March 16, 2012 12:19 AM
  • I use your way to solve the distinct issues, but then got another issues at my report parameter. My parameter will always auto postback (parameter that have the distinct record) event I already tick on checkbox never refresh....how to solve this??

    TQ

    Tuesday, May 15, 2012 8:41 AM
  • Hi Swapnil,

    Did you find a work around for your problem? I have got same issue.

    Thanks,

    Jaspreet

    Tuesday, May 29, 2012 9:10 PM
  • Hi Friend,

      i have followed your steps, even i am getting the following errors.

    "A error occurred during local report processing. The definition of the report '/Manager' is invalid. The report parameter 'Hiddenmanager' has a default value or Valid value that depends on the parameter 'HiddenManager'. Forward dependencies are not valid."

    Manager is a parameter. It is followed step 3.

    Hiddenmanager is a parameter. It is followed step 1.

    Please Help me.

    Thanks,

    Aasai

    Thursday, August 02, 2012 2:29 PM
  • I have tried it whole day today.. doesnt work !!!!!!!!!!!!!!!!!!!

    I managed to get somehow first row and that too only if I keep original paramter that is bound to dataset as Visible. Upon selecting this paramter report does postback and brings first value only.

    BTW Split Join function doesnt work. It just gives error message that can not covert from string to string array....

    It will be great if you provide tested solution...

    I checked one from the SQL server FAQs PDF. Even that doesnt work

    Tuesday, August 21, 2012 11:11 PM
  • Jin:

    I tried the same way as in your walkthrough, but I have to manually select the DummyParameter (how can I get it preselected), though I have the default value defined. And because of this I cannot have the DummyParameter hidden as it will grey out the other parameter.

    Thanks

    Monday, February 11, 2013 8:14 PM
  • Hi,

    I used the steps and got an error that says:

    • The ValidValue expression for the report parameter ‘ReportParameter1’ contains an error: Unable to cast object of type 'System.String' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameter'. (rsRuntimeErrorInExpression)

    Regards, Vikram


    • Edited by Vicky-SP Thursday, March 14, 2013 10:03 AM
    Thursday, March 14, 2013 8:56 AM
  • Hi,

    Iam getting the same error, have you found a work around for this error???


    Regards, Vikram

    Thursday, March 14, 2013 10:44 AM
  • Hello Christian,

    Have you resolved the issue? I am facing with same issue too. 

    Thanks.

    Wednesday, March 20, 2013 2:09 PM
  • How do you "create a function" from within Report Builder 3.0 - newbie
    Monday, April 29, 2013 8:55 PM
  • If someone still has the problem with the dropdown being disabled:

    I've solved it by simply checking the option "Allow blank value" in the Report Parameter properties (I checked it only for the "hidden parameter"). Hope this helps other people too.

    Wednesday, January 15, 2014 12:25 PM
  • Hi Jin,

      This is karthik, thanks for the post. I followed same steps to create distinct values in ssrs report using sharepoint list as a datasource. But i'm facing with the below error.

    Error: The value expression for the report parameter 'Parametername' contains an error. [BC30203] Identifier expected.

    Please let me know the solution for this.

    Thanks in advance,

    Best Regards,

    Karthik R

    Saturday, April 26, 2014 11:59 AM
  • Hi Swapnil,

     Can you please provide the steps how you have removed duplicate data for one of the columns.

    Thanks and Regards,

    Karthik R

    Saturday, April 26, 2014 12:06 PM
  • Do you still have the walkthrough available for download? The link is broken. Thanks!

    Shawn

    Friday, September 12, 2014 8:04 PM
  • This looks like a good potential option. I'm sure that the information is technically correct, but it lacks the clarity to let a less-advanced user follow the steps.

    There appear to be missing details as to where to do each of the steps. Each of the steps need a click-by-click to orient less-experienced users to the UI. Details that are obvious to veterans, when omitted, make it impossible for novices to follow. For example, instead of "Create a hidden parameter..." provide the click-by-click to do that.

    Please also use consistent terminology throughout. For example, are "main dataset" and "maindata" the same thing? When you say, "Note: Please create another dataset that is same of the main dataset. This dataset is used for the parameter," does "This dataset" refer to the "another" or the "main"? It appears to be the main, but is that what you're intending?

    Tuesday, November 03, 2015 7:38 PM
  • Where/how do you add the function?

    David Narramore

    Wednesday, January 25, 2017 7:05 PM
  • Wednesday, January 25, 2017 7:16 PM
  • Do not forget to set the default value of the hidden parameter. I fixed my disabled drop down issue by adding the default value.
    Wednesday, March 01, 2017 1:31 AM