Skip to content

Creating Custom Reports with CAML

June 15, 2010

Right now, I’m working with the powerful CAML functionality in Sharepoint to generate automatic reports of site data. Personally, I would much rather write SQL queries as they are much more straightforward, but hey, it is the Sharepoint way to do things less efficiently. The first report I wanted to create was called, “New in the Past Week.” It displays items, believe it or not, that have been created in the past week. But first things first. To create (or modify) a report, you need to be in the Content and Structure Reports library, found under the View All Site Content > Content and Structure Reports. Once you’re in that library of sorts, you’ll see several reports that come with Sharepoint out of the box, such as Checked Out To Me and Expiring Within Next Seven Days, few of which you’ve probably ever used. And that’s why we’re creating our own. If you don’t know already, you can view these reports by going to Site Actions > Manage Content and Structure, and choosing a view (as described with screenshots on this MSDN blog entry).

So on to creating my report, “New in the Past Week”. First, click the New button to create a new item. I filled in a name and nice little description; these don’t really matter, they’re just for looks. The first step is the CAML List Type field. This isn’t required, but if you’re doing anything specific at all, you’re going to want to use it. It basically lets you specify what to query through. There are two possible tags you can enter here, <Lists ServerTemplate='' /> and <Lists BaseType='' />. In my case, I want to search through all pages, so I used:

<Lists ServerTemplate='850' />

The 850 tells Sharepoint to search through Publishing Site Pages, which is all I want to look through. For other data types, take a look at this Sharepoint Reference Sheet from Abstract Spaces. It has pretty much everything else. Now, the CAML Query field is where we get down to business. This is where the actual query goes. Long story short, for my purposes, this query works just fine:

<Where>
   <Geq>
      <FieldRef Name="Created" />
      <Value Type="DateTime" IncludeTimeValue='TRUE'>
         <Today OffsetDays="-7" />
      </Value>
   </Geq>
</Where>

Essentially, it’s looking for things whose Created field is greater than or equal to (<Geq>) today’s date minus seven days (<Today OffsetDays="-7" />). Would this have been easier to write in normal SQL? Yes. Does it still work? Definitely. This query combined with my list type specification above results in a view of all pages created in the past week!

As far as writing your own queries, Sharepoint Magazine has a great article about the basic operators in CAML. The syntax is a little strange at first, but it’s easy to learn and there are many helpful examples online.

Resources I Used:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: