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!
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
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.