Working with Query CAML

The Collaborative Application Markup Language (better known as CAML) is an XML-based query language that can be used to query web sites based on Windows SharePoint Services.

BrightWork Reporter as well as the List Board View, List Chart View, and Gantt Chart web parts use CAML to filter the items returned by a report (for example, all items with a status of open). Without CAML, all the items in the lists and webs the query is executed against would be returned.

BrightWork Reporter also allows you to add dynamic filtering to a report. This allows a report user to specify or select the filtering criteria using the interface in the report

Note: Before you attempt to create a CAML Filter, consider whether or not you can achieve your aims using a Report Filter.

Report Filters are easier to use, achieve the same end result and offer some advantages over CAML filters.

For example, CAML can not be used to filter Metrics in a Metric Join but this can be achieved with Report Filters.

CAML filtering is still recommend if the report is querying a large data set and has performance issues or if you need to apply a filter to a lot of existing reports.

Building the CAML

Every CAML query you write for a List Query or Site Filter in BrightWork Reporter (and the List Chart View and List Gantt View web parts) must start with <Where> and end with </Where>.

In a basic CAML query, you specify a comparison operator, a field reference and a value. More complex examples include logical joins and multiple operator and field name combinations.

In the simple example below, a comparison operator (Eq), a list column (Type) and a value (Environment) have been specified. The effect of this CAML would be to only return items that have the value Environment in the Type column.

<Where> <Eq> <FieldRef Name="Type" /> <Value Type='Text'>Environment</Value> </Eq> </Where>

More complex examples involve defining more than one comparison operator using a logical join such as <and> or <or>. The effect of the CAML below would be to only return items Assigned To the logged in user (this is achieved with the UserID tag) and that have a Due Date of greater than today (i.e. that are overdue).

<Where> <And> <Eq> <FieldRef Name="AssignedTo" LookupId="TRUE" /> <Value Type="int"><UserID /></Value> </Eq> <Lt> <FieldRef Name="DateDue" /> <Value Type="DateTime"> <Today /> </Value> </Lt> </And> </Where>

To return items greater than or less than Today, use the OffsetDays attribute.

For example, the below code would return all items due in the last seven days and in the next seven days.

<Where> <And> <Geq> <FieldRef Name="DateDue" /> <Value Type="DateTime"><Today OffsetDays="-7" /></Value> </Geq> <Leq> <FieldRef Name="DateDue" /> <Value Type="DateTime"><Today OffsetDays="7" /></Value> </Leq> </And> </Where>

Note: The OffsetDays value can be passed from a QueryParams.

Here is another useful CAML example. This reports on open Tasks and excludes Summary Tasks. Excluding Summary Tasks can be useful when reporting on rollup numbers like Work, Duration or Cost, as the number will not get included more than once when getting totals. You might need totals in Metrics, Charts, or reports with column totals.

<Where> <And> <Neq> <FieldRef Name="Status" /> <Value Type="Text">Completed</Value> </Neq> <Eq> <FieldRef Name="bwSummaryTask" /> <Value Type="Boolean">0</Value> </Eq> </And> </Where>

Below is an example of a CAML query using the <OrderBy> element. The effect of this CAML would be to sort the columns first in descending order by the WeekEnding column, and then in descending order by the Created column. Note: this is not the standard CAML syntax for nesting OrderBy.

<Where> <OrderBy> <FieldRef Name="WeekEnding" Ascending="FALSE" /> <FieldRef Name="Created" Ascending="FALSE" /> </OrderBy> </Where>

Using CAML with Reporter Filters

Report Filters enable a report user to dynamically filter the report using the Reporter UI. To achieve this you must build the appropriate CAML in the List Query Template or Site Filter and replace the filter value with the filter name (see Add a New Report Filter) When the user selects or specifies a value in the report UI, this value will be passed back to the CAML in place of the filter name.

Every CAML query you write will start with <Where> and end with </Where>.

In a basic CAML query, you specify a comparison operator, a field reference and a value. More complex examples include logical joins and multiple operator and field name combinations.

The example below is from the My Work Report. The filter name is CurrentUser (the filter name should always be between square brackets e.g. [Name].

<Where> <And> <Or> <Eq> <FieldRef Name="Owner" LookupId="TRUE" /> <Value Type="int">[CurrentUser]</Value> </Eq> <Eq> <FieldRef Name="AssignedTo" LookupId="TRUE" /> <Value Type="int">[CurrentUser]</Value> </Eq> </Or> <Eq> <FieldRef Name="CompletedFlag" /> <Value Type="Boolean">0</Value> </Eq> </And> </Where>

The corresponding QueryParam code for the above CAML is below. Note how the named attribute is the same as in the CAML.

<QueryParams requireUserInput="false"> <ChoiceParam> <UserParam name="CurrentUser" caption="User" defaultValue="[me]" /> </ChoiceParam> </QueryParams>

Supported CAML Query Elements

The following CAML tags are supported:

Logical Joins: <And> <Or>

Comparison Operators: <BeginsWith> <Contains> <Eq> <Geq> <Gt> <IsNotNull> <IsNull> <Leq> <Lt> <Neq>

Note: These tags are case sensitive.

Group/Order Elements: <GroupBy> <OrderBy>

Using CAML with either Cache

Work Cache

When you want to filter items from the Work Cache with an Assigned To value of the logged in user, you can use the following CAML query:

Note: The Field Name for Assigned To is bwWICacheAssignedTo.

<Where> <Eq> <FieldRef Name="bwWICacheAssignedTo" LookupId="TRUE" /> <Value Type="int"><UserID/></Value> </Eq> </Where>

To select items from the Work Cache with an Assigned To value of the logged in user, and from a list with ‘Tasks’ in the name, you can use the following CAML query:

<Where> <And> <Eq> <FieldRef Name="bwWICacheAssignedTo" LookupId="TRUE" /> <Value Type="int"><UserID /></Value> </Eq> <Contains> <FieldRef Name="bwWICacheListName" /> <Value Type="Text">Tasks</Value> </Contains> </And> </Where>

Project Cache

To select closed projects from the Project Cache use the following CAML:

<Where> <Eq> <FieldRef Name="bwPCICompletedFlag" /> <Value Type="Boolean">1</Value> </Eq> </Where>

To select projects from the Project Cache that are open, and exclude project office sites, use the following CAML. Of course, selecting the Open Projects view is an option also!

<Where> <And> <And> <Eq> <FieldRef Name="bwPCICompletedFlag" /> <Value Type="Boolean">0</Value> </Eq> <Eq> <FieldRef Name="bwPCIArchivedFlag" /> <Value Type="Boolean">0</Value> </Eq> </And> <Eq> <FieldRef Name="bwPCacheIncludeInProjectReports" /> <Value Type="Boolean">1</Value> </Eq> </And> </Where>

To select open projects where the logged in user is the project manager use the following: