Skip to content

A Few More CAML Reports

June 15, 2010

I’ve been writing a few more CAML reports, so here’s some more code and resources to write your own! Remember, queries can’t have any line breaks for Sharepoint reports.

Large File Sizes:

<Where>
   <Geq>
      <FieldRef Name="File_x0020_Size" />
      <Value Type="Lookup">1000000</Value>
   </Geq>
</Where>

Large JPG Images: (adapted from the ECM Team blog)

<Where>
   <And>
      <Eq>
         <FieldRef Name="DocIcon"  />
         <Value  Type="Computed">jpg</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>

For many of these CAML queries, you’ll find yourself in need of the internal column names – those are the names Sharepoint uses for columns in lists and libraries. For example, Sharepoint displays “File Size”, but to compare file size data, you need to use the internal value File_x0020_Size. A great list of display names, corresponding internal names, and the type used in the <Value> tag can be found in this MSDN article.

Advertisements

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:

Designing Web Parts: A Quick Reference Sheet

June 3, 2010

For the past few days, I’ve been working on designing a web part that in essence builds a Coin Slider slideshow given the name of a Picture Library. This has some inherent problems, and the main one I’m dealing with is varying image size. This slideshow script displays each picture as a background image —this means that if the box is too big for the picture, it will tile, and that doesn’t look too pretty. The best dynamic solution I’ve come up with is to set the slideshow size to the dimensions of the smallest image in the library (and hope there aren’t any really weird dimensions like 20×500 that will display wrong). Much to my dismay, several Javascript solutions failed, so I am currently developing logic in the .cs file. That’s easier said than done. If you find yourself in a similar situation dealing with picture info while designing a Web Part, the following should be at least marginally useful to you.

Section 1: Formatting the URL

If you just pull the list name and filename into your Web Part, characters like spaces are going to come over normally, which works in most browsers but it’s better to convert those to %20’s to be safe. C# has a nice little method for doing this:

System.Web.HttpUtility.UrlPathEncode(yourContentHere)

This will turn yourContentHere into something web-friendly that any browser can understand without a problem.

Section 2: Pulling image data

Now that you have the image displaying correctly according to semantics and Internet Explorer (two polar opposites, coincidentally), you might want to pull in image data such as dimensions. For my Web Part, I’m looping through all the items in a given Picture Library with each item named, cleverly, item. To get the width and height, you would do something like this:

script.Append("Image dimensions: " + item["Picture Width"].toString()
   + " x " + item["Picture Height"].toString());

Believe it or not, those are indeed what Sharepoint names the respective values. That code would have the following output for a 900 x 900 picture:

Image dimensions: 900 x 900

Using this information, I can determine the smallest dimensions (by using Area so as to include both width and height) and display my slideshow accordingly. For any other field in the Library, just use item["FieldName"] to refer to it. For example, I have a field called “Alt” for the alternate text attribute of my images. To call them, I just use:

script.Append("<img alt=\""+ item["Alt"].toString() +"\">");

Hopefully this will shine a little bit of light on how to design a Web Part that pulls data from a Library.

Part 3: Using this data

Now, I want to run some basic calculations on the height and width of images. To do this, they need to be of the data type int (or similar). The value returned by item["Picture Height"] is of the data type Object. This is a problem. A problem overcome by the following code:

height = Convert.ToInt32(item["Picture Height"]);
width = Convert.ToInt32(item["Picture Width"]);

And now I have my int variables height and width with integer values, ready for whatever math I can throw at them.

Edit: I forgot to include something that might be important. In case you wondered what I was referencing with script, as in script.Append(), it’s defined as such:

System.Text.StringBuilder script = new System.Text.StringBuilder();

Which just means it’s the code my script builds and ultimately outputs. Sorry about any confusion that may have caused.

Modifying RadEditor’s Table Wizard

May 27, 2010

The next step I took in bending RadEditor to my liking was modifying the Table Wizard, the built-in tool for inserting tables. The first thing I wanted to do was hide the entire Layout fieldset in the Table Properties tab. This wasn’t very hard. All you have to do is search for document.write(localization["Layout"]);, find the <tr> it’s contained in (line 373 for me), and add style="visibility: hidden" to that tag. It’s important not to use display:none because that will mess up the layout of the dialog; visibility:hidden makes that box invisible, but it still takes up the same amount of space so things display correctly.

My next task for the Table Properties tab was defining custom CSS classes for users to choose. This is explained in a Telerik forum post, and it’s not too hard. First, make a CSS file with all of the classes you want users to be able to choose from. I named it tablelayoutcss.css and put it in my \Resources\ folder. Then, open ConfigFile.xml and add the following code anywhere in between the <configuration> tags:

