Wednesday, April 16, 2008

Displaying listname and sitename when querying data with CQWP

A colleague of mine who works with our MOSS Intranet asked me if I could help him tweak the Content Query Webpart to aggregate all announcements from all sites in our Intranet.

He had already started to tune the content, following Heather Solomons excellent article: and an Microsoft article:

However, there seemed to be no way of getting the listname and sitename where the item was coming from to the XSL transformation. After some digging deep with the reflector and some testing with the SPSiteDataQuery I found a solution that seems to do the trick.

First (as described in step 3 in the Microsoft article) you need to export the .webpart file from the CQWP-webpart that you have configured.

In the .webpart file there is a property called ViewFieldsOverride. This property overrides the fields that are loaded by the CQWP. This gives us an opportunity to add the Listname when querying the data.

Since we are overriding it, we first need to add the default fields (Title, Created etc) manually. After that, we add the listname and sitename using the <ListProperty Name="Title" />and <ProjectProperty Name="Title" />

<property name="ViewFieldsOverride" type="string"><![CDATA[<FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Nullable="True" Type="Text" /><FieldRef ID="{94f89715-e097-4e8b-ba79-ea02aa8b7adb}" Nullable="True" Type="Lookup" /><FieldRef ID="{1d22ea11-1e32-424e-89ab-9fedbadb6ce1}" Nullable="True" Type="Counter" /><FieldRef ID="{28cf69c5-fa48-462a-b5cd-27b6f9d2bd5f}" Nullable="True" Type="DateTime" /><FieldRef ID="{1df5e554-ec7e-46a6-901d-d85a3881cb18}" Nullable="True" Type="User" /><FieldRef ID="{d31655d1-1d5b-4511-95a1-7a09e9b75bf2}" Nullable="True" Type="User" /><FieldRef ID="{8c06beca-0777-48f7-91c7-6da68bc07b69}" Nullable="True" Type="DateTime" /><FieldRef Name="PublishingRollupImage" Nullable="True" Type="Image" /><FieldRef Name="_Level" Nullable="True" Type="Number" /><FieldRef Name="Comments" Nullable="True" Type="Note" /><ListProperty Name="Title" /><ProjectProperty Name="Title" />]]></property>

(Note: I found the GUID:s of the default fields using the reflector)

After importing the webpart file, the listname and sitename are now available in the xsl-file as ListProperty.Title and ProjectProperty.Title

Insert the following xsl code in ItemStyle.xsl (for more details, see the Heather Solomon or Microsoft guides above):

<xsl:value-of select="@ListProperty.Title" />
<xsl:value-of select="@ProjectProperty.Title" />

There is still one problem I'm trying to solve. The Author is for some reason presented as ID#Name (where ID is a number, specifying the user) when using it in the XSL-file. This can be fixed with the variable below, but I would rather find why the override causes Author to be changed.

<xsl:variable name="Author">
  <xsl:when test="contains(@Author, '#')">
   <xsl:value-of select="substring-after(@Author, '#')" />
   <xsl:value-of select="@Author" />

Use it with the following xsl code:
<xsl:value-of select="$Author" />

Update: To enable Audience targeting, we first need to add another field into ViewFieldsOverride. Otherwise, the settings in the webpart won't work:
<FieldRef ID="{61cbb965-1e04-4273-b658-eedaa662f48d}" Nullable="True" Type="TargetTo" />


Jason said...

Thank you for the code, it helps out quite a bit, but I was looking for the ability to use the SiteURL rather than the actual name. example: with your code it gives me /My%20Blog%C3/lists/yada
I would like to have /myblog/lists/yada

I'm not sure if this is possible. Thank you.

Jonas said...

Exactly what I've been looking for, thanks!

Lars-Erik said...

You're the only one that have been able to help me with this (on google at least). However, when I add the ViewFieldsOverride I lose the body of the announcements which I've added through CommonViewFields. How can I be able to use both?

Per Gårdebrink said...

Lars-Erik: You have to add the field to ViewFieldsOverride instead since when you use that, CommonViewFields is not used (if I remember it correctly).

I'm currently on parental leave and don't have access to a sharepoint environment to test, but I think that you should be able to add the following somewhere between the other FieldRefs (you'll have to add the start and end tags since blogger doesn't allow me to add them):

FieldRef name="Body" Nullable="True" Type="Text" /

I'm guessing that the name isn't "Body" so you'll have to replace it with the same column name as you use in CommonViewFields

You could also try to find out the ID for the Body column if the XML above doesn't work and add it like the other fieldrefs.

Per Gårdebrink said...

Hmm.. maybe name should be capitalized as Name

FieldRef Name="Body" Nullable="True" Type="Text" /

Lars-Erik said...

Thanks for your reply, that was quick! However, I still can't get it to work. Before I used the ViewFieldsOverride I could access the body through ItemStyle.xsl using @Body, in the webpart type Note. I tried adding it to the ViewFieldsOverride by grabbing the Field ID from the announcements Schema.xml, I don't know if it is the right ID? I still can't get it to show up using the for-each-loop in ItemStyle.

Per Gårdebrink said...

Lars-Erik: Ok, that's strange.

I checked our MOSS intranet (it's been a while since I wrote it) and we use the body of announcements. So if you would like, I could send you a copy of our XSL and .webpart file. Send me an email and I'll reply with the files attached.

My email is: my firstname DOT my lastname (replace å with a) AT gmail DOT com

Lars-Erik said...

Excellent, thanks! As it turned out from the files you gave me, I had just screwed up the ViewFieldsOverride somehow. I swapped it with your entry, and it all works out. Thanks again for all your assistance. :)

Glyn said...

Per, thanks for this; it worked great to get the list title of the items that I was aggregating. I've tried to use a similar technique to get the description of the list that the item originates from. However ListProperty.Description doesn't seem to work - do you have any ideas?

Thanks again,

Patrik Luca said...

Pingback from IT Pro Ramblings

Anonymous said...

Is there a similar solution for SharePoint 2010 with linking to the site ?


Jordi Weber
The Netherlands

This works, but the site name does not link to the site..

陳珮琴 said...

IS VERY GOOD..............................

Anonymous said...

Hai, its retrieving site name but other list columns were not showing..

Anonymous said...

It helped a lot. Thanks a lot.

Anonymous said...

Thanks! It helped a lot!

Anonymous said...