locked
Selecting distinct values of Sharepoint List column in Data View Webpart RRS feed

  • Question

  • Hi,

     

    I am trying to create Data View Webpart in MOSS 2007 using Sharepoint Designer for a Sharepoint List Data.

    From the list Data I have to show the Data in such a way that for a particular column I have to fech the unique values in the list, and count for the items for each of that column value.

     

    How to select Distinct values of a list column in DataView webpart.

     

    Thanks in Advance.

    Wednesday, May 21, 2008 12:47 PM

Answers

All replies

  • Did you ever find an answer to this question?  I'm trying to do something similar.

     

    Tuesday, June 3, 2008 1:04 PM
  • Hello there,

    I have been hunting about for a solutino to the duplicate rows returned in and XSLT dataview withing SPD. Looks like way back in FP2003 a few folks reported this issue to the WSS team and they came back stating that it was an issue. However, nobody seems to have a way of dealing with the issue even in SPD. Anyone else out there with a solution that relates to data brought back from a SP list and not another data provide where we could simply set up a query to return distinct rows.

    Thanks
    Faheem

    mfn
    Saturday, August 2, 2008 9:46 PM
  • Hi,

    try to use a XPATH (XSLT filter) expression as a filter:

    [not(@Field = preceding-sibling::Row/@Field)]

    So the current value in "Field" should not be equal to the previous value.
    Found it at: http://blogs.msdn.com/timpash/archive/2005/11/23/XPATH-Filtering-Distinct-Values.aspx

    Thursday, September 4, 2008 10:59 AM
  • Hi

    The post was very useful for me,
    but i have an query using this filter.
    i have date filed formated like mm/dd/yyyy and i need to filter this date column(based on month and year),
    how can i filter that column.
    ex:i have the following date values
    a)11/10/2008
    b)11/22/2008
    c)08/08/2008
    d)11/12/2008

    and need to display(output) as like
    1)Nov 2008
    2)Aug 2008

    how to filter this
    pls any one suggest me a gud solutions...


    Thanks in advance
     


    With Love
    Thursday, December 11, 2008 10:04 AM
  • I tried using CAML Query...here using Datatable distinct list items were selected.

    public DataView DistinctProjectItem(string listName,string fieldName)
        {
            siteUrl = ConfigurationManager.AppSettings["siteUrl"].ToString();
            SPSite spSite = new SPSite(siteUrl);
            SPWeb spWeb = spSite.OpenWeb();
            using (spWeb = spSite.OpenWeb())
            {
                SPList MyList = spWeb.Lists["Employee Log"];
                SPListItemCollection spListItem;
                SPQuery spDistinctItem = new SPQuery();
                spDistinctItem.Query = "<OrderBy><FieldRef Name='Projects'/></Order By><FieldRef Name='"+fieldName+"'/>";
                spListItem = MyList.GetItems(spDistinctItem);

                DataTable dtDistinctProject = spListItem.GetDataTable();

                try
                {
                    DataTable dtListItem = dtDistinctProject.DefaultView.ToTable(true, fieldName);//true means selecting distince items
                    DataView dvListItem = new DataView(dtListItem);
                    return(dvListItem);
                }
                catch
                {
                    DataView dvListItemEmpty = null;
                    return (dvListItemEmpty);
                }
            }
        }

    Hope this helps...:)

    • Proposed as answer by Tester Monday, March 16, 2009 7:13 AM
    • Unproposed as answer by Mike Walsh FIN Sunday, March 29, 2009 4:13 PM
    • Proposed as answer by Tester Monday, April 13, 2009 9:50 AM
    • Unproposed as answer by Mike Walsh FIN Tuesday, April 21, 2009 11:40 AM
    • Edited by Tester Friday, April 24, 2009 7:26 AM
    Thursday, March 12, 2009 12:02 PM
  • Deleted
    • Proposed as answer by o365spo Friday, April 24, 2009 7:25 PM
    • Marked as answer by Mike Walsh FIN Thursday, April 30, 2009 7:40 AM
    Thursday, March 12, 2009 4:07 PM
  • Saujanya,

    Is that solution working for you? I don't see where you are grouping distincts.. BTW, I've tried group by in a CAML on my datasource in Sharepoint designer and can't seem to make it work either.

    Thanks.
    Sunday, March 29, 2009 1:42 PM
  • Ya the distinct was working fine for me

    [not(@Field = preceding-sibling::Row/@Field)]
     i used this code in datasource
    ex:
    [not(@Country= preceding-sibling::Row/@Country)]

    With Love
    Tuesday, April 21, 2009 9:27 AM
  • >I have this requirement, where i have to display distinct list items from sharepoint list column and bind it with repeater in a custom page. Here is the code
    > which i have written:


    STOP PROPOSING THIS AS AN ANSWER

    Any post that starts with "I have this requirement" can not possibly be an answer.

    I have already unmarked it as a Propose as Answer once and now you are forcing me to do it for the second time.

    If you want to get on my black list, repeatedly proposing your own posts as answers (and especially if they are not answers) is the way to do it.


    (Moderator)


    Later: the offending post was later deleted by the poster.

    WSS FAQ sites: WSS 2.0: http://wssv2faq.mindsharp.com WSS 3.0 and MOSS 2007: http://wssv3faq.mindsharp.com
    Total list of WSS 3.0 and MOSS 2007 Books (including foreign language titles) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx
    Tuesday, April 21, 2009 11:42 AM
  • See my blog post for a simple answer for this in your DVWP using ddwrt:NameChanged.
    http://mdasblog.wordpress.com/2007/10/23/eliminating-duplicates-in-data-view-web-parts/

    No managed code required!
    Marc
    Tuesday, April 21, 2009 9:14 PM
  • marc i have tried implementing your solution but you need to elaborate more on your blog and maybe put up a sample
    Cheers
    Thursday, April 30, 2009 7:11 AM
  • Marc,

    Please do not propose *your own answers*. Wait for someone else to do so.

    Propose by all means good answers from *other people*.


    (Proposing answers is a function that is there to help the Moderators. Proposing your own answers doesn't help the Moderators. Establishing a record of proposing other peoples' good answers does.)

    WSS FAQ sites: WSS 2.0: http://wssv2faq.mindsharp.com WSS 3.0 and MOSS 2007: http://wssv3faq.mindsharp.com
    Total list of WSS 3.0 and MOSS 2007 Books (including foreign language titles) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx
    Thursday, April 30, 2009 7:42 AM
  • Patrick:

    I added a new post explaining the initial steps based on your comment on the existing post.
    http://mdasblog.wordpress.com/2009/04/30/adding-a-dropdown-select-to-a-dvwp/
    Hopefully this will give you the extra pieces you need to be successful with this!

    M.

    p.s. Mike: Sorry about proposing my own answer.  Point taken!
    Marc - Sympraxis Consulting LLC - Marc's Blog
    • Proposed as answer by Bob Manjoney Monday, May 4, 2009 9:54 PM
    Friday, May 1, 2009 1:46 AM
  • Thanks Marc..
    I'm actually almost there .The only problrm i have now is with formatting the date.
    If i don't format the date like this :2009-04-12 i don't get any result back
    But the issue with the dropdown is that i can't sort in properly and the uniques isn't working properly too.

    What datetime format can you propose.
    I'm using this calculated field
    =TEXT([Date Worked],"YYYY-MM-DD") as my filter..

    Thanks in advance
    Friday, May 1, 2009 8:03 AM
  • I'd use that date format.  (Regardless of locale preferences, that's the format that sorts chronologically correctly.) You'll want to use <xsl:sort> to sort your items by whatever value you choose so that the ddwrt:NameChanged function works the way you want it to.
    Marc - Sympraxis Consulting LLC - Marc's Blog
    Friday, May 1, 2009 12:58 PM
  • Hi Marc,
     this is my snippet below:
    As you can see i would probably need to format the date at ddrwt:Namechanged and also on the <xsl:if test

    <xsl:template name="dvt_1.rowview">  
          <xsl:variable name="NewGroup" select="ddwrt:NameChanged(string(@Date_x0020_Worked), 0)" />  
          <xsl:if test="string-length($NewGroup)">  
              <option>
          <xsl:if test="$fDate = @Date_x0020_Worked">
            <xsl:attribute name="selected">
              <xsl:value-of select="selected" />
            </xsl:attribute>
          </xsl:if>
          <xsl:attribute name="value">
            <xsl:value-of select="ddwrt:FormatDateTime(string(@Date_x0020_Worked),1033,'yyyy-MM-dd')" />
          </xsl:attribute>
          <xsl:value-of select="ddwrt:FormatDateTime(string(@Date_x0020_Worked),1033,'yyyy-MM-dd')" />
          <!--
           <xsl:value-of select="ddwrt:FormatDate(string(@Date_x0020_Worked),1033,1)" />
           -->
        </option>     
        </xsl:if>  
       </xsl:template>

    Almost there. Let me know if you have any ideas
    Thanks
    Monday, May 4, 2009 12:44 AM
  • Patrick:

    This looks like it ought to be working.  What problem(s) are you having?  I assume that you've got the xsl:sort on the @Date_x0020_Worked column in the dvt_1.body template?

    M.
    Marc - Sympraxis Consulting LLC - Marc's Blog
    Monday, May 4, 2009 12:52 AM
  • Thanks Marc...
    I'm still getting duplicated in the dropdown guess 'm still missing something...
    Where would i include the @Date_x0020_Worked column in the dvt_1.body template?
    My full snippet is below..
    Thanks


    <xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
     <xsl:output method="html" indent="no"/>
     <xsl:decimal-format NaN=""/>
      <xsl:param name="dvt_apos">&apos;</xsl:param>
     <xsl:param name="ListID">1B2D47AC-653C-4125-91AA-1D20309DDC7F</xsl:param>
     <xsl:param name="fDate" />
     <xsl:variable name="dvt_1_automode">0</xsl:variable>
      
      <xsl:template match="/" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:SharePoint="Microsoft.SharePoint.WebControls">
       <xsl:call-template name="dvt_1"/>
     </xsl:template>
      <xsl:template name="dvt_1">
      
      <!--original
     <xsl:variable name="dvt_StyleName">Drpdwn</xsl:variable>
       <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row" />
       <xsl:variable name="dvt_RowCount" select="count($Rows)" />
       <xsl:variable name="IsEmpty" select="$dvt_RowCount = 0" />
       <select name="ID" size="1">
       <option selected="true" value="0">Choose One...</option>
       <xsl:call-template name="dvt_1.body">
        <xsl:with-param name="Rows" select="$Rows" />
       </xsl:call-template>
       </select>
      -->
      <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row" />

      <select size="1" onchange="HandleFilterSelection('/testnew.aspx', '/testnew.aspx', 'fDate', this.options[selectedIndex].value)">

      <xsl:choose>
        <xsl:when test="not(string-length($fDate))" >
          <option value="0">Choose Dates</option>
        </xsl:when>
        <xsl:otherwise>
          <option value="*">All Dates</option>
        </xsl:otherwise>
      </xsl:choose>
      <xsl:call-template name="dvt_1.body">
        <xsl:with-param name="Rows" select="$Rows" />
      </xsl:call-template>
      </select>
      
      
      </xsl:template>
      
      
      
      <xsl:template name="dvt_1.body">
       <xsl:param name="Rows" />
       <xsl:for-each select="$Rows">
        <xsl:call-template name="dvt_1.rowview" />
       </xsl:for-each>
      </xsl:template>
      
      <!--original
      <xsl:template name="dvt_1.rowview">
       <option>
       <xsl:value-of select="ddwrt:FormatDate(string(@Date_x0020_Worked),1033, 5)" />
       </option>
      </xsl:template>
      -->


     <xsl:template name="dvt_1.rowview"> 
          <xsl:variable name="NewGroup" select="ddwrt:NameChanged(string(@Date_x0020_Worked), 0)" /> 
          <xsl:if test="string-length($NewGroup)"> 
              <option>
          <xsl:if test="$fDate = ddwrt:FormatDateTime(string(@Date_x0020_Worked),1033,'yyyy-MM-dd')">
            <xsl:attribute name="selected">
              <xsl:value-of select="selected" />
            </xsl:attribute>
          </xsl:if>
          <xsl:attribute name="value">
            <xsl:value-of select="ddwrt:FormatDateTime(string(@Date_x0020_Worked),1033,'yyyy-MM-dd')" />
          </xsl:attribute>
          <xsl:value-of select="ddwrt:FormatDateTime(string(@Date_x0020_Worked),1033,'yyyy-MM-dd')" />
          <!--
           <xsl:value-of select="ddwrt:FormatDate(string(@Date_x0020_Worked),1033,1)" />
           -->
        </option>    
        </xsl:if> 
       </xsl:template>
      

      
      </xsl:stylesheet>

    Monday, May 4, 2009 1:50 AM
  • Patrick:

    If you don't sort the rows by the @Date_x0020_Worked column, then you will be getting those values in the order that the items were added to the list (in ID order).  This means that you have no idea what order the dates will be in, and therefore, it's likely that you'll get results from the ddwrt:NameChanged function that aren't useful.

    If you add the xsl:sort line which is bold below, then you'll be sure that the items are in the right order for the ddwrt:NameChange function to help you.

    <xsl:template name="dvt_1.body">
       <xsl:param name="Rows" />
       <xsl:for-each select="$Rows">
        <xsl:sort select="@Date_x0020_Worked" order="ascending"/>
        <xsl:call-template name="dvt_1.rowview" />
       </xsl:for-each>
      </xsl:template>

    M.
    Marc - Sympraxis Consulting LLC - Marc's Blog
    • Proposed as answer by Patrick.I Monday, May 4, 2009 4:33 AM
    Monday, May 4, 2009 3:22 AM
  • Marc thanks for the solution.Did work like a charm.
    Hope MS will remember this in their future release..
    Monday, May 4, 2009 5:22 AM
  • Glad it worked.  This is all custom, so I don't think that it's up to Microsoft!

    M.
    Marc - Sympraxis Consulting LLC - Marc's Blog
    Monday, May 4, 2009 12:40 PM
  • Create a Column in your list.  It needs to be Calculated (Calculation based on other columns) and in the formula put:

    =TEXT([Delivery Time and Date],"MMM-YYYY")

    Replace [Delivery Time and Date] with the name of the date/time or created field already in the list.


    Cheers

    Niall
    Friday, August 14, 2009 1:47 PM
  • I am using Data View Web Part to show data from 22 Doc Libraries in my page. I have enabled the Filter & Sort features of this Web Part. However the 7 Drop Down Filters are having duplicate values. 

    Please find below the code snippet of the page from SPD, 

         <xsl:for-each select="msxsl:node-set($dvt_Rows)/*[not(@*[name()=$dvt_FieldNameNoAtSign]=preceding-sibling::*[1]/@*[name()=$dvt_FieldNameNoAtSign])]/@*[name()=$dvt_FieldNameNoAtSign]">
           <xsl:sort data-type="{$sorttype}" />



    When I remove [1] from it
         <xsl:for-each select="msxsl:node-set($dvt_Rows)/*[not(@*[name()=$dvt_FieldNameNoAtSign]=preceding-sibling::*/@*[name()=$dvt_FieldNameNoAtSign])]/@*[name()=$dvt_FieldNameNoAtSign]">
           <xsl:sort data-type="{$sorttype}" />

    My page is not rendered. And I still have the dupliactes in the Drop Downs.

    Please let me know how to get rid of the Duplicate values.

    Thanks,
    Salil
    Monday, September 28, 2009 4:11 PM
  • sm100,
          Have you had a look at this one:
    http://mdasblog.wordpress.com/2009/04/30/adding-a-dropdown-select-to-a-dvwp/
    Patrick
    Monday, September 28, 2009 11:46 PM