SharePoint 2010 Content Query Web Part

The story

According to Microsoft the CQWP can be used to aggregate content from the following sources:
  • a single list;
  • a site and it’s subsites;
  • an entire site collection;

The story demistified

The above mentioned is accurate...although not complete.
Imagine this real world scenario. Contoso is a medium sized company with approx 25.000 documents. These documents reside in a single Site Collection and are spread over multiple document libraries. Off course we implemented a thorough information architecture and all documents are neatly classified in terms of content type and metadata.
Now we have a business requirement: "I want a list of all contracts assigned to me.".
Further, an architectural principle states only OOB functionality is allowed.
And we are off.... Let's skip a couple of steps to the point, the CQWP is the way to go for this requirement (OOB it is... really... trust me... no you cannot use search because it cannot filter on [me] out of the box).

The CQWP: What does it do and where can I find it?

The CQWP is an out of the box webpart that, as stated above, enables you to aggregate data. We need aggregation over multiple libraries and we need filtering. Before this little 'gem' makes its appearance, you need to enable it. This is done via the activation of the publishing infrastructure feature.
2013-09-06-SharePointCQWP-02.png

Requirement analysis

The contracts are stored in several different libraries. Perhaps due to contract type, shere numbers, authorization,  or what have you.

Implementation

So we create a new page, add the CQWP.
Next part is the configuration. The 3 elements we are going to focus on here are:
  • Source
  • Filter
  • Sorting
First off the source. The documents, as stated above, are in multiple documents scatered across a site collection. Since we are testing the solution we set the source to aggregate over a single list, just for simplicity.
Content Type = Contract
The filter is DocumentManager (people picker field type) and it's value is set to '[Me]'.
2013-09-06-SharePointCQWP-03.png
Run the page and voila we have a result of 2 contracts (just an example). As a wise man once said: WOOHO! We have a working solution, almost!
Now, change the source of the filter to aggregate everything over the site collection like so:
2013-09-06-SharePointCQWP-04.png
Refresh the page and: CRASH!
2013-09-06-SharePointCQWP-05.png
What just happened? Where is my nicely aggregated list of contracts?
Let's demisty this!

CQWP internals

At this point we need to start debugging the solution. Review filter settings, (if you didn't already) enable trace logging, etc. etc.
As you perhaps already know the CQWP runs a CAML query that you probably want to analyze. Glyn Clough has written a nice blog on just how to do that! Off course we are using the ULS Viewer to analyze the huge amounts of ULS logs created. Somewhere in there you will find something like:
"xxxxxxx*
w3wp.exe (0x0C98) 0x16F0
Web Content Management
Publishing
7352
Warning ...entTypeId" Nullable="True" Type="ContentTypeId"/><Value Type="ContentTypeId">0x0101</Value></BeginsWith><Eq><FieldRef ID="{f366697d-21a6-493e-af7d-9b3cf5410ea4}" Nullable="True" Type="User"/><Value Type="User"><UserID/></Value></Eq></And></Where><OrderBy></OrderBy></Query>' generated the following error:The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. at the following url: XXXXX. Web Part title: Content Query b8210847-657f-40f3-80af-22bf444ad8f8"

The good, the bad and the ugly

The good part about this log is that it is extremely clear and straightforward! The CQWP is prohibited from displaying the results because it exceeds the set limitation of the list view threshold (LVT). As you can read in SharePoint 2010 capacity management the default value of the lvt is 5.000 items.
The bad part about this is that this setting isn't there because it looks really cool but it has a real purpose. Try cranking it up to, let's say, 30.000 and let half a dozen users try to access the page. You can actually see the performance penalty it causes.
The ugly part... now what? The CQWP should only return a couple of contracts, not even close to 5.000.

Indexed columns

As it turns out the columns you use with filtering AND sorting need to be indexed columns, according to Microsofts Best practices for publishing sites (SharePoint Server 2010).

The ultimate catch

Try and add more than 5.000 items in a single document library, filter on a single column (using the CQWP) and give it a shot! When you use an indexed column as a filter it will return results. When you remove the indexed column from that specific list it will not return results and spit back the log mentioned above.
So, the solution is adding the filter column on all document libraries in the site collection and....CRASH! WT#? We did everything correct and still nothing.
2013-09-06-SharePointCQWP-06.png

Conclusion

When trying to use the CQWP for aggregation you have to take into account the following:

  • Returning results may never exceed the List View Threshold
  • When the source is a single list your filters and sorting columns need to be indexed columns otherwise the result will not be matched with the LVT but the total set of items
  • When the source is other than a single list SharePoint will ignore all indexed columns, filters and sorting and will always match the total set of items the CQWP needs to query with the LVT.

Comments

Post a Comment

Popular posts from this blog

Sending email using Powershell script

Image
 Below is a script that can be used to send email using Powershell. If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )   { Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell } $currentDate = Get-Date -Format g $HeaderOne $HeaderTwo $HeaderThree $HeaderFour $Outputreport = "<HTML><TITLE> Test E-Mail notification </TITLE> <head> <style>body {font-family: Calibri;font-size: 14px;}table {border-collapse: collapse;}table, th, td {border: 1px solid black;font-family: Calibri,Verdana;font-size: 14px;}th {background-color: #4CAF50;color:white;}</style></head>                      <BODY>                                           <H2> Test E-Mail notification on $($currentDate) CET </H2>  ...
Read more

Convert List Collection to DataTable using C#

public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)         {             PropertyDescriptorCollection properties =                TypeDescriptor.GetProperties(typeof(T));             System.Data.DataTable table = new System.Data.DataTable();             foreach (PropertyDescriptor prop in properties)                 table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);             foreach (T item in data)             {                 DataRow row = table.NewRow();                 foreach (PropertyDescriptor prop in properties)                ...
Read more

Difference Between Site Template and Site Definition

Difference Between Site Template and Site Definition Before going into direct comparison, let me define both: Site Templates  : Are  snapshots of  sites at a point in time. When a user customizes a site from the UI or SPD, the custom template consists of the difference between the original state of the site (determined by its definition) and the state of the site or list when the custom template is generated. Custom templates remain tied to a particular site definition (for example, the site definition for SharePoint Web sites or Meeting Workspace Web sites), so that if the site definition is not present or is changed, the custom template cannot work. Site Definitions  : As the name implies, A Site definition is "core definition of a site”. Each site definition emerges through a combination of files that are placed in the 12 hive of WFE during installation of SharePoint. Site definition files include .xml, .aspx, .ascx, and .master page files, as well as docum...
Read more