I recently needed to display a list of items in alphabetical
order for a multi select parameter inside of a report. I was using Report Builder 3.0 and there is no option for sorting the list in the user interface. Many people recommended sorting the list before returning it to the report. This wasn’t really an option for me because I was pulling the data from a SharePoint list that I didn’t have control over.
A lot of articles out there say that this can’t be done or
that in order to do it you must embed some code inside of the report to sort
the list manually. There is an easier way to do this however. You can use CAML (Categorical Abstract Machine Language) to set the order.

My initial query looked like this:

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>MyListName</ListName>
    <ViewFields>
      <FieldRef Name="ItemToSort" />
    </ViewFields>
</ RSSharePointList>

In order to change the sort order of my data set, I changed my query to the following:

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>MyListName</ListName>
    <ViewFields>
      <FieldRef Name="ItemToSort"/>
  </ViewFields>

  <Query>
      <OrderBy>
           <FieldRef Name="ItemToSort" Ascending="True" />  
    </OrderBy>
  </Query>
</RSSharePointList>

I added the query node, and inside of that specified what I
want to order by; simple as that.  Be
warned, however, that CAML queries are notoriously easy to break. Everything needs to be in the correct case,
and no extra spaces. Because of that I would recommend using a CAML query
builder in order to generate your query.

This post is cross posted on TCSC’s blog.

Tagged with: