The ListViewByQuery Control
Introduction
The ListViewByQuery control renders a list view on an application page or within a web part based on a specified query.
When the control is displayed users can sort on the columns of the list view and apply filters. But all these events need to be implemented by you otherwise the rendering will fail with an ugly error.
Syntax
The ListViewByQuery control is part of the Microsoft.SharePoint.dll and is located in the Microsoft.SharePoint.WebControls namespace. You can use this control declaratively when developing application pages or control templates:
<spuc:ListViewByQuery ID="CustomersListViewByQuery" runat="server" Width="700px" />
But there is a caveat: the properties List and Query only accept objects and cannot be set declaratively, only in code.
Don’t forget to add a directive at the top of the application page or control template:
<%@ Register TagPrefix="spuc" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
If your are developing a web part you have to create the ListViewByQuery control completely in code:
private ListViewByQuery CustomersListViewByQuery;
private void EnsureChildControls()
{
CustomersListViewByQuery = new ListViewByQuery();
// The rest of the code follows here....
}
There are two mandatory properties that need to be set: the List property which is of type SPList and the Query property which is of type SPQuery. The OnLoad event handler or the EnsureChildControls method then contains following code:
list = SPContext.Current.Web.Lists["Customers"];
CustomersListViewByQuery.List = list;
string query = null;
if (!Page.IsPostBack)
{
query = "<OrderBy><FieldRef Name='Title' Ascending='true' /></OrderBy>";
}
else
{
// apply filtering and/or sorting
}
SPQuery qry = new SPQuery(list.DefaultView);
qry.Query = query;
CustomersListViewByQuery.Query = qry;
The query object must be instantiated based on an existing view of the list otherwise the ListViewByQuery control will not work. The data displayed by the control is determined by the CAML query itself.
You can limit the number of rows returned by setting the RowLimit property of the SPQuery object:
qry.RowLimit = 50;
This was the easy part but it becomes more complex when you need to implement sorting and filtering. When the user chooses to sort or to filter, the page is posted back with some extra information stored in the HttpRequest object.
Sorting
The user can decide to sort on a certain column by clicking the column header and choosing A on Top or Z on Top.
This instructs the page to post back, and the selected column and sort order is passed to the server stored into the Context.Request["SortField"] and the Context.Request["SortDir"]. Following code sample calls the BuildSortOrder method and passes both constructs the <OrderBy> clause based on both request values.
if (!Page.IsPostBack)
{
query = "<Query><OrderBy><FieldRef Name='Title' Ascending='true' /></OrderBy></Query>";
}
else
{
// apply filtering and/or sorting
if (this.Context.Request != null && this.Context.Request["SortField"] != null)
{
string sortorder = BuildSortOrder(this.Context.Request["SortField"],
(this.Context.Request["SortDir"] == "Asc" ? "true" : "false"));
if (string.IsNullOrEmpty(query))
query = sortorder;
else
query += sortorder;
}
}
Following code sample constructs the <OrderBy> clause based on both request values:
private string BuildSortOrder(string fieldName, string sortOrder)
{
string query = null;
if (!string.IsNullOrEmpty(fieldName))
{
query = string.Format("<OrderBy><FieldRef Name='{0}' Ascending='{1}' /></OrderBy>", fieldName, sortOrder);
}
return query;
}
Filtering
The user can also filter the data in the ListViewByQuery control. When the different values in one of the columns is not a large set of data, the user can select from a list of distinct values.
When the page is posting back after the user action, the selected field is stored in the Context.Request["FilterField1"] and the selected value is stored in the Context.Request["FilterValue1"]. If more than 1 filter criteria is specified by the user (by selecting a value of a second column) key/value pairs are added with FilterField2 and FilterValue2 as key, and so on.
Constructing the Where clause of a CAML query is not that easy. I give you the code sample to give you an idea on how you can proceed:
private string BuildFilter()
{
string query = "{0}";
bool isFound = true;
int counter = 1;
while (isFound)
{
string filterfield = "FilterField" + counter.ToString();
string filtervalue = "FilterValue" + counter.ToString();
if (this.Context.Request[filterfield] != null && this.Context.Request[filtervalue] != null)
{
// Field type must be treated differently in case of other data type
if (counter > 1)
query = "<And>" + query + "{0}</And>";
query = string.Format(query, string.Format("<Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq>",
this.Context.Request[filterfield], this.Context.Request[filtervalue]));
counter++;
}
else
{
isFound = false;
}
}
if (!string.IsNullOrEmpty(query))
query = "<Where>" + query + "</Where>";
return query;
}
As one of my readers pointed out, this code snippet only works for text fields. If you want to filter on other types of fields, like number fields, choice fields or lookups, you will have to change the Type attribute from the Value element into the correct type. To get hold on the field type, you will have to retrieve the field from the list.
SPField field = list.Fields[filterfield];
And change the query statement accordingly:
query = string.Format(query, string.Format("<Eq><FieldRef Name='{0}' /><Value Type='{1}'>{2}</Value></Eq>",
this.Context.Request[filterfield], field.TypeAsString, this.Context.Request[filtervalue]));
Grouping
With the ListViewByQuery control you can also build a view where the data is grouped. In that case you have to use the CAML GroupBy element:
query = "<GroupBy Collapse='FALSE'><FieldRef Name='CountryRegionName' /><FieldRef Name='City' /></GroupBy>";
This sample code groups the data by the CountryRegionName and then by City. You can specify more than one group by criterium but the choice of showing the groups collapsed or expanded must be set for all groups by using the Collapse attribute of the GroupBy element.
Use the + and – signs to expand and collapse the different groups.
If you decide to show the groups expanded, you set the Collapse attribute to TRUE and you get the following view:
But then there is a small problem: you can expand all groups, except the latest one: it shows a Loading label but nevers shows the data.
When taking a look at the rendered HTML you can see that all of the hyperlinks that have to do with the ListViewByQuery contain a call to the ExpCollGroup javascript call, which is a function in one of the javascript files that come with SharePoint.
Thanks to one of my readers, Alex, we can now solve this problem with a small piece of javascript. First set the Collapse attribute to FALSE and place your ListViewByQuery control in a DIV and give this DIV an ID. Immediately after the DIV add the following javascript:
<div id="ViewDiv" class="ms-authoringcontrols" style="width:700px">
<spuc:ListViewByQuery ID="CustomersListViewByQuery" runat="server" Width="700px" />
</div>
<script language="javascript">
ExpLinkFormer = function(divId)
{
this._DivId = divId;
this._init();
}
ExpLinkFormer.prototype = {
_init: function() {
var div = document.getElementById(this._DivId);
var links = div.getElementsByTagName("a");
for (var i = 0; i < links.length; i++) {
if (links[i].href == "javascript:" && links[i].onclick.toString().indexOf("ExpCollGroup") > -1) {
//alert(links[i]);
links[i].click();
}
}
}
}
var expLnk = new ExpLinkFormer('ViewDiv');
</script>
The ListViewByQuery control loads with all groups expanded but when it is rendered to the page, the click method is executed on all hyperlinks that have a call to ExpCollGroup in their href attribute.
54 Comments »
Leave a Reply Cancel reply
About
I’m Karine Bosch and I’m a SharePoint developer. Since April 2009 I’m rewarded as SharePoint MOSS MVP. For several years I worked as technical assistant for Patrick Tisseghem who unfortunately died on 3th September 2008. After I left U2U, I started working part time for Spikes NV and for Critical Path Training of Ted Pattison and Andrew Connell (the former Ted Pattison Group). As of January 2011 I start working for ING, which is a banking company.
The U2U CAML Query Builder and some other SharePoint features like the U2U List Properties feature and the U2U Site Properties are developed by me and are still downloadable from the U2U site at http://www.u2u.be/res/software.aspx.
Together with Patrick we developed the alfa and beta versions of the Silverlight BluePrint for SharePoint which contains several samples on how to integrate Silverlight with SharePoint. You can download the samples from http://www.codeplex.com/SL4SP.
Also the integration between SharePoint and CRM intrigues me. If you have questions on how to develop in SharePoint using CRM data, you can read my article at http://sharepointmagazine.net/technical/development/developing-a-sharepoint-custom-field-type-for-displaying-crm-40-data.
If you need detailed information about CAML or the SharePoint web services you can read my articles at http://www.U2U.be/res/articles.aspx and of course you can post your questions on this blog.
Also PowerShell (with SharePoint of course) is not unkown to me. You can read several posts about this on my previous blog http://www.U2U.info/blogs/karine.
If you don’t find the answers to your questions on this blog, you can always post your questions to the SharePoint 2007 forum or the SharePoint 2010 forum. You can find me there almost every day.
Karine, thanks!
One question: what about GroupBy?
When you send GroupBy criteria to the CAML query its showing the groups and the number of documents in those groups but when you click on the expansion icon it just sits there and says loading…
Do you know solution?
Thanks for the input. I’ll take a look at GroupBy tomorrow and I’ll let you know in the comments of this page.
Karine
Is there any news? I think to solve GroupBy problem we need to use JS.. like a ListViewWebPart
[...] The ListViewByQuery Control [...]
Pingback by The ListViewByQuery Control « Karine Bosch’s Blog | April 7, 2009 |
thanks
Nice article! I was able to use the sorting and it works absolutely fine! But with filtering I think BuildFilter() method has to be modified
1.filterfield has to be changed to filtervalue (second parameter in the AND condition)
if(this.Context.Request[filterfield] != null && this.Context.Request[filterfield] != null)
has to be modified as
if (this.Context.Request[filterfield] != null && this.Context.Request[filtervalue] != null)
2. I believe has to be changed in the following lines. If the field is of type
‘Choice’ or any other the below code is not working
query = string.Format(query, string.Format(“{1}”,
this.Context.Request[filterfield], this.Context.Request[filtervalue]));
Can you please update the above code to grab the type of the field dynamically?
Thanks,
Susheel
Susheel,
Thanks for your comments! I really appreciate!
I’ll try your tips out as soon as possible, and I’ll modify/complete the post if necesseray.
Have a nice weekend,
Karine
All the code works fine, but it seems that part of the default behavior in standard lists is lost. For instance, when filtering in a standard list I can see a little funnel symbol on top of the column being filtrated and also the context menu “Clear filter on…” is enabled. This simply doesn’t happen in my code when creating a ListViewByQuery control. I can see the rows filtered, but then I cannot clear the filter, etc. Am I missing something?
Thanks in advance.
Manu Moro
Hi Manu,
You’re right that some standard behavior like “Clear filter on…” and some other things are not working with ListViewByQuery. I was not able yet to figure that out to make it working.
Kind regards,
Karine
Hi Karine
thanks for your great blog post. The issue that “Clear filter on” is disabled still hasn’t vanished, even after insalling the Oct 2010 sharepoint hotfix. Would you have any idea on how to handle this? Haven’t found anything after researching the whole ListViewByQuery class.
I have a question. I have been working with the ListViewByQuery and have gotten it to work with most views. I am having a hard time getting it to work with a Calendar View. It works fine with All Events and Current Events in a calendar list but not the Calendar view. Has anyone else experienced this?
Thanks for the code!
One problem: using the RowLimit property limits the rows but how do I get the paging view to click to the next 50?
And a second problem:
For the filters the list elements are used, not the elements from the query. Is it able to manage that?
Besides I figured out, that deleting the &View={CDF0B740-0464-4489-B744-14024AB968A8} or similar from the URL actvates the Clear Filter context menu, but I didn’t figure out how manipulate the URL by Code.
Kind regards, Eckart
Eckart,
For your comment “For the filters the list elements are used, not the elements from the query. Is it able to manage that?”
I suppose you mean that the DefaultView is always used. You can change this code by passing in the correct view here:
SPQuery qry = new SPQuery(new Guid(list.Views[viewguid]));
qry.Query = query;
CustomersListViewByQuery.Query = qry;
For your other remarks on paging and clearing the filter, I have no answer yet but I’ll post it as soon as I have.
Karine
Hello,
thanks, Eckart, for realizing point 11.
here is the solution, put this code somewhere at the beginning of CreateChildControls method
if (!String.IsNullOrEmpty(this.Page.Request.QueryString["View"]))
{
string strPageQS = “View=” + this.Page.Request.QueryString["View"];
string strPageUrl = this.Page.Request.Url.ToString().Replace(“?” + strPageQS, “?a=a”).Replace(“&” + strPageQS, “”);
this.Page.Response.Redirect(strPageUrl, true);
}
Ondra
PS after several hacks and 3 weeks of HC coding, ListViewByQuery overrides standard list view and has big potential for more features. In MOSS 2010, please, MS, make developer’s life easier!!! ( I doubt it
)
PSS
As for paging, i got it working, but you will have to pay for that, it is like 400 lines of code
)
O. L. / Czech Rep.
Good work Ondra!
Thanks for posting part of the solution!
Karine
I’m trying to get paging to work with ListViewByQuery but I’m not having any luck. It’s just consistently returning back the exact number of items I specify in the RowLimit property of the SPQuery object.
Here’s a quick sample of what I’m doing:
————————————————————
Dim ListView as New ListViewByQuery
Dim List As SPList = web.Lists(“Incident”)
Dim myQuery As SPQuery
Dim CollectionPosition As New SPListItemCollectionPosition(“Paged=TRUE&p_IncidentDate=2007%2d05%2d02&p_ID=136&PageFirstRow=6″)
myQuery = New SPQuery(List.Views(“All Incidents”))
myQuery.RowLimit = 5
myQuery.ListItemCollectionPosition = CollectionPosition
ListView.List = List
ListView.Query = myQuery
————————————————————
I’m using the SPListItemCollectionPosition object. In this scenario I’m showing 5 items per page, the item with p_ID = 136 is the 5th item in the list, I want to show items 6-10 so I specify &PageFirstRow = 6. This doesn’t work for some reason when the results are rendered to the ListViewByQuery object.
However, if I call List.GetItems(myQuery). Items 6-10 in the list are returned in the SPListItemCollection.
What step am I missing to get this to work for ListViewByQuery?
Thanks,
-Somsong
Thank you for doing such excellent work. Your CAML tool was instrumental in my moving ahead on one of my projects. Keep up the great work. God Bless
Hi,
Thanks for this great article. It’s an excellent starting point for ListViewByQuery Control.
Manitra.
Hello Karine,
This article is very good.I need ur help.
Actually i have a custom list definition,in the customlist all item.aspx i need GropBy.In all rows in the allitem.aspx page should be groupby with column names.How can i achive this?
Actually filtering and sorting is a bit easier than all this.
I have been playing with ListViewByQuery and this code trying to get filtering working properly but it was always going bonkers.
To make the filtering/sorting work you don’t actually need any of the code above, you only need to remove the view property from the querystring.
Place this code at the top of CreateChildControls
PropertyInfo isreadonly = typeof(System.Collections.Specialized.NameValueCollection).GetProperty(“IsReadOnly”, BindingFlags.Instance | BindingFlags.NonPublic);
isreadonly.SetValue(HttpContext.Current.Request.QueryString, false, null);
HttpContext.Current.Request.QueryString.Remove(“View”);
isreadonly.SetValue(HttpContext.Current.Request.QueryString, true, null);
It pays reading all the comments. Matt this made my day, thanks! This solution solves the filtering issue.
You will need to add
using System.Reflection;
before the namespace, but then it just works great!
Thanks for the info Matt,
I’ll try it out somewhere this weekend, and if your info is correct, I’ll update the post. Thanks again!
Exactly what I was after… Thanks!!
hi Karine,
i use filter in ListViewByQuery,but i found “clear filter from ..” function cannot show.
how do i slove it?
thank you.
Thanks for your great articles.
But I think there are some thing wrong in the code below(Filter):
if (!string.IsNullOrEmpty(query))
query = “” + query + “”;
should be
if (!queryStr.Equals(“{0}”))
queryStr = string.Format(“{0}”,queryStr);
Thank you for providing this article it does contain a lot of great information about configuring a ListViewByQuery.
Currently I am trying to find a way to add paging and it doesn’t look like it is possible.
I am not really sure any other way in SharePoint to do this since I like this control will provide the ability to filter and sort so it just seems to complex to use any other method.
I have tried to pass this into the url:
?Paged=TRUE&p_Title=Agelidis&p_ID=11896&PageFirstRow=51
This doesn’t appear to work on a ListViewByQuery control, but a regular list this does change the page for the content to appear.
Does anyone have any insight into this and some recommendation how to add paging??
Hi Steve,
I know it is possible to add paging, but unfortunately I never found out how.
Karine
Didn’t get chance to read entire post and all the comments ! But if it’s not already posted then, here is the code to take care of Null values i.e. (Empty) filter conditions.
—————-
if(string.IsNullOrEmpty(this.Context.Request[filtervalue]))
{
query = string.Format(query, string.Format(“”,this.Context.Request[filterfield]));
}
else{
query = string.Format(query, string.Format(“{2}”,
this.Context.Request[filterfield], “TEXT”,this.Context.Request[filtervalue]));
}
—————-
Thanks,
Whizsid
Karine, 27 and 28 are the same. (You might wanna delete 27th )!
The reason to post the same comment twice is because I noted that first time it just printed “{2}” in the String.Format(… function. On submitting the comment second time, I realized that tags like lt; Eq gt; were filtered leaving only “{2}”.
It’s nothing but the same code taken from your function – BuildFilter().
Thanks.
Hi whizsid,
Thanks for the additional information.
Karine
Hi Karine,
Any solution to collapse the groups when the web part is rendered? and to enable the clear filter from thing?
Thanks for the great article. It was really helpful.
I want to add the ListViewByQuery Control into a web part. I did it like this.
protected override void Render(HtmlTextWriter writer)
{
//here i gave the list and view for the control.
writer.Write(“”);
view.RenderControl(writer);
writer.Write(“”);
writer.Write(“”);
writer.Write(“ExpLinkFormer = function(divId)”);
writer.Write(” {“);
writer.Write(“this._DivId = divId;”);
writer.Write(“this._init();”);
writer.Write(” }”);
writer.Write(“ExpLinkFormer.prototype = {“);
writer.Write(” _init: function() {“);
writer.Write(” var div = document.getElementById(this._DivId);”);
writer.Write(“var links = div.getElementsByTagName(‘a’);”);
writer.Write(“for (var i = 0; i -1) {“);
writer.Write(” //alert(links[i]);”);
writer.Write(” links[i].click();”);
writer.Write(” }”);
writer.Write(” }”);
writer.Write(” }”);
writer.Write(” }”);
writer.Write(” var expLnk = new ExpLinkFormer(‘ViewDiv’);”);
writer.Write(” “);
}
My problem is when groups are always expanded when the page loads. Please help.
[...] http://karinebosch.wordpress.com/sharepoint-reins/thе-listviewbyquery-control/ [...]
Pingback by My Favorite Blog List | Articles Review | August 18, 2010 |
The individual context menu is not working. I have added listviewbyquery on custom webpart. Is there any solution to get the context menu working?
The javascript message that pops up when i click on the context menu is:
This item is no longer available. It may have been deleted by another user. Click Ok to refresh page
Hi, great post. I am trying to use this control as a custom webpart in 2010, I am getting the same issue as Pragnesh wherby the context menu does not work. I have tried debugging the xslt and see that the ctx. object that is declared in the javascript is different, this is used to build the JSON callback, as the table ID is not found in the JSON, the user is told that the item no longer exists.
I wonder if Pragnesh or yourself hnow how to resolve this?
Thanks in advance.
Duncan
The hotfix below seems to address this issue.
http://support.microsoft.com/kb/2405789
Thanks,
Moninder
OMG, Thank you so much Matt for your post (#20). Simply removing the View parameter from the query string makes everything work like a charm! Sorting, filtering, even adds the small icons that tell you the column is sorted/filtered. Also enables the Clear filters option.
This just made my day and it’s not even noon yet, thanks again! Thanks to Karine for this blog also, great work!
Hi, I have been trying to use this the ListViewByQuery control on a subsite to display a list from the parent site. It seems to support standard list views fine, however I cannot get the DataSheet view to display on the child site. Is the DataSheet View support for this.
Thanks,
Moni
Hi,
this post has been very useful for me but now i want to add the paging feature to my listviewby query and i don’t know how.
is there anyone who has achieved it?
thanks
if we used this web part in page and re customized it using SPD and try to make A connection with Custom list form is this work
Hi,
Nice post. Is their any way to clear the view. Actually I am looking to clear the value from ListViewByQuery and then populate it again.
–Sujeet
Im proberbly stupid but I cant get this to work. I get a “Error Rendering Control – MyListViewByQueryName An unhandled exception has occured. Object reference not set to an instance of an object.
What am I doing wrong?
In jquery
$(document).ready( function(){
$(‘#viewDiv’).find(‘a[onclick*=ExpCollGroup]:even’).each(
function(){
this.onclick();
}
);
});
Wished I spotted this one earlier saved me the trouble of figuring it out, I made mine a little more complicated, wasnt sure I could rely on that, but actually yours makes more sense than mine
$(‘#viewdiv td a[onclick*="ExpCollGroup"] img’).parent().each(function() { $(this).click(); }); });
This, as does yours, as does the original , clicks every button, if you have a double grouping then you only need to click the layer 1 groups, but I’d had enough by this point. without a Regex (i know theres yet another plugin) I couldnt filter by the “td titlen-n_1_$” where n1 is the ctxid and n2 is the Level1 count.
Would be great if someone could figure out a better selector to select only L1 clicks
Hi all,
I have this problem.. 2 list: States (Name) and Cities (Name, State).. I use the listViewByQuery control to show the States wich have not a city.. the problem is that I have 400 Cities so the query is too long and the listViewByQuery return
.. any ideas?
Tnks
Hi Karine,
Your blog is one of my favorite blog which I always likes to bookmark your posts.
I am using ListViewByQuery control, but the sorting logic is not executing as expected.
When I click on header column, it always loads SortOrder=Asc. It never goes to Dsc in the whole life time, no matter what how many times we have clicked the header column.
Any suggestions where am I doing mistake?
I have added all my logic in CreateChildControls(), and then moved code to OnLoad and then moved to EnsureChildControls(), nothing works.
The question in my mind is, how it detects the last sort order action? It should save somewhere right like ViewState etc… Because I am using plain web part which recreated when page loads everytime, it losts that data. So, everytime it is loading Asc instead of Dsc next time.
thanks
-Praveen.
Hi Praveen,
Thanks for reading my blog!
I have 2 questions for you:
- are you using SharePoint 207 or SharePoint 2010?
- Did you add some code behind as explained in the Order section?
Karine
Hi Karine,
Awesome blog. I tried this to query shared documents from a different collections.
It display the list nicely but I can’t get the “Add New Document” link to show up.
Any idea how to do that?
Cheers!!
This has dug me out of a hole, but this control still has problems. My encapsulation of this control allows the user to pick the list and view and in my fix I regex the collapse out the query.
I discovered if you add two onto the page then each one has a ctxid of 1, when it renders the table the ID’s of the table (and subsequently how it references each level for the click events) uses that ctxId as part of all other Id’s. As both Controls are ctxid=1 then clicking on items in WebPart 2 effect the tree in Webart 1
Not Good.
I tried using the ListViewPart instead encapsulated within my part, but this too, did not work, it failed to keep track of the ctxId’s, it’s client side code was correct in this case but the server side responses returned the wrong ctxId’s. So I returned to the ListViewByQuery and for now, hope no-one sticks two on a page.
Later on I might try and grab the rendered output and regex all the ctxIds and table Ids to match, somewhere in the 100′s and try and register the ctxid somewhere in the page/thread so other of my parts know which number they are.
44.Hi Karine,
Awesome blog. I tried this to query shared documents from a different collections.
It display the list nicely but I can’t get the “Add New Document” link to show up.
Any idea how to do that?
Cheers!!
[...] http://karinebosch.wordpress.com/sharepoint-controls/the-listviewbyquery-control/ [...]
Pingback by Display SharePoint list view or document library view from different site collection « BrandingSharepoint | July 29, 2011 |
I can do grouping at two levels successfully. For third attempt it is giving err: render failed . Any suggestion?
[...] http://karinebosch.wordpress.com/sharepoint-controls/the-listviewbyquery-control/ [...]
Pingback by Achieve similar to sharepoint lists the grouping of statistical functions | October 12, 2011 |