In this post I thought of taking advantage of Microsoft Office SharePoint Designer 2007 (SPD) seeing as its a free download (
http://office.microsoft.com/en-us/sharepointdesigner/HA103607621033.aspx) and anyone can now use its awesomeness. I’ve also encountered a number of people asking for some “Advanced functionality” on their SharePoint Lists without wanting to call in a developer to rattle up Visual Studio. This leads me to bring an old faviorite into the limelight and present an article on the
Data View Web Part. It too renders the need for fame along with SPD becoming a freebie.
The Problem:
I have a standard list that is grouped according to a certain column, in this case the classic “Title”. The list is a budget. This means I have two columns – One Budget Value and the other an Actual Value. A third column essentially needs to be a calculated column that determines the variance between the first two fields. Meaning what is the difference between the actual spend and what actually budgeted. Basic stuff. Limitation comes in when start using the Total Sum for that column (as seen below).
The Proposed Solution:
Basically we need to create a Data View Web Part based on this list and start customising its appearance/behaviour using XSLT and some XPath formulas.
Figure 1 - Standard SharePoint Budget List, grouped and totalled
1) Open up SPD 2007
2) I've created another site to do this demo. This site is called "dataview" and is based on the Publishing Site template.
3) Next, I navigate to the Pages library of this site, locate the default.aspx page and detach it from the Page Layout associated to that page.
Figure 2 - Detaching the Default.aspx page
SPD will warn you that you are detaching your page from the WelcomePages.aspx page layout. This is what we are looking for. Also remember to check out your page. After this you’ll see a prompt stating that you can now edit the page.
4) So here is the new publishing page ready for editing:
Figure 3 - Publishing page with Web Part zones
5) On the SharePoint Designer toolbar, click on the Data View option and Insert Data View...
6) You’ll now notice the Data Source Library window pane appears on the right of your SPD application
Figure 5 - Data Source Library
7) I’d like to pause here for a moment as I can’t possibly overlook the true essence and power of the data view capabilities. Notice the multiple variations of data sources that are available. Display data from SharePoint Lists, Libraries, Database Connections, XML Files, Scripts, Web Services, the BDC and event Linked Sources. Each of these is a blog topic by itself. I encourage you to delve deeper in these options. Take a look at Linked Sources - it’s incredible, merging multiple lists in a single XML view that can be displayed on your SharePoint page using the Data View web part – this is seriously powerful guys!
8) Next, we're going to select the Budget SharePoint list for my example. I simply drag and drop the Budgets list onto my publishing page in the top web part zone that we created above and it creates a DataFormWebPart!
9) Then we start configuring the way you want your new web part to look by using the Common Data View Tasks. This can displayed by clicking the handle on the top right of the data view web part.
Figure 6 - Common Data View Tasks
Using this control basically allows you to configure which columns you want in your view, grouping and formatting.
I really suggest working in split view, sometimes it really is easier to configure code than having to try find the right window to do something. I’ve included the snippet of my code at the end of this article. Check it out and compare to your code if you wish.
10) We're going to basically include the two fields - Budget Value and Actual Cost and going to create a calculated column that is the Variance between the other two columns. Creating the calculated field requires you to “Add a Formula Column” on your “Edit Columns” window. I’m going to group them by Title, sort it Ascending and do a Count for the grouping.
Figure 7 - Sorting and Grouping of the DataView Web Part
It’s very important to ensure you select the "Show column totals per group". Its provides us that grouping detail so we can total our column values per group. This is called a Footer column:
Figure 8 - Example of the Footer column from Grouping
On each cell, click the on the cell. Right click and "Insert Formula". Do this for the Budget, Actual and Variance columns. Add the following XPath calculations:
Count:
<xsl:value-of select="count($nodeset/@Title)" />
Budget Value:
<xsl:value-of select="format-number(sum($nodeset/@Budget_x0020_Value), "R#,##0.00;-R#,##0.00")" />
Actual Cost:
<xsl:value-of select="format-number(sum($nodeset/@Actual_x0020_Cost), 'R#,##0.00;-R#,##0.00')" />
Variance:
<xsl:value-of select="format-number(sum($nodeset/@Actual_x0020_Cost)-sum($nodeset/@Budget_x0020_Value),
'R#,##0.00;-R#,##0.00')" />
Notice that the return is a sum of the nodeset. This is necessary when suming a group within a list.
I’m also formatting the field in Variance column number currency using 2 decimal places and an R for ZAR Rand. It’s an optional.
11) Pause and reflect time...
So we have now created our Data View web part and configured our XSLT (XSL Transformation) to include various data elements from our list. We’ve grouped our list, included the totals per group and applied some basic formatting. In the code below, you’ll find the data source is referenced by using CAML.
12) Save and Check – In your page, your output should be similar to this:
Figure 9 - Result of the column grouping and XPath calculations
Thats it. We're now able to perform a sum on the variance based on calculations from other columns at runtime.
Hope this helps.
Over n Out
MarcL