Further Customise CQWP to Display Draft and Checked Out Pages By User

In a recent article I demonstrated how to embed CAML queries into the Content Query Web Part (CQWP). I’m going to take that concept a little further this time by customising the columns output by the CQWP when it returns its query results.


What problem will we be solving? Well I’ve had a lot of feedback from clients who have been editing content in their MOSS publishing sites. If the site is large, they tend to lose track of what pages are currently checked out or in a draft state. MOSS does provide a solution to this via Site Actions -> View Reports -> All Draft Documents . The feedback I get is that this isn’t obvious enough for users, and they want to be notified about content that needs their attention.


The CQWP is an ideal solution to this. I can place two CQWP instances on the front page of the site, one listing all draft documents, the other listing all checked out documents. The end result will look like this;

The document names provide a hyperlink so that the page can be navigated to instantly. A date field is included to give some indication how old the item is. The best bit is that items will be grouped by the user who has altered the page. This provides a bit of ‘name and shame’. You’d be amazed how quickly our authors action their outstanding pages when their check outs are listed for all other authors to see!


Once we have a working CQWP on the page, we’re going to limit it’s visibility via an Audience so that only content Authors and above see it. I’ll also skim over the topic of caching so that we don’t impact our server by firing off these CAML queries on the front page.


 Get the CQWP XML

My first article on CQWP details how to get hold of the Web part xml for hand editing. Read that here; https://jamiemcallister.com/post/Embed-CAML-Queries-into-the-Content-Query-Webpart---Finding-Draft-Publishing-Content.aspx#comment


 Override the Query

Also as detailed in that article we need to place our CAML query into the XML.


The All Draft Documents query will look like this;

<property name="QueryOverride" type="string">&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef ID="{fdc3b2ed-5bf2-4835-a4bc-b885f3396a61}"&gt;&lt;/FieldRef&gt;&lt;Value Type="Number"&gt;3&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name='CheckoutUser'/&gt;&lt;/OrderBy&gt;</property>

The All Checked Out Documents Query will look like this (for your second web part);

<property name="QueryOverride" type="string">&lt;Where&gt;&lt;IsNotNull&gt;&lt;FieldRef Name='CheckoutUser'/&gt;&lt;/IsNotNull&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name='CheckoutUser'/&gt;&lt;/OrderBy&gt;</property>


Notice that these queries are a little different from my first article. This time they have an Order By clause on CheckoutUser. That’s going to help provide our name and shame headings!


 Alter the Title

Provide a new title for the CQWP by inserting this;

<property name="Title" type="string">All Draft Documents</property>


If you’re working in a multi-lingual site you might want to replace the title text with a Language Resource File reference instead.



 Get the Columns we want

The CAML query can return all sorts of crazy data about the pages in its result set. The CQWP displays the name and description by default. We want the Checkout User for our title, and the last modified date of the page.


We amend the Common View Fields property. The format is “fieldname,type;fieldname,type” without any spaces. The xml is;


<property name="CommonViewFields" type="string">Modified,Text;CheckoutUser,User</property>


Notice that the field names are the internal field names. Should you wish to display different fields to my example, you’ll need to find out the internal field names. This can be done by;

Looking in the Content Type that the field resides in.

Looking in the markup (ListViewXml element) of an AllItems.aspx view that references the field. (Needs Sharepoint Designer and probably too much hassle!)

Vincent Rothwell (aka TheKid) has a reference for some commonfields here;



 Output the Columns

Remember me saying that CQWP outputs the name and description by default? I want to replace description with my Last Modified Date. I therefore perform a rename to fool CQWP into outputting my data;

<property name="DataColumnRenames" type="string">Modified,Description</property>


 Order the Output

My CAML query had an Order By on CheckoutUser. Now if I group by that field as well that will give me my user name sub-headings.

<property name="GroupByFieldType" type="string">User</property>


We’ve dodged a slight gotcha here. If I hadn’t put the Order By in the query, the Group By would have been ignored. (That originally foxed me for a few minutes I can tell you!).


 Tell the Web Part Where to Query

You can do this via the UI, but what the heck. Tell the web part which site it is starting its query at. Just provide a top level url;

<property name="WebUrl" type="string">/UK</property>


 Deploy the CQWP

You are now able to save the file, and place the web part on a page. This was detailed in the prior article.


 Reducing the Server Load

The CAML query is performing work across several lists. If the page where this CQWP resides were being accessed several times a second, your server is going to be doing a lot of unnecessary work. We are going to use Audiences and caching to reduce the risk of this.


With the CQWP on the page, click into Modify Shared Webpart and open up ‘Advanced’. At the bottom of that section you’ll find a box to specify target audiences;


This allows you to define which groups will see this web part. You might want to include your Authors, Managers, Administrators. Select them via this box dialog. Once the page has been checked in only people you just defined in that Audience box will ever see this web part. Since your general readership no longer see that CQWP the cross list query is not being fired on every page visit.


Save the web part. Check in the publishing page. Our work on this page is done. We now want to set up some caching to further reduce the server load.


Go to the Site Settings page at the top of your site collection.  We’re interested in the Site Collection Administration section;

Click into Site Collection Object Cache.


You’ll find a section called ‘Cross List Query Cache Changes’. This should be changed as below;

When we do this we know that maximum the queries in our CQWP will only be run once per minute. This caching means that when an author finally checks in the document that they had locked everyone out of for the past few weeks, their shame will be visible on the screen for perhaps an extra 60 seconds afterwards. A small price to pay to reduce server load!


Remember that this setting change will effect all CQWP cross list queries in the whole site collection. Unlikely to be a big issue, but change this setting with your eyes open to the effect!


That is it. We’ve specified our own query for a CQWP, overridden the columns that are shown in that web part, and taken reasonable steps to ensure our server wasn’t overloaded with work, and it felt good.


One final point. We only added two extra fields in our CommonViewFields property before – Last Modified Date and Checkout User. What if we‘d wanted to have even more fields visible, can it be done? Yes it can. You can put as many fields in CommonViewFields as you like, however there is a big BUT coming…..



BUT the standard XSL that renders the CQWP output won’t handle the extra fields. If you want to do this, you have to go even further than we did today and customise the ItemStyle.xsl for the CQWP. That will be covered in a future article.


Disclaimer: The software, source code and guidance on this website is provided "AS IS"
with no warranties of any kind. The entire risk arising out of the use or
performance of the software and source code is with you.

Any views expressed in this blog are those of the individual and may not necessarily reflect the views of any organization the individual may be affiliated with.