Mike Smith's Tech Training Notes SharePoint, PowerShell and .Net!: November 2008

11/23/2008

SharePoint: Group By on more than 2 columns in a view (Updated!)

An expanded version of this article, along with many other customization examples and how-tos can be found in my book, SharePoint 2007 and 2010 Customization for the Site Owner.

12/19/09 Updated! More pictures and some more detail in the steps.

12/19/09 Expand Collapse bug found and fixed! (not my bug, SharePoint’s bug!) See here…

1/30/11 SharePoint 2010 version of this article. See here…

Group By on more than 2 columns in a view

In my SharePoint classes I have often been of asked how to group on more than two levels in a view. I have always given the quick answer of "use SharePoint Designer"!

But, some of you have asked "How?". You may be sorry you asked… but here goes…

Goals:

Create a view with nested groups deeper than just 2 levels
Put the counts and totals at the bottom of each section.
Do the above with no custom web parts, custom XSLT or anything that has to be deployed to the server.

Sample:

Secrets and Tricks needed:

It can't be done "out of the box" in the browser
SharePoint Designer is needed to make the edits
You need to convert the view web part to a XSLT Data View (SharePoint Designer's Data View web part)
You need a Sort and Group secret
You need to fix the footer rows
You need to manually add your totals, averages, counts etc.

My Example:

I have a simple list (actually imported from Excel) with these columns: ID1, Bike, BikeType, BikeSize, BikeColor and Retail (price).
I want to group this on BikeType, BikeSize, BikeColor and count the items and sum or average the price in each group.

Steps: (and yes, a lot of them!)

Tip: There are many, many options available in the Data View Web Part used here. For the first time through this process don't change any options not listed below. You can always come back and experiment with the rest of the options later!
Create a new Standard View from the list's view menu.
Generally don't bother with any options other than selecting the desired columns.
- Don't set any grouping options, they will mess up the expand / collapse functionality
- Don't set up any Totals options, they will be ignored
- Do select the columns needed in your view, both display and grouping
- Optionally set the filter options (this can also be done in SharePoint Designer)
  
  The “before” – just a standard view with selected columns:
Open your site with SharePoint Designer and expand the Lists node in the Folder List pane.
Expand your list's node and double-click on your new view.
If your view page is not being displayed in the Design view, click the Design tab at the bottom of the page.
Note: the view is now being displayed using a ListViewWebPart and can only be customized from the browser ("Modify View")
Right-click in the middle of the view and select Convert to XSLT Data View.
Note: the view is now being displayed using a DataFormWebPart and
- is no longer a “View” and can not longer be modified from the “Customize this view”
- can be customized from SharePoint Designer,
- from the browser you can only rename the view,
- from the browser you can also use Site Actions, Edit Page, Modify Shared Web Part to hand edit the XSLT,
- going forward, columns added to the list will not be added to the DataFormWebPart.
Your data should now be displayed.
- You could also fix up the display of the data (right align some columns, rename column headings, select color, bold, etc)
Save! At this point you may want to click Save and review the results so far in a browser. Review the formatting and note the things you need to change. This will then be an unghosted, or customized, page.
Now add the Sorting and Grouping options…
- For my example I am first grouping on BikeType, then by BikeSize and BikeColor
Right-click the web part and select Show Common Control Tasks (or click the pop-out arrow at the top right corner of the control The pop-out is named “Common Data View Tasks”).
Click “Sort and Group”
Remove any sort fields listed (usually one)
Add your top most group:
- Click the field (BikeType) and
- Click Add
- Click “Show group header”
- Click Collapse group (optional)
- Click “Show group footer” (if you want counts and totals)

Add your second group by clicking the field (Example: Size) and clicking Add.
- If you stop here you will have groups, but all of your counts and totals will be wrong! We need to create both the text to display for the group and a hierarchy for the grouping. At the second level of our grouping we need to group on the combination of both columns, “Bike Type” plus “Size”
- Click “Edit Sort Expression”
  - At a minimum you will need to concatenate the current grouping column with the previous grouping columns, and while you are at it you can add some formatting:
  - Enter: concat(@Bike_x0020_Type, " - ", @Size) (Watch the capitalization! Use the exact name the Intelisense offers – spaces are “coded” so “Bike Type” is “Bike_x0020_Type”.)
  - Note the preview at the bottom of the dialog box…
  - Click OK
  - Click Show group header
  - Click Collapse group (optional)
  - Click Show group footer (if you want counts and totals)
For the third and following groups repeat the step above with Sort Expressions similar to:
- concat(@Bike_x0020_Type, " - ", @Size, " - ", @Bike_x0020_Color)
- In each new group include all of the fields from the previous groups along with any separators you like:
  concat(@group1field, @group2field, @group3field, @group4field, @group5field, etc)
  or concat(@group1field, " - ", @group2field, " - ", @group3field, " - ", @group4field, " - ", @group5field, etc)
Before clicking OK to leave this dialog box, check the order of the groups. They have probably gotten changed. (bug?)
- Make sure they are in order something like:
  @BikeType
  concat(@Bike_x0020_Type, " - ", @Size)
  concat(@Bike_x0020_Type, " - ", @Size, " - ", @Bike_x0020_Color)
- Recheck each of the sort levels to see if these are still checked:
  - Click Show group header
  - Click Collapse group (optional)
  - Click Show group footer (if you want counts and totals)
Optionally add one more column just to sort the data within the last group. Do not turn on the group header or group footer options for this sort-only field.
Click OK to close the Sort and Group dialog box.
You will probably want to change the Paging options as they default to 100 items.
- Right-click the new data and select Show Common Control Tasks (or click the pop-out arrow at the top right corner of the control).
- Click Paging and pick your options
Tip: If you have a lot of data in the list SharePoint Designer can get really slow. There are two things you could do here, set paging to a small number (not so good for testing multi-level grouping) or from the Common Control Tasks select Show With Sample Data.
If you want grand totals you will also need to click Data View Properties and check Show View Footer.
Save! At this point you may want to click Save and review the results so far in a browser. You should now have all of your groups.

Click the + to expand a section.
Now is a good time to clean up the formatting such as the gray background and odd row height in the group header and footer rows. (Right-click in the row in Design view and select Cell Properties.

(click for bigger view)
Your grouping work is now done. The following steps are only needed if you want to add totals, counts, averages, etc.
Now for totals!
- These steps describe adding totals to the footer rows, but also apply to modifying header (top of each group) rows.
- The Data View sets up the header and footer rows as a single cell with a column span of 99! To display totals in the same columns and the data you will need to fix these rows.
- You will need to fix each of the group footer rows plus the view footer row.
In the Code View do a search for "colspan="99"". In my example there will be seven of these, one for each grouping footer, one for each grouping header and one for the view footer.
Now you need to do a little planning. In my example I'd like to put the "Count=" in a two column spanned cell under the ID and Bike columns. Then I would like to have one cell under each additional column. So something like: <td colspan='2'>count stuff</td><td></td><td></td><td></td><td>total goes here</td>

Steps:
- Find "colspan="99"" for the first group footer row. (Tip: Did you find a header or footer row? Look up two lines and you should see "<xsl:if test="$showheader"" or "<xsl:if test="$showfooter""!)
- Change the 99 to 2
- Find the end tag for the cell (</td>) (down about 16 lines of html)
- After the end tag add the other cells (TD tag pairs), one for each additional column (I need four more)
  <td></td> <td></td><td></td><td></td>
Now to add the total…
- Switch to Design view and you should see the new cells in one of the footer rows.
- Click in the cell where you want the total
- Right-click and select Insert Formula
- Build your formula. For a total double click "sum" in the function list then select your field from the dropdown list. My example needed: sum(@Retail)
- The formula editor does not know one extra piece of information needed here. The @Retail field needs to come from the current group only. To get this, update the formula like this: sum($nodeset/@Retail) ("nodeset" is all lower case)
  Tip: How would you have discovered this? Go look at how they calculated the Count: count($nodeset).
- Notice that the formatting is wrong. The total is left aligned and the wrong size. To match the font and size find the style used for the data in the row above. In my example it looked like this: <TD Class="{$IDADW3HE}">
- Copy the class info (Class="{$IDADW3HE}") into your total cell (the TD tag). The result will look something like this:
  <td Class="{$IDADW3HE}"><xsl:value-of select="sum(@Retail)" /></td>
- The last step is to right align the cell (you can use the toolbar button) and to format the total. To format the number, click in the cell with the total, right-click and select Format Item As… and pick your formatting options.
- Note: SharePoint Designer may get creative and merge your formatting in new styles with great names like style1, style2, style3 etc.. If you are curious, do a search for .style1 (dot style1) and you will find they have placed the style class definition in the PlaceHolderBodyAreaClass master page placeholder.
Fix up each footer row, including the View Footer, the same way. Add the extra TDs, add the totals, counts, etc, and format the results.
Now about the View Footer… and some things to make your head hurt…
- The View Footer is built in its own HTML table, so the column widths are not going to line up with the rest of the list. (I'll have to come back with another article to make these line up correctly).
- The grand totals need a special calculation to sum all of the rows. The @Retail field (or @yourfield) needs to come from the entire set of rows. To get this, update the formula like this: sum(/dsQueryResponse/Rows/Row/@yourfield) (make sure the capitalization is correct!)
  Tip: How would you have discovered this? Go look at how they calculated the Count for the View Footer: count(/dsQueryResponse/Rows/Row) .

Finally all done!

A lot of steps, but it's a fairly routine and mechanical process. Do it a few times and you to will be able to amaze your fellow site owners!

Hopefully the next version of SharePoint will support more than two levels of grouping. Then we can all brag: "Back in the old days I had to … " (But sad to say… 2010 Beta 2 does not!)

11/19/2008

SharePoint: Color Coding SharePoint Lists

Color Coding SharePoint Lists – without custom web parts or server code deployments

SharePoint does not have an out of the box way to set colors based on the data in a list or library. There are both third party web parts and open source projects, such as codeplex.com, that can color code lists, but these usually require installing code on the SharePoint web servers. In this article we will look at color coding lists just using the built-in Content Editor web part and some JavaScript.

Each list will need a slightly different fragment of JavaScript to set the colors.

Color coded calendars?

To see how to use JavaScript to color code a SharePoint calendar see this article: http://techtrainingnotes.blogspot.com/2008/11/sharepoint-color-coded-calendars.html

For other lists and libraries you will find examples below to:

Set color backgrounds for rows in task lists to show the status of the task
Set colors in libraries to show approval status
Set the color of a single column based on its value

To add the Content Editor web part and JavaScript:

Display the view to color code (each view will need its own web part and JavaScript)
Add a Content Editor web part
- Site Actions, Site Settings, Edit Page
- Add a Content Editor web part and move it below the calendar web part
- Move this web part so it is below the list you are color coding
- Click in the web part, click Edit, Modify Shared Web Part, and in the Appearance section change "Chrome" to "None".
Add the JavaScript
- Click Source Editor
- Type or paste the JavaScript (examples below)

Example 1: Set the background color of a row based on data in the row

To color code a row all we need is:

A column to check (such as the Task Lists' status column)
JavaScript to find this item in a table TD tag
JavaScript to assign a style to the TD's parent row (TR)
And of course, the web part described above to hold the JavaScript

Here is the example for a task list:

Code Notes:

x[i] is one of the table cells (TD)
x[i].innerHTML is the contents of a cell (TD) (which may include additional HTML)
x[i].parentNode is the row containing the cell (a TR)
x[i].parentNode.style.stylename is used to set any valid style on the TR

Example 2: Set the color of a field based on data in the field

To color code a cell all we need is:

A column to check (such as the Task Lists' status column or a library's approval status column)
JavaScript to find this item in a table TD tag
JavaScript to assign a style to the TD

Here is the revised JavaScript fragment for a color coding a single cell:

if (x[i].className=="ms-vb2") //find the TDs styled for lists
{
if (x[i].innerHTML=="Completed") //find the data to use to determine the color
{
x[i].style.backgroundColor='darkblue'; // set the background color
x[i].style.color='white'; //set the font color
}
}

11/18/2008

SharePoint: View the XML used in SharePoint web parts

View the XML used in SharePoint web parts

I was customizing the XSLT for a Data View web part and wanted to see what the XML looked like as delivered to the web part from SharePoint. The following is one way to see this. (There are probably better ways.)

Create a new view in your site. A simple standard view with the columns you need selected will do.
Open SharePoint Designer and open your site.
Expand Lists and find your list and then expand the list and find the new view.
Double click the view and display in Design view.
Note: the view is now being displayed using a ListViewWebPart and can only be customized from the browser ("Modify View")
Right-click in the middle of the view and select Convert to XSLT Data View.
Note: the view is now being displayed using a DataViewWebPart and can only be customized from SharePoint Designer or by editing the XSLT from the browser ("Modify View")
Right-click the Data View web part and select Web Part Properties.
Click the XSLT Builder Button (big name for the "…" button)
Delete everything starting with "<xsl:decimal-format NaN="" />" down to, but not including "</xsl:stylesheet>".
Just before the "</xsl:stylesheet>" tag enter:
<xsl:template match="/">
START
<xsl:copy-of select="/" />
END
</xsl:template>
Click Save, OK and then save your changes to the page.
In the browser navigate to this view (which will look kind of empty), right-click and View Source.
All of the text between START and END is the XML returned by SharePoint. This is what your XSLT needs to convert to HTML. Paste this into your favorite XML / XSLT editor and have fun!

SharePoint: How to hide the right web part column

Updated! (3/17/10) Yet another update! “Erik” pointed out that if you have a Content Editor Web Part on the page that has a table with the pattern “<td> </td>” then the code below also changed the table. (not good!) “Erik” offered an interesting solution, but I got to thinking about it and there was a much better solution. So… there is new JavaScript below… (Erik, I approved your post, but it looks like Blogger “ate it”, maybe because it had code.)

Updated! The following JavaScript only really worked if you placed it in the column to be hidden, which of course made it a bit hard to remove later on. One of the comments below got me to looking for the best way to launch JavaScript routines in a SharePoint page. This blog article by Mart Muller seems to show the best solution, and I have modified my example below to include this better solution. You can now place the JavaScript and the CEWP just about anywhere and it will work perfectly. (The changes are in BLUE.)

How to hide the right web part column.

The Team Site template creates a home page with two columns, one 70% wide and one 30% wide. Actually there are two more columns, one between the web part columns and one to the right. Here are two techniques to solve this problem:

Using SharePoint Designer

Open the site
Double-click on default.aspx
In the Design view click in the right web part zone column and then select Delete Columns from the Table menu. Repeat for the spacer columns. Change the width of the left column to 100%.
Save and then test the site in the browser.
Tip: You can just as easily insert new rows and columns and then add new web part zones from the Insert, SharePoint Controls menu

Using a JavaScript "hack"

SharePoint Designer is not needed here, only the Content Editor Web Part.

The trick is to identify the columns to hide. It turns out only two have widths of 30% and 70%.
Add a Content Editor web part to either zone. Modify the web part and click Source Editor.
Paste the following JavaScript code (changes column widths and hides the other columns):

The new version:

<script>

function HideWebPartZone()
{
  var x = document.getElementsByTagName("TD")
  var i=0;
  for (i=0;i<x.length;i++)
  {
    if (x[i].width=="70%")
    {
      // left column
      x[i].style.width="100%"; 

      // center (otherwise empty) column
      if (document.all) // is IE
        var x2=x[i].nextSibling;
      else
        var x2=x[i].nextSibling.nextSibling;

      x2.style.width="0";
      x2.style.display="none";
      x2.innerHTML=""; 

      // right column
      if (document.all) // is IE
        x2=x[i].nextSibling.nextSibling;
      else
        x2=x[i].nextSibling.nextSibling.nextSibling.nextSibling;

      x2.style.width="0";
      x2.style.display="none";
      x2.innerHTML=""; 

      // right margin column
      if (document.all) // is IE
        x2=x[i].nextSibling.nextSibling.nextSibling;
      else
        x2=x[i].nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling;

      x2.style.width="0";
      x2.style.display="none";
      x2.innerHTML="";

      //all done
      return;
    }
  }
}


_spBodyOnLoadFunctionNames.push("HideWebPartZone")
</script>

The old version:

How about just setting the columns 50/50?

Add a Content Editor web part to either zone. Modify the web part and click Source Editor.
Paste the following JavaScript code:

function ReformatWebPartZone()

{

}

_spBodyOnLoadFunctionNames.push("ReformatWebPartZone")

</script>

11/03/2008

SharePoint: Color Coded Calendars!

Updated!
Note: If you got the Month view working and the Week and Day views did not then make sure that when you customized the view that you changed all three:

Change the field used for the Month View Title AND Day View Title AND Week View Title to "CalendarText"

For the SharePoint 2010 version of this article click here!

To add “strike-out” for canceled events click here!

For color coding other SharePoint lists click here!

Can a calendar display in color?

I have been asked a few times if the SharePoint calendar can display items in color. It turns out this is not too hard to do. The basic steps are:

Add a column to the calendar list to pick the color
Add a calculated column to create the HTML to display the color. This can be done with HTML or CSS. This example uses "<FONT COLOR=".
Add the new column to the view
SharePoint will convert the "<" character into "<" so we need to add a little JavaScript to convert it back. The easiest way to add the JavaScript is with a Content Editor Web Part

Sample:

Create or open a calendar
Add a new column named "Color" (Settings, List Settings) – most likely type will be "Choice" with choices like "Red, Green, Blue, Black", but this could be a lookup or a single line of text.
(See here for an HTML color chart: http://www.w3schools.com/html/html_colornames.asp)
Add a new column named "CalendarText"
1. Column Type is Calculated
2. Equation is:
  ="<font color='" & Color & "'>" & Title & "</font>"
3. Data type returned = single line of text
Modify the existing view, or create a new view such as "Color Calendar"
1. Change the field used for the Month View Title AND Day View Title AND Week View Title to "CalendarText"
2. Save and exit (The HTML for "<FONT" will now be displayed. Some JavaScript will be needed to fix the HTML tags)
Add a Content Editor web part
1. Site Actions, Site Settings, Edit Page
2. Add a Content Editor web part and move it below the calendar web part
3. Click in the web part click Edit, Modify Shared Web Part
4. Click Source Editor
Paste this JavaScript:

<script type="text/javascript" language="javascript">
var x = document.getElementsByTagName("TD")
var i=0;
for (i=0;i<x.length;i++)
{
if (x[i].className.substring(0,6)=="ms-cal")
{
x[i].innerHTML= x[i].innerHTML.replace(/</g,'<').replace(/>/g,'>')
}
}
</script>
Click Save, OK, Exit Edit Mode
Add a new calendar item and select a color… Color calendars!

Some thoughts on changing the background color and not just the text color…

Just change the FONT tag to a SPAN tag and use a style. Example:

="<span style='background-color:"&Color&"'>"&Title&"</span>"

This works, but the background is for just the width of the text, not the entire table cell. The closest I’ve come is to hard code the width:

="<span style='width:120px;background-color:"&Color&"'>"&Title&"</span>"

This looks good in the Month view, but not so good in Week or Day views.

You will probably want to use colors like lightblue, lightgreen, tomoto (light red would just be Pink!), etc.

11/01/2008

SharePoint History

Here's a nice history of SharePoint and its related products: Joining Dots: Blog: SharePoint History

Pages