Tag Archives: CAML

Custom document reports using OOTB SharePoint

There is a useful feature of SharePoint that provides site collection wide reporting across documents (and items). I’m referring to ‘Site Content and Structure Reports’ and it allows you to view a handful of OOTB reports as well as create custom reports (al beit somewhat limited). You may not want to provide access to this page for many users but it can be a handy IT feature if nothing else. I have an example of a “documents checked out to any user” report at the end of this blog.

The Content and Structure page can be found under Site Settings in SharePoint 2013 [or in the Site Actions drop down in SharePoint 2010]
The Content and Structure page can be found under Site Settings in SharePoint 2013 [or in the Site Actions drop down in SharePoint 2010]

The default reports are listed in the next image and some can be quite useful like ‘Checked out to me’ and ‘My tasks’ although often a customised solution has more elegant approaches to displaying this information elsewhere. The good bit is that it is very easy to make your own assuming you know CAML.

The reports can be run using the View menu on the Content and Structure page
The reports can be run using the View menu on the Content and Structure page

There’s a link to the list of these report definitions from the site collection root site (/Reports%20List/AllItems.aspx). Here you can create new report definitions just by adding a new item to the list. You merely need to define the CAML which will then return results from every library/list in site collection.

The set of Content and Structure report items the come with SharePoint
The set of Content and Structure report items the come with SharePoint

As an example, I have created a new report to return all checked out documents no matter who they are checked out to by using the following CAML:
<Where><IsNotNull><FieldRef Name="CheckoutUser" LookupId="TRUE"/></IsNotNull></Where>

Report: Checked out to anyone
Report: Checked out to anyone

It is important to note that you should leave both of the ‘Resource Id’ column blank. If you populate these columns with valid resource Ids then the correspoding resources will overwrite the reports CAML and description.
Also if you would like to restrict the query to a specific sub-set of list types then you can use the ‘CAML List Type’ field to enter a list definition ID (a full list of the defaults can be found here) like so:
<Lists ServerTemplate="101"/>

Another handy use of this is to create a report all of all pages awaiting approval. This can be achievd with a CAML List Type of: <Lists ServerTemplate="850"/> and a CAML Query of: <Where><And><Eq><FieldRef ID="{fdc3b2ed-5bf2-4835-a4bc-b885f3396a61}"></FieldRef><Value Type="Number">3</Value></Eq><IsNull><FieldRef Name="CheckoutUser" LookupId="TRUE"/></IsNull></And></Where>

Note the List Template ID of 850 representing Pages Library.

Hope you found this useful.

Dynamically nesting CAML query statements

Here is a short PowerShell function that can be used when you need to dynamically generate CAML queries with many logically joined statements. I actually adapted it from a C# implementation I wrote (which is probably more useful…) but as you can rewrite it in C# very easily I won’t bother bother posting it twice.

As CAML logical join operators (And, Or) can only compare two statements, when many statements need to be compared you must nest them which is what this function achieves. The $join parameter should be passed as “And” or “Or” and the $fragments parameter should be passed as an array of CAML statement strings such as:
@("<Eq><FieldRef Name='Title' /><Value Type='Text'>title</Value></Eq>", "<Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>name.docx</Value></Eq>")

# Define method for nesting caml query statements
function GetNestedCaml([array]$fragments, [string]$join)
{
    if ($fragments.Length -lt 1)
    {
        return [string]::Empty
    }
    elseif ($fragments.length -eq 1)
    {
        return $fragments[0]
    }
    elseif ($fragments.length -eq 2)
    {
        return "<$join>" + $fragments[0] + $fragments[1] + "</$join>"
    }
    $joinFrags = @()
     $baseJoinCount = [int][Math]::Floor($fragments.length / 2)
    for ($i = 0; $i -lt $baseJoinCount; $i++)
    {
        $baseIndex = (2 * $i)
        $fragsToJoin = @($fragments[$baseIndex], $fragments[$baseIndex + 1])
        $joinFrag = GetNestedCaml $fragsToJoin $join
        $joinFrags += $joinFrag
    }
    if ($fragments.length % 2 -ne 0)
    {
        $joinFrags += $fragments[$fragments.length - 1]
    }
    return GetNestedCaml $joinFrags $join
}