Report Rapport in MOSS 2007

Many of you have probably used the Content and Structure Reports tool to generate reports on content in your site. These reports allow you to query on all the pages and documents in your site collection in a specific way; for example, one report we include out of the box is the "Checked Out To Me" report, which quickly shows you all the documents and pages that are checked out to you under a specific location in your site collection. When you run a report, it is aware of your current location in the site collection, and only shows you results from that location. You can scope a report to a single list, a single site (and all the subsites and lists under that site), or your entire site collection.

If you haven't used this feature yet, you should give it a try - just click Site Actions -> View Reports to get a list of reports that we include out of the box. Click any of these to run the report from your current location. Alternately, you can run a report at any location from within the Site Content and Structure tool; simply navigate to any location in the site hierarchy, click the View dropdown, and see the list of reports appear:

Reports

Since I am currently at the root of my site collection (see that "Lincoln's Site Collection" is highlighted on the left), if I click the "Checked Out To Me" report, I quickly get a view of all documents and pages that are checked out to me across the entire site collection:

If I drill down deeper into the site collection (say, if I click on the "News" subsite to navigate there) and run the same report again, I will only see the documents and pages that are checked out to me in that site:

Pretty simple, isn't it? Each Content and Structure report is essentially one Collaborative Application Markup Language (CAML) query that runs each time you access the report. You can take a look at the exact CAML query that is being run for each report if you navigate to the Content and Structure Reports list; in the Content and Structure tool, just click "Content and Structure Reports" to get a list of all the reports you have available in your site collection. You can see that the Content and Structure Reports list is just like any other list in SharePoint - so, you can add a new custom report to your site collection simply by adding an item to this list.

The interface for adding (or editing) an item in this list looks like this:

A quick breakdown of these fields:

  • Report Title. No explanation needed.
  • Resource ID and his twin brother, Resource ID. Just ignore these; they're for internal use, so you should leave them blank.
  • CAML List Type. You can specify a list template here if you only want your report to search through a specific type of list. For example, the out of the box "My Tasks" report specifies <Lists ServerTemplate="107"/> in this field to ensure the report only queries the My Tasks list. This SDK article has a list of these ServerTemplate values for each SharePoint list type. If you're code-savvy, you can also use the SPWeb.ListTemplates property to get a list of all templates available in a given site.
  • CAML Query. The query for the report.
  • Target Audiences. If you want to use audience targeting for any report, you can do that here. If you specify a group or audience in this field, then the report will only be available to those users.
  • Report Description. This is the description shown in the Site Actions UI.

Writing the CAML query for the report can be sort of painful, especially if you aren't very familiar with the syntax. The good news is that there are some cool tools out there that can really lighten the burden. Some of my favorites include:

I won't go in detail how to use these tools, because they are both quite straightforward and intuitive. They, of course, aren't officially supported by us, but you may still find them useful - I know they have helped me in the past. Additionally, you should check out the CAML node in our SDK to help you learn more syntax.

Before I get into sharing some custom report samples, let me enumarate a few tips, tricks, and caveats that you should keep in mind when playing with the reports list:

  • By default, the "CAML Query" field in the Reports list is a "Single Line of Text", which limits it to 255 characters. This is an insufficient length for many of the queries you will want to write! To fix this, you want to change this field to a "Multiple lines of text"; under the Reports list, click Settings -> List Settings, click on the CAML Query column, switch the column type to "Multiple Lines of Text", and click the "Plain Text" radio button below. This will allow you to enter much longer queries.
  • When you enter a custom query, don't enclose it in a <Query> tag. If you do this, your query will not be parsed correctly, and will return zero results.
  • When entering a query for a new report, remove all extra line breaks and spaces. Your query should simply be a sequence of tags, like <Where><Or><Eq> and so on. If you have extra spaces or line breaks in there, your report will not be parsed correctly, and will return zero results.
  • When viewing report results, you are stuck with the columns we give you, namely: Type, Title, Modified, and Created By. Sadly, you cannot customize which columns to display in this view, and yes, Created By is always empty (this is a bug that we were not able to fix by RTM).

Okay; I think that just about covers it. Without further ado, here are some custom reports:

All Items Checked out. We have an "Checked Out to Me" report, but it may be useful to know all items across the site collection that are checked out by anybody.

<Where><IsNotNull><FieldRef Name="CheckoutUser" LookupId="TRUE"/></IsNotNull></Where>

Article Pages with no Rollup Image. You may want to ensure that all your article pages have a rollup image associated with them, so they don't look strange in a Content Query web part alongside other Article Pages that do have rollup images. Here's how you get a list of pages that don't have a Rollup Image associated with them:

<Where><And><IsNull><FieldRef Name="PublishingRollupImage" /></IsNull><Eq><FieldRef Name="ContentType" /><Value Type="Choice">Article Page</Value></Eq></And></Where>

You can easily change "PublishingRollupImage" to another field to run a similar query for whatever field you choose.

All large bitmap images. Say your authors have been getting into the habit of using large bitmap files for their site images, and you want to generate a report that gives you a list of all .bmp files in your site collection that have either a height or a width over 200 pixels, and output them in descending order by file size. This is the query you would use:

<Where><And><Eq><FieldRef Name="DocIcon" /><Value Type="Computed">bmp</Value></Eq><Or><Gt><FieldRef Name="ImageWidth" /><Value Type="Integer">200</Value></Gt><Gt><FieldRef Name="ImageHeight" /><Value Type="Integer">200</Value></Gt></Or></And><OrderBy><FieldRef Name="FileSizeDisplay" Ascending="False" /></OrderBy></Where>

Hopefully these examples will get you started with some ideas on how to build relevant reports that filter data in a meaningful way for your application. If not, leave a comment and we'll try to help you out.

Lincoln DeMaris, Program Manager, Web Content Management