Sep
26
2008

SharePoint Data View / Data Form Web Part - Group items by month on DateTime field

This is one of the most popular requirements I always get from clients. Especially when they have large collection of documents and want to give their user an easy way to browse document items in Data Form Web Part (DFWP).

For example, you have a list of documents displayed in DFWP and each document has "Published Date" field. How can you group DFWP items by its "Published Date" month value?

Goal

James Tsai .Net SharePoint Blog - DFWP Group By Month Final

Solution

Luckily, all you need to do is to change a few lines in XSL that renders your DFWP. Here is step by step of how to do it.

1. Add "Group By" to Data Form Web Part.

Here is what your original DFWP should look like without any "Group by" on field

James Tsai .Net SharePoint Blog - DFWP default view without Group by field

In SharePoint Designer (SPD), open DFWP's Common Data View Tasks and select "Sort and Group"

James Tsai .Net SharePoint Blog - DFWP common data view tasks

Select DateTime field you want to Group by, In this example it is "Published Date". "Show group header" is also selected here because this way you can see what values are used to group items

James Tsai .Net SharePoint Blog - DFWP sort and group options

After above steps, you can see the DFWP is correctly grouped on "Published Date" field. But it treats each DateTime value as a different group value.

By default, DFWP group DateTime field based on their actual Date (YYYY-MM-DD) value. Not just year and month (YYYY-MM).

James Tsai .Net SharePoint Blog - DFWP Group By DateTime Field Default

Since requirement here is to group items with same month (item with "Publsihed Date" 08/16/2008 and 08/03/2008 in this example) in same group. Further steps are needed.

2. Modify XSL

Inside the XSL that renders your DFWP, search for "dvt_groupfield". If you use "Search All" within XSL, <xsl:when test="not ($dvt_groupfield)"> is the line you want.

James Tsai .Net SharePoint Blog - DFWP XSL Search For DVT_GroupField

And you will see this section,


<xsl:when test="not ($dvt_groupfield)">
        <xsl:value-of select="ddwrt:NameChanged(string(@PublishedDate), 0)" />
</xsl:when>

Change this to,


<xsl:when test="not ($dvt_groupfield)">
        <xsl:value-of select="ddwrt:NameChanged(string(substring(@PublishedDate,1,7)), 0)" />
</xsl:when>

Above change is critical, that's where you specify how you want to group items in DFWP.

The original @PublishedDate value is presented in format "YYYY-MM-DDTHH:MM:SSZ". substring(@PusblishedDate,1,7) gives us "YYYY-MM" which is what we want DFWP to group by. Note: In XSL, index starts from 1 not 0.

You will see this after above changes

James Tsai .Net SharePoint Blog - DFWP XSL After Change Group By Value

Items are now group correctly, but group header still displaying incorrect text. Because In DFWP XSL, group value and header value are generated from different template. You have changed first one (in above step), and now last step is to change header value.

Just scroll down from where you changed group value in XSL a bit, and you should see this line


<xsl:when test="not (@PublishedDate) and (@PublishedDate) != false()"><xsl:value-of select="' '" /></xsl:when>
        <xsl:otherwise>
                <xsl:value-of select="ddwrt:GenDisplayName(string(@PublishedDate))" />

Change ddwrt:GenDisplayName(string(@PublishedDate)) to substring(@PublishedDate,1,7). Like following,


<xsl:when test="not (@PublishedDate) and (@PublishedDate) != false()"><xsl:value-of select="' '" /></xsl:when>
        <xsl:otherwise>
                <xsl:value-of select="substring(@PublishedDate,1,7)" />

And you should get this as result

James Tsai .Net SharePoint Blog - DFWP XSL After Change Heading Values

You can also change Group Heading to display in format YYYY-MMM (like the one in first screen shot) , or anything you like by changing above XSL.

Aug
12
2008

How to query cross-site lists in DataFormWebPart - Part 3. Filtering on column headers problem

Here are the links to previous two parts of the series

Part 1. Build your own data source for Data Form Web Part

Part 2. Use XSLT generated from SharePoint Designer to display data 

If you build a Data Form Web Part with customized data source (i.e. data source that does cross-sites querying), you've probably noticed the filtering on column headers does not work. In this part of customize Data Form Web Part series, you will see how this problem can be fixed by extend your custom Data Form Web Part code a bit more.

Before click on "Form Name" column header, there are two items displayed under it.

James Tsai .Net Blog - SharePoint C# ASP.NET VSTO -  Data Form Web Part with cross-sites query data source displayed

After click on "Form Name" column header, no available filter data returned.

James Tsai .Net Blog - SharePoint C# ASP.NET VSTO -  Cross-sites query data source with filtering on column headers problem

The Problem

Before we going into how to fix it. You must first understand what is causing this problem. And the answer simple. Because with the customized data source you just created, the original Data From Web Part methods that used to handle filtering are no longer to be able to perform filtering and render filter value correctly.

You will see how it works behind the scene from the following three methods. These methods are called after you clicked on any filterable column header.


/*Microsoft.SharePoint.WebPartPages.DataFormWebPart*/
protected virtual void RaiseCallBackEvent(string eventArgument){};
protected virtual string GetCallbackResult(){};
private string DoCallBackFilters(string filterStr){};

 

RaiseCallBackEvent

This method assigns event argument to the web part (Data Form Web Part in this example) after callback event has been raised.

Event argument is a string variable represent in following format: (i.e. clicked on "Title" column header)

"__filter={Form Name @FormName x:string;1033 g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb}"

As you can see, it contains column name and web part ClientID - g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb.

 

GetCallbackResult

After RaiseCallBackEvent, this method gets called. It first check if raised callback event is for filtering event on column headers. If so, it strips out the filtering string

"Form Name @FormName x:string;1033 g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb"

And pass it to DoCallBackFilters as an input argument.

 

DoCallBackFilters

DoCallBackFIlters prepares callback result that will be used for rendering filtering drop down list on column header. The correct returned string should look like this:

<SELECT>
<OPTION href="__doPostBack('g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb','NotUTF8;__filter={@FormName=##dvt_all##}')"></OPTION>
<OPTION href="__doPostBack('g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb','NotUTF8;__filter={@FormName=Temp page}')">Temp page</OPTION>
<OPTION href="__doPostBack('g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb','NotUTF8;__filter={@FormName=Test Page}')">Test Page</OPTION>
</SELECT>

Note! This is where you get empty result when you clicked on column header. Because this method cannot process your customized cross-sites query data source, it returned empty string instead to GetCallbackResult.
James Tsai .Net Blog - SharePoint C# ASP.NET VSTO -  GetCallbackResult method failed on return correct data

 

Solution

DoCallBackFilters is a private method and there is no way to extend it to make it work the way you wanted. The only option here is to override GetCallbackResult method and make it to call new DoCallbackFilters replacement method. (CustomizedDoCallBackFIlters in this example)

/*Original implementation of GetCallbackResult*/

public virtual string GetCallbackResult()
{
    if (string.Compare(this.eventArgument, 0, "__filter", 0, 8, false, CultureInfo.InvariantCulture) != 0)
    {
        return string.Empty;
    }
    string filterStr = this.eventArgument.Substring(10, this.eventArgument.Length - 11);
    return this.DoCallBackFilters(filterStr);
}

Override above method with following implementation.


/*Override implementation of GetCallbackResult*/
public virtual string GetCallbackResult()
{
    //This is where it make sure event raised from filtering on column headers
    if (string.Compare(this.eventArgument, 0, "__filter", 0, 8, false, CultureInfo.InvariantCulture) != 0)
    {
        return string.Empty;
    }
    string filterStr = this.eventArgument.Substring(10, this.eventArgument.Length - 11);
    return this.CustomizedDoCallBackFilters(filterStr);
}
private string CustomizedDoCallBackFilters(string filterStr)
{
    string yourDataAsString;
    //Build your customized filtering data here. To return data in format described above
    return yourDataAsString;
}

Inside CustomizedDoCallBackFilters method you can build result data with any logic you like. As long as it is in correct format. I suggest you to follow the same logic of how you've built your data source. To make sure data displayed in column headers always consistent with actual displayed items in Data From Web Part.

James Tsai .Net Blog - SharePoint C# ASP.NET VSTO -  Correct data returned from GetCallbackResult method 

More Details

I will explain what should your final result data look like. As you can see in above, it follows this format:


<SELECT>

<OPTION href="__doPostBack('g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb','NotUTF8;__filter={@FormName=##dvt_all##}')"></OPTION>

<OPTION href="__doPostBack('g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb','NotUTF8;__filter={@FormName=Temp page}')">Temp page</OPTION>

<OPTION href="__doPostBack('g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb','NotUTF8;__filter={@FormName=Test Page}')">Test Page</OPTION>

</SELECT>

Each <OPTION/> element inside <SELECT/> has only one attribute "herf". Except first <OPTION/> element served special purpose and I will explain it later.

But first let's see the break down to the "herf" attribute value

James Tsai .Net Blog - SharePoint C# ASP.NET VSTO - Correct result format explained

Only difference in each line are the values used to display on the UI drop down list and used for filtering PostBack result.

The first <OPTION/> is used to indicate whether filtering has been done on current column. To toggle the flag you just need to assign 1 to this <OPTION/> element


<OPTION href="__doPostBack('g_548009f0_beeb_4e3c_a4a6_71fc338cc8cb','NotUTF8;__filter={@FormName=##dvt_all##}')">1</OPTION>

James Tsai .Net Blog - SharePoint C# ASP.NET VSTO - How to use first element in returned result to toggle filtering on column header flag

 

 

That's all. It is pretty much how you fix filtering on column header problem. Just remember to return your final result data in GetCallbackResult().

In summary.

1. Get Callback argument with all the necessary data (field name and control client id).

2. Use above input argument to build result data using any code logic you like.

3. return data in GetCallbackResult() method.

Hope this post helps you to get what you want.

 

James

Apr
11
2008

How to query cross-site lists in DataFormWebPart - Part 2. Use XSLT generated from SharePoint Designer to display data

Part 1. Build your own data source for Data Form Web Part

Part 2. Use XSLT generated from SharePoint Designer to display data  

In last post I have described how to build a data source and use it in custom DataFormWebPart to query cross-site lists.

Now, I am going to explain how you can display result data easily by using XSLT from SharePoint Designer.

Like I have mentioned in previous post, you should build data result in specific format. The format respects the XSLT that will be generated by SharePoint Designer. The format looks like this


<dsQueryResponse>

    <Rows>

        <Row Attribute1="" Attribute2=""  Attribute3=""/>

        <Row Attribute1="" Attribute2=""  Attribute3=""/>

    </Rows>

</dsQueryResponse>

Atrribute 1,2,3 can be field name.  

1. Create a new page with DataFormWebPart

Once you have data present in above format. You can then go into SharePoint Designer and open the library/list that your cross-site query will be queried on. And create a new temporary .aspx page in this library.

 NewAspxPage_thumb  

In our case, this page can be created in any sub-site's page library.

cross_list_structure_thumb1  

After you created a new page, insert a data view (DataFormWebPart)  control to this page.

InsertDFWP_thumb  

And select a data source from the data source panel for this control

 ClickForDataSource_thumb1

DataSourceLibrary_thumb1  

In this example we select "Pages" library as our data source. Because we want to use the site columns for the content type used in this page library as display fields. (Same fields we queried in result data in Part 1.)

SelectedLibrarySource_thumb1  

Surely You can create this temporary .aspx page in any place you like. But create it under right site avoid extra works for set up data source library manually. To get site columns you want.

ConnectToDiffLibrary_thumb

 

2. Customise the presentation of DataFormWebPart

Now, you can select all the fields you wanted to display in your cross-site lists DataFormWebPart. And insert them to the control.

InsertSelectedFields_thumb1 

Sample data view will be displayed on the page for you to preview the result.

 

3. Copy XSLT

If everything looks good to you, right click on the DataFormWebPart on the page and select "Web Part Properties..".

In XSL Editor box, copy the XSLT and save it to separate file for your custom cross-list DataFormWebPart to use.  

WebPartProperties_thumb2  


//XSLT saved in CrossList.xsl and use it via code.
//XslLink can also be set from Web Part Properties UI
CustomDataFormWebPart dfwp = new CustomDataFormWebPart();
dfwp.XslLink = "/Style Library/XSL Style Sheets/CrossList.xslt"  

Now, You should be able to display data without any changes to this XSLT. Because display fields matches attributes of our result XML data.

At this stage you have both custom cross-site DataFormWebPart control and XSLT presentation file.

Note

If you changed the display layout in Step 2 to enable Sorting and Filtering on column headers. You may find filtering does not work properly. It returns no data for you to perform filtering when you click on column headers.

 EnableFIlteringSorting_thumb1 

NoFilteringData_thumb1

It is because the default implementation of the method (the one return filtering data)  in DataFormWebPart class does not support our custom data source.

 

Coming up..

In Part 3. I will post about how to get filtering to work on column headers!

Mar
21
2008

How to query cross-site lists in DataFormWebPart - Part 1. Build your own data source for DataFormWebPart

Problem - We have a site which contains many sub-sites. On this site we want to use DataFormWebPart to display all the pages in Pages library of all sub-sites.

James Tsai Blog - C# ASP.Net SharePoint VST. How to query cross-site lists DataFormWebPart Pages Library

Possible solutions:

1. We could use OOTB DataFormWebPart with SharePoint Designer to configure SPDataSource of the web part to do cross-site query. On SharePoint Designer Team Blog they have detailed article about how to do this. But I couldn't get this working after followed the instructions described in the article. And it isn't easy to find out which part of my configuration was causing the error.

2. Create a custom DataFormWebPart (inherit from DataFormWebPart class) with SPSiteDataQuery to query the data we want. And use XmlDataSource as data source for our custom web part.

The steps are:

a. Build your query

We created a SPSiteDataQuery with following configurations

SPSiteDataQuery qry = new SPSiteDataQuery();
qry.Lists = "<Lists ServerTemplate='850' Hidden='TRUE' />"; //Pages library has template Id 850
qry.Webs = "<Webs Scope='Recursive' />"; //Set scope to Recursive. To query current site and all sub-sites
qry.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='Comments' /><FieldRef Name=Type/>"; //The fields we want to display
qry.RowLimit = 1000; //number limit of results
qry.Query = "<Where><Eq><FieldRef Name='ContentType' /><Value Type='Text'>My Content Type</Value></Eq></Where><OrderBy><FieldRef Name='Title' Ascending='FALSE' /></OrderBy>"; //query logic
DataTable tbl = web.GetSiteData(qry); // query SPWeb and store result as DataTable

Above code returns all pages created with "My Content Type" content type in Pages Library of parent site and all sub-sites.

b. Save result data collection to XML format

Next step is to transform result in DataTable into XML. We respect default XML format expected by DataFormWebPart to make sure minimal changes needed for XSL in later stage.

XmlDocument doc = new XmlDocument();
XmlNode queryResponse = doc.AppendChild(doc.CreateElement("dsQueryResponse"));
XmlNode root = queryResponse.AppendChild(doc.CreateElement("Rows"));
foreach (DataRow row in tbl.Rows)
{
         XmlElement rowNode = doc.CreateElement("Row");
         foreach (DataColumn col in row.Table.Columns)
         {
               string val = row[col].ToString();
               XmlAttribute att = doc.CreateAttribute(col.ColumnName);
               att.Value = val;
               rowNode.Attributes.Append(att);
          }
          root.AppendChild(rowNode);
}

Sample result of above code looks like this


<dsQueryResponse>

        <Rows>

                <Row ListId="7DC60945-8C38-47D9-BD82-422D2B6D873C" WebId="A696D3FB-998F-4C7E-BDF9-3EECA4BE8A34" ID="2" Title="General Information" Comments="subsite 2 page 1" Type="Info" />

                <Row ListId="389949C9-D040-4DD4-A18F-75F4401F580A" WebId="E00F6CCB-9893-4155-A811-5D4D6DED5054" ID="4" Title="Important Information" Comments="subsite 1 page 2" Type="" />

                <Row ListId="389949C9-D040-4DD4-A18F-75F4401F580A" WebId="E00F6CCB-9893-4155-A811-5D4D6DED5054" ID="2" Title="Important Policy" Comments="subsite 1 page 1" Type="" />

       </Rows>

</dsQueryResponse>

c. Create XmlDataSource (The data source for custom DataFormWebPart)

The last step at this stage is to create a XmlDataSource and bind XML data we created in previous step with this XmlDataSource.

XmlDataSource source = new XmlDataSource();
source.Data = doc.InnerXml;

All above code are placed inside the overrided DataBind() method of custom DataFormWebPart. DataBind() method will be called when SharePoint try to render custom DataFormWebPart.

public class ExtendedDataFormWebPart : DataFormWebPart
{

    public override void DataBind()
    {

          /* all code described in above steps */

          this.DataSource = source;

          base.DataBind();

    }

}

That's all for Part 1 of How to query cross-site lists in DataFormWebPart. In Part II, I will describe how to display our query result on page using XSL.