<property name="TableLayoutCssFile">/_wpresources/RadEditorSharePoint/5.x.x.x__1f131a624888eeed/Resources/tablelayoutcss.css</property>

Change that path and filename to whatever your CSS file is, of course. If you did this correctly, the CSS Class Layout drop-down should now contain your custom CSS classes!

My final change on that tab was setting display:none to the <div> around document.write(localization["ApplySpecialFormatsTo"]); and to the <table> around those special format options because they don’t apply to my custom CSS classes.

Next, I wanted to hide some options from the Cell Properties tab. This is a pretty similar process to hiding options from the Image Manager’s Properties tab. Looking through CellProperties.ascx, search for the option you want to hide (no spaces because it’s going to be in the format document.write(localization["ContentAlignment"]);) and add style="display: none" to the <tr> that contains it.

Further Customization of the RadEditor Image Manager

May 26, 2010

Removing the preview pane toolbar buttons is simple enough. But what if you want to get rid of the New Folder option, or take away certain elements from the Properties tab? That’s a little more complicated. (See my previous entry about editing the RadEditor Dialogs if you haven’t already.)

Removing the New Folder Option

Telerik actually has a great article about this. I’ll repeat their instructions here. Inside EditorDialogs\FileBrowser.ascx, find the tag. Simply add the property EnableCreateNewFolder="false" and the New Folder button will disappear from the file browser.

Removing Properties from Properties

These properties are all contained within EditorDialogs\SetImageProperties.ascx. What you’re looking for here is a document.write() of a term pretty similar to the property name in the dialog. In the table below, you’ll find the document.write() parameter and the line number of the corresponding
tag (more on this later).

Property document.write(); Parameter Line
Image Width/Height localization[“Width”], localization[“Height”] 720
Border Color localization[“BorderColor”] 768
Border Width localization[“BorderWidth”] 782
ALT Text localization[“ImageAltText”] 796
Long Description localization[“ImageLongDesc”] 810
Image Alignment localization[“ImageAlignment”] 824
Margins localization[“Margin”], localization[“Top”], localization[“Right”], localization[“Bottom”], localization[“Left”] 852
CSS Class localization[“CssClass”] 907

To hide any of these options, simply append style="display: none" to the <tr> tag on the indicated line. If you try this and it doesn’t work, or there isn’t a <tr> tag on that line, it’s probably because your SetImageProperties.ascx file is a little bit different than mine. In that case, just search the file for document.write(localization["ImageAlignment"]), for example, and find the containing <tr> to apply the style to.

Modifying the RadEditor Image Manager Dialog in Sharepoint 2007

May 26, 2010

This one took a long time to figure out.  Almost all of the support available online refers to the generic ASP.NET AJAX version of RadEditor which comes with handy dialog editing files. The Sharepoint 2007 one, however, does not. Luckily, these files can be downloaded as part of a free trial and plug right in to RadEditor without a hitch (so far at least) and allow you to edit dialogs just like you would in the generic flavor. Here’s what to do:

  1. Download the free trial of RadEditor here: (this version should work with modern installations of RadEditor; if not, look around for other trial versions on that site.)
    I recommend the DLL, Scripts, and Skins Only file, as it’s small and contains little more than what you need for this.
    http://www.telerik.com/account/free-trials/trial-product-versions/single-trial.aspx?pmvid=2214&pid=0
    Update: Looks like that requires a login. WordPress.com won’t let me host a .zip file but it’s a free registration so trust me that it works — giving up a little information is the price you have to pay, I guess.
  2. After you unzip the download, copy the entire EditorDialogs folder to your wpresources\RadEditorSharePoint\5.x.x.x__1f131a624888eeed\Resources\ folder.
  3. Open up ImageManager.ascx and mess around in there to your liking. To disable the Image Editor function, for example, search for the tag that contains Text="Image Editor" and add the parameter Visible="false" inside that tag. Do the same to make any button disappear from the Image Manager dialog.

That’s all there is to it! This forum posting helped me out a lot with this one after spending two days on Google trying to figure this one out.

More RadEditor Tweaks: Design, HTML, and Preview Edit Modes

May 26, 2010

Another quick RadEditor modification you can make is to the three available edit modes, Design, HTML, and Preview. You can hide any of them that you don’t want users to have access to with a simple tag in the ConfigFile.xml located in the Resources folder. Within the <configuration> tag, add this line of code:

<property name="EditModes">Design,Html</property>

This hides the Preview option for all users. Similarly, you can remove Html from the code and add Preview (comma separated for each) and you’ll only get Design and Preview.