Andy Burns’ SharePoint Blog

Thrilling title, eh? So, previously I’ve talked about merged lists in the Dataview web part. This time, I was after something slightly different – rather than merging two lists, I wanted to join them.

Joining is pretty easy, actually, but the process is a little bizarre. I ended up using the post ‘Performing Joins with SharePoint Lists‘ by Sahil Malik. The thing that confused me was that when I was I expected to provide the ‘keys’ of my lists when I created the new Datasource – but actually, you define it when you insert your ‘joined subview’.

Anyway, the scenario was that there are customer purchases for ‘Credits’ to do things. Operatives then do things for them, and these actions subtract time off those pools of credits. The need was for a way to:

• Record purchases of pools of credits. Customers may purchase multiple  times, but under different agreements (or invoices, or whatever)
• Record actions and the number of credits that they cost
• Remaining total.

I solved this with a Dataview web part, and two lists. Read more »

Tags: Dataview, XSL

So, we have a customer who has a library of Newsletters.

These newsletters belong to different regions, and they have a date on which they should be ‘published’ – or at least, a date at which they’re the most recent item. So, we have two columns to capture that – News Region and Publish Date.

The customer wanted a web part on a page that would show the most recently ‘published’ item for each of those regions. Something like:

I did this with the Content Query Web Part (CQWP) – but how?

Well, first I set my CQWP to query the list of newsletters. They have a specific content type that I queried for. I filtered items with a Publish Date in the future out – although not a secure way of preventing users seeing these items, at least it doesn’t make the obvious.

Next, I set the CQWP to group by News Region, and Sort by descenting Publish Date. So far, so standard – but it would also return multiple items for each region, and display their title, not their region.

This called for a special Itemstyle, which I added using the technique mentioned before for putting ItemStyles into a different file. This made sense; we’re not going to use this style extensively, so it’s best to keep it separate.

Next, I modified my CommonViewFields to bring in the News Region column. I’ve mentioned this before.

Then I wrote my XSL:

Take a look at it – it’s cool.

Looking at the code, you can see I read the News Region that we’re looking at into a variable. I then count up the number of preceding nodes which also have the same value for their News Region.

The first node with a particular News Region value will always be the one with the latest Publish Date that isn’t in the future – therefore, the most recent one for that region. The count statement won’t count nodes for other regions, either. So, if it’s the first, we display it – using the News Region text, rather than the title. Simple!

(But a bugger of an XSL query to figure out)

Tags: CQWP, XSL

I was going to remind myself of a technique that I’d seen Liam Cleary use on his blog about importing XSL files into ItemStyles.xsl, but then I noticed that he’d linked to a far better idea from Brendon Swartz – you can change the XSL file that the Content Query Web Part presents ItemStyles for. If you export the Content Query Web Part as an XML file and edit it, you’ll see a property ItemXslLink. Simply give it a url to the new ItemStyles.xsl file.

Personally, I think this is fantastic. Frequently I have been asked to do custom roll-up of content, with customised display. If you have to add to the standard ItemStyles.xsl, then before long your ‘Presentation’ setting for your standard CQWPs is full of highly specific item styles. This way, I can isolate – nearly hide – the existance of my much more specific item styles to general users.

And I’ll say more about what I was doing with it shortly.

Tags: CQWP, XSL

A modification on the XSL I’d used previously – this gives a more readable presentation, and displays the values:

<xsl:template name="ShowFields" match="Row[@Style='ShowFields']" mode="itemstyle">
<div style="border:1px #aaa solid;background-color:#eee;margin:5px;padding:5px">
<xsl:for-each select="@*">
<xsl:value-of select="name()" /> = <xsl:value-of select="." /><br />
</xsl:for-each>
</div>
</xsl:template>

Tags: Content Rollup, CQWP, XSL