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. First off, we’ve got a list of customer Credit purchases:
This is the list for recording the pools of credits bought by customers. Note that Agreement Ref is the ‘key’ to this list, not customer name, as a customer may purchase multiple times.
Then we’ve got a (simple) list for recording actions, and how many credits it cost:
Notice that the Agreement Ref column is a lookup column against Agreement Reference in the ‘Credits’ list.
So, an operative performs some action, and records it’s usage:
…and then the Dataview web part shows our agreements, totals and remaining time:
Okay, so that kind of skipped a step – how the hell did I get the dataview web part to show that last screen? Well, I inserted a Joined subview as Sahil Malik described, and then modified the XSL for the subview.
- I removed the titles and stuff for the subview
- I changed the XSL template for the subview body. Instead of calling a template for each row, I created two XSL variables – for the number of credits in the pool, and to calculate the number used (highlighted in yellow)
- I then added another XSL variable to calculate the number remaining!
- And finally, I emit the values as two table cells:
So what’s that scary XSL line for ‘used’ doing? Well, it’s summing the ‘Used’ values for rows where the value of ‘Agreement Ref’ equals the ‘Title’ of the parent row. Works pretty well.You could do other stuff there – like counting the number of calls, etc..
How is this better than a grouped view, with a ‘Sum’ total for the used column? Well, we needed to have a way of seeing the remaining credits – this means knowing 1) the total credits bought in the first place, and 2) units remaining. A list view with a group and ‘Sum’ total wouldn’t show us these – and without that context, the totals are a bit meaningless.
Hopefully normal blogging service will be resumed shortly – been busy as lately!
Andy Burns' Blog 
My hero!
But seriously, thanks for posting this, I’d been attempting to do this in SPD for hours and couldn’t figure the right part of the code to change to get subtotals displaying.
Is it possible to then sort or filter on the remaining number of items?
I have an almost identical example but want to show only records that have less than 10% of the total amount remaining.
Any help much appreciated
Hmm. Sorting – not sure. But filtering – yeah, no problem. I’d be lazy and just use an around the xsl that outputs the whole row.
It’s lazy ‘cos it isn’t filtering at the query stage – you’re just not displayed records where the ‘if’ isn’t true.
‘Course that means having to write more xsl which can be a bit scary, but it would work!
Hi,
thank you for very interesting information.
I was tryng to use your instruction to obtain a sum of the subview data instead the value of each row, but Data View in sharepoint say me I did some error.
I’m not so able to understand how to write code properly in XSL, I would be obliged to you if you can send me the original code and the final code of your example, so that I can learn how to apply this to my lists (i tried to replicate your exercise using your lists but still I did some error, it seems I miss something).
Kind regards
Michele
I don’t think I’ve got the ‘before’ XSL code. Sorry.
I inserted the ‘Joined Subview’ as described in Sahil Malik’s instructions, which shows all the joined sub items in a nested table.
I then editted the XSL. The ‘template’ I modified was the ‘dvt_2’ template. Basically, I made it output another couple of table cells, rather than a new table.
I also queried the XML data to get the sum and total – those are the two lines highlighted in Yellow
Hi,
I have exactly the same issuue, but since i am new to sharepoint, I don’t know where exactly i need to change the XSL code. Can you please guide me which part of the XSL code, I need to change to get the Total Sum
Well, that depends on your code – but I’m pretty sure the template I modified was clled dvt_2 . All the ‘2’ bits seem to be for the joined subview.
Hi – if in your example you wanted to add a total for the Credits, Used, and Remaining columns – how could that be done?
/bump
Hi – if in your example you wanted to add a total for the Credits, Used, and Remaining columns – how could that be done?
Thanks for this — great post and opens up a lot of possibilities.
I wonder bout the performance of this for joining a large list to a normal one.
I have a list of articles (with ArticleIDs). I have another list with “views” — each row corresponds to someone having viewed the article.
Obviously the second list could be huge — with thousands of rows per article ID.
I want to join to the second list to show the rowcount, then sort by the rowcount.
In SQL this wouldn’t be so bad — the join would happen at the database layer, and we would only get our 100 or so rows back, one for each article ID, which we would then sort.
I’m not sure how this works in SharePoint though. I am worried that it will be stupid enough to pull all 10,000 view rows, then do operations on them. If it does this, where does the XSL translation take place? Is it at the application layer, or is it done by the client browser? If the former, then with judicious caching it might not be so bad. But if it’s the latter, it’s a no-go.
Unfortunately just incrementing a viewcount column for each row doesn’t work — that would require per-column permissions, which SP doesn’t support.
Well, it’s done on the server – but I expect that you’re right, pulling back thousands of rows would be a bad idea.
I never did find a good solution to the question of a page hit counter – but it is possible that you might be able to use usage reporting.
Hi – I’m new to SP 2010 and ran through the list tute you mentioned above but with a “Customer” title list and a “Task” list, trying to join on the Customer which is actually just a company department name that the task is being done for. For some reason I don’t get the option to choose a Joined Subview when I click on my title in the linked datasource. Can you speculate as to what I might be doing wrong?
Cheers! K
Hi Kylie,
Sorry for the delay – summer holidays.
Well, my immediate thought is that I’ve not tried doing this with SharePoint Designer 2010! I’m not sure what the actions to do that might be! It should still be possible, though the user interface to do it might have changed.
Is there anyway to use XSLT to display a particular item in a particular column in a list based on a date?
example:
Jan 1st would pull a partular banner image to swap out
Sorry I submitted it before I got a chance to finish:
So i want to dynamically swap out a banner image or background image based on a particular date.
I also would like to know if you can stylize the XSLT to look like a webpart Chrome Style but with different images and background color and text colors? Basically a box look and feels that has the list items displaying inside of it.