Sharepoint and Historical Dependence on Email: Why Outlook Hides Reasons that Make SharePoint Implementations Fail

Guest Author: Daniel Dunne

Here�s a typical path for a SharePoint implementation in your neighborhood:

The organization is distributed: there�s a large contingency of in-office information workers with a smaller, but mission critical, staff distributed in the field. The organization has a complete dependence on email and turns to SharePoint to enhance communication and reduce the email rat race. IT operation has some form of a VPN in place to enable the remote worker�s native Outlook client, and the SharePoint server is only visible from the VPN. Many organizational entities take hold and create powerful sites that organize team and project activities. The field staff, however, stubbornly resists and declines using SharePoint-based resources � for one silly but critically important reason: �I try to use those discussion boards and lists � but they�re too damn slow!� Implementation efforts eventually reach a glass ceiling of participation which never really reaches the potential that SharePoint has for the organization.

What�s happening?

The Outlook client�s native �Synchronize� technology is a novel and very powerful (credit to Microsoft) technology that has historically enabled remote users to work �virtually� anywhere, seemingly, seamlessly. (�While I�m sitting on my long plane ride, I can grind out 75 new email messages to my associates (YIKES!), then when I�m at the hotel I �send/receive� and all my messages!� Never mind that the VPN connections used for that synchronize was about 150Kbps.) Slow, jagged, and inconsistent VPN�s are masked by the outlook client�s robust ability to �send/receive� occasionally over very slow connections. Users don�t mind, much less recognize, that some VPN setups reduce Gigabit connections to less-than-dial-up speeds; you don�t know the difference if you only send/receive at a leisurely, occasional interval. Email, as an asynchronous communication, lets lazy IT operations get away with deficient VPN connectivity solutions.

Flash forward to SharePoint, a synchronous communication tool, that relies on a consistent and navigable HTTP connection between client and server. Connections that are poor, unreliable or inconsistent create negative perceptions with users that are very difficult overcome

What�s been done about this?

The typical approach is to invite in some sales demos for 3rd part SharePoint synchronizing utilities, (�let�s make this SharePoint thing do that same thing the Outlook client is doing!�)   There are many, MANY third party solutions for �synchronizing� sharepoint resources for offline users, but I think of this like people selling maps to avoid traffic jams.  They�re complex, you usually get lost trying to follow them, and you spend more in time and gas getting where you wanted to go, than if you�d taken the direct route in the first place.

And then there�s the Outlook client itself� in 2007 Microsoft created utilities in the outlook client to �synchronize� SharePoint document libraries and lists.  Outlook client-based synchronize approaches are operational, but disorienting to the casual user, (�Outlook is where I go to read email � it�s confusing to me to edit my proposal from within Outlook.�)  Besides, carrying redundant copies of SharePoint content on the exchange server doesn�t seem like a good use of resources.

And now�s there�s the groovy SharePoint Workspace 2010.  The exchange server will probably breathe a sigh of relief, but the user experience is still disorienting.  For its many layers of complexity and local resource consumption, you�ve got to really want to edit/contribute offline to master this solution.

What�s the right thing to do about this?

The right thing to do is to address the root cause � the unreliable VPN connection. This is not a security column, but there are many who can call an HTTPS secured resource strong enough to be internet facing in the first place, (get rid of the goofy VPN!) Either way, benchmark your VPN and go on safari to the places where the field operation goes, (the hotel, the airport, and especially: the client or customer locations!) to determine that your connectivity solution is ready for prime time, and garnishes the respect of new and uncertain users.

Guest Author: Daniel Dunne

Dan is a Mechanical Engineer who has a 20 year career in product development, including product design, (US Patent holder,) program management and consulting Mechanical CAD, PDM and PLM-related software implementations. Dan has lead SharePoint implementation teams (as a direct employee) in several different corporate environments, including his first which emerged from a server he setup under his desk in engineering. Dan has consistently championed and evangelized the user-driven SharePoint implementation as most aligned to Lean Methodologies and does everything he can to promote the Spirit of Continuous Improvement. Dan can be reached at


Essential SharePoint 2010: Overview, Governance and Planning (Book Review)

I had lunch with Sue Hanley at Best Practices Conference last week. She’s one of my favorite people to talk with in the SharePoint Community because of her complete focus on value to the business, outside of any technology piece. When I got home yesterday, I was surprised and pleased to find her new book, co-authored with Scott Jamison and Mauro Cardarelli, waiting on the stoop.

"Essential SharePoint 2010: Overview, Governance and Planning" is a much needed conversation from the business perspective of how to talk about governance and planning when speaking about SharePoint in your company. Tom Rizzo has an interesting comment in the forward. He talks about the perception people had when Tahoe, the first version of SharePoint, was on the drawing boards at Microsoft:

"Many folks, including myself, were nervous about how customers and partners would accept the new technology, especially given that it provided portal, enterprise search, and document management functionality." — Tom Rizzo

The bold part is above is mine. Isn’t that the 30 second elevator pitch we’ve been looking for? One of the big problems of talking with business people is having a short handle to build from. This type of comment is typical of how Scott, Sue and Mauro have approached the content of their book.

The key to a successful SharePoint implementation is the upfront planning. Any IT department can standup SharePoint, or any company can pay for a hosted solution and get started tomorrow. The difference between success and failure is in the planning and governance phase. I like the way Sue says it: "Don’t come to me with your requirements. It’s my job to define the requirements. Your job is to tell me what the business problem is and what a final solution to that problem should look like."

The first section of the book, Planning, is 290 pages. That’s how important the authors think the planning phase is before even popping open the box. Governance, information architecture, enterprise content management, security, planning for training and communications… these each receive their own drill down, helping you to define what is going to be needed to help you run your SharePoint implementation.

The rest of the book handles optimization issues and migration planning. From an End User perspective, this is interesting reading, but the money shot for me is the first part of the book, the planning phase.

This is a book you should seriously consider if you are responsible for SharePoint at your company. There are not enough people talking about real world business issues outside of the technology itself. Scott, Sue and Mauro have put together an essential resource you should have on your bookshelf.

Five stars, highest rating.

SharePoint: Extending the DVWP - Part 32: Filling in Default Data on the insert Template with Multiple DVWPs

Last time, we talked about setting a control to a default value using jQuery. In the example, we were setting the value of a dropdown list on which we had implemented Marc Anderson’s SPCascadeDropdowns. It’s a pretty straightforward process with a couple of caveats depending on which browser your users are using and/or how many items are in your dropdown list.

However, we enlisted Christophe Humbert’s Easy Tabs on this project, too. We did that so we could have a couple of groupings of data on one page, but organized by tabs. They’re basically the same DVWP, just with a different filter. Which means that all of the form fields are laid out the same way regardless of which tab you’re on.

So, if we implemented the script from the last article, every insert template on the page (IOW, there are as many insert templates as there are DVWPs) will get the same defaults. But that doesn’t make sense for most implementations of multiple DVWP.


How do we tell them apart?

ID-entifying the Solution

One of the easiest selectors to use in jQuery is the ID Selector ("#id"). To limit the scope of our default settings, we only have to give each form its own ID and then use that in our set call.

  1. In the SharePoint Designer (SPD) Design pane, click on the left-most column in the header row
  2. 2010-08-28-ExtendingTheDVWPLayoutEnhancement-Part32-02.png
    Clicking on the Employee header highlights the call to dvt.headerfield, which is at the top of the table that draws all of our forms

  3. Just above the highlighted area, the arrow points to the table tag that wraps the entire display area including the edit, remove, and insert templates. This is where we’ll add our ID.
  4. When we get this webpart just the way we want it, we’re going to save it and reuse it for every page we create for the various managers who only need to see their own data. It’s best to give this table a descriptive but reusable ID. If we need to put two very similar DVWPs on the same page, we can give a more descriptive and distinct ID to each when we come to that type of page.

  5. Repeat the process for the rest of the DVWPs on your page, giving each a unique ID.
  6. For tabset above, I used these ID values: ACB_FTE, ARC_FTE, ADC_FTE, PRN, and Other. The first three are an example of importing the same saved DVWP three times, once for each Location in this grouping. For each of those three, I set the filter to that specific location and edited its ID to indicate which Location I was working with.

Now, let’s go back to the jQuery we’re using to set defaults.


To make .val() settings DVWP-specific, we’ll just add the ID at the beginning of the jQuery selector:

In my case, I’ll also need to make copies of these two lines for each additional DVWP on the page:




A note about the way jQuery works: The above script does not say "find a specific variable and set its value". That would result in an error if the variable wasn’t defined. Instead, it says "for every match you find on this selector, set its value". If it doesn’t find anything, no harm, no foul; it just moves on to the next statement.

Likewise, the following call to SPServices.SPCascadeDropdowns() does its work on every dropdown it finds with the name Location, so it will work for all DVWPs on any given page. Since they’re all using the controls with the same name, we only have to include the cascade call once inside the .ready() function.

One caveat to that, though: It is  possible to have the insert template (or the edit template, for that matter) open on several tabs at once. You’ll only see one at a time, but they’ll all be open. The cascade will not work with more than one set of the dropdowns open. Ninety-nine times out of a hundred, the user won’t open an insert or edit template without clicking Save or Cancel, before clicking on another tab. But for that one time they do, I wanted you to know what the issue was.

Default the Second Tier of a Cascading Dropdown

You probably noticed two things in the screenshots above:

  1. I didn’t show you the Other tab, and
  2. The PRN tab had the second tier dropdown set already

In the first place, I don’t default the top tier for the Other tab because I’m not sure what it will be. I do, however, default the second tier for it.

In the second place, for PRN, I know which of the three Locations actually has PRN employees, so I default the second tier as well as the first.

So, how do you set the second tier?

Not too surprisingly, same as the first tier.

In the case of the Other tab, I’m setting the Group but not the Location. When the user picks the appropriate "other" Location, the Group will not change. On the other hand, if the user picks a "non-other" {grin} Location, the Group will be blanked out.


If you were counting line numbers, you’ll notice that I set the second tier after the first call to SPCascadeDropdowns. But, the Other tab begs the question:

"Is that necessary for the second tier if I’m setting both?"
No. You can set both before the SPCascadeDropdowns call.

"What if I’m only setting the second tier?"
It still doesn�t matter. SPCascadeDropdowns binds the cascade function to the onChange() event of the dropdown, the actual cascade doesn’t happen when the page loads; only after the parent dropdown has been changed.

Smell That Yummy Dessert

We’re closing in on the finish line in the Extending the DVWP series. See you next time, when we’ll clean up the totals and subtotals.

SharePoint 2010 and Visio 2010: Better Together - Part 1

Guest Author: Wictor Wil�n

This is the first post in a series about SharePoint 2010 and Visio 2010 and how the two products integrate with each other.


I remember when I first saw Visio many, many years ago. It was before Microsoft acquired it from Visio Corporation. My dad was using it to make blue prints of our summer house. As with most of the gadgets and software he buys my dad needs a helping hand, not saying he is not technical, but I tend to catch up on such stuff faster than him, so I learned the basics. I have used Visio since then, during my years in school and university and especially in my job as a developer and architect. (I have also made exact blue prints of our house, including the electrical wiring - call me crazy but I do love that product.) Visio is a great tool for technical diagrams and representations and extremely effective in drawing flowcharts and business processes.

Last summer when SharePoint 2010 and Visio 2010 were let out of the gates in Redmond, I saw how my two favorite products, SharePoint and Visio, finally found each other. Not only as a client integration, Visio was also a service application for SharePoint, called Visio Services. There was no longer need for an Active X control to view Visio drawings in the browser.

Visio 2010, part of the Office family, but not included in the Office suite is a diagramming, drawing and process modeling tool. The latest release of Visio includes new SharePoint integration features such as Business Process Modeling Notation and finally the Fluent user interface (aka the Ribbon and friends). Visio 2010 comes in three flavors Standard, Professional and Premium.

This series will cover most of the connection points between SharePoint 2010 and Visio 2010, starting with the demo friendly workflow creation using the Visio to SharePoint Designer 2010 integration. Then I will show you how to use the Visio client and service application to provide the end users with a visual snapshot representation of a SharePoint workflow. After that we will take a look at how you can use SharePoint 2010 as an effective storage for process diagrams before looking into the BI aspects of Visio Services.

Guest Author: Wictor Wil�n

Wictor Wil�n is a SharePoint Architect at Connecta AB with more than 12 years experience in the web content management and portal industry. He has worked with consulting companies, founded and sold his own software company and saw the dawn of SharePoint back in 2001. Wictor is an active SharePoint community participant, writer, tutor, frequent speaker at local and international conferences, and author of SharePoint 2010 Web Parts in Action. In 2010 Wictor was awarded the SharePoint Server MVP title by Microsoft for his community contributions. He can be found online at Wictor is based in Stockholm, Sweden.


SharePoint: Interactive Charts using Google Visualization API v2.0

14.09.2010 Small update to the code to fix the “Initial state” in motion charts, as well as some minor bug fixes. I have moved the files to my home server for you to download. This provides a better solution then manually highlighting and saving 2000+ lines of code. Please note that the file now come in three different “versions”. Read more in the “download section” below.

09.09.2010 Small update to the description regarding update from v2.0 to v2.5. I forgot to mention another field required to be added to the Chart configuration list: ListBaseUrl. This in addition to the already mentioned “ChartFormatters”. I have also removed an alert that was left in the code (line 40).

Christophe notified me of a problem with the 2010 compatibility in sub-site “homepage” – which is a wiki page. The CEWP HTML handling in SP2010 is a bit “different” than in SP2007. When editing the HTML source code, it actually pulls the contents generated by the script into the edit dialog and does not use the content that was originally saved to the CEWP… This is a bit of a headache!. A workaround for the wiki-pages is to use the “Content Link” property for the CEWP and link to a .txt file with the CEWP code placed in a document library (alongside the other scripts).

06.09.2010 Updated the script “ChartUsingGoogleVisualizationAPI.js” and the CEWP code. The version number is notched up to 2.5 and the changes are:

  • Added the ability to chart data from all ascending parent sites and all first level subwebs of current site
  • Added a few formatter options in the UI – refer the linked resources for instructions (thanks to Paulo for the tip).
  • Added new “action” for formatting as % (value*100).
  • The solution is now SharePoint 2010 compatible
  • Enhanced the UI for editing charts – all options and columns are now directly editable.
  • No more need to specify the “userListGuid” and the “userListBaseUrl” in the CEWP code.
  • The naming of the charts in the Chart Configuration list is enhanced – it now prefixes the chartId with “location.pathname” to prevent overwriting an existing chart when reusing the CEWP code in another page (thanks to Christophe for the tip).
  • Edit chart is now done in the standard “Browse mode” and not in “EditPage mode”. The edit button is found in the top left corner of the chart. The button is only visible for users with “Edit page rights” and does not show on printouts.
  • And more…

Note: This update introduces a new column in the configuration list (GoogleVisualization_InteractiveChartsConfig). To reuse the existing Chart configuration list you must add one field of type “Multiple lines of text (Plain text) named “ChartFormatters”. Optionally you can delete the Configuration list and have this solution automatically recreate it for you.

Please note that the CEWP code has changed alongside the code for the file “ChartUsingGoogleVisualizationAPI.js”.

Note about existing charts:
All your existing charts will need to be updated with the new “page id” in the chart configuration list (GoogleVisualization_InteractiveChartsConfig). Look at the new configuration item that is automatically added – copy it’s name and update your existing chart configuration – while deleting the newly added (empty) chart configuration.

Since I posted the previous version of the solution that utilizes the Google Chart Tools / Interactive Charts (aka Visualization API), Google has updated their API and made the previous version more or less obsolete.

NOTE: I have made a “bridge” between Google’s Visualization API and SharePoint. How the charts render or function is up to Google to decide – it is their “product”. Refer to the various configuration options by following the link from the “Edit chart UI”.

Read Google’s terms of Use here.

Google’s Data Policy on these charts:
All code and data are processed and rendered in the browser. No data is sent to any server.

This new release features these chart types:
Area chart | Bar chart | Column chart | Gauge | Geo Map | Line Chart |
Map | Motion Chart | Organizational Chart | Pie chart | Scatter Chart | Table

Other modifications made:

  • All code in one file: “ChartUsingGoogleVisualizationAPI_v2.0.js”
  • In the CEWP: Refer to the above file, jQuery (fixed external link) and the Google jsapi (fixed external link)
  • Chart configuration list is automatically created if it is not already present

The solution is tested in IE8, Google Chrome 5.0.375.127 and Firefox 3.6.8.

Some screenshots:

Not all chart types are presented.

When first adding a chart CEWP to a site, the configuration list is created:

Press OK:

Gauge – a good KPI:


Column chart – counting unique values per country:

This chart type merges all “lines” where the country is the same, counting “fruits” in the choice column named “Fruit or vegetable”.


Motion Chart:

The “date” column can be a SharePoint data-column, or a “US formatted” string (still formatted as “Date in the UI”) in the format “mm/dd/yyyy”. Refer to the “Instructions for MotionChart” link in the UI.

Organizational Chart:

This is the data that makes up this chart. Note that it is built with various calculated columns – no need for it to render as HTML in the list – it is “raw data”.


More examples – including custom CAML-queries will be added on request.

The Code:

This is the CEWP code:

<div id="MyChart1"></div>
<script type="text/javascript">
		Address all containers
// All charts must be represented by a container with a unique id. This container  must be present in the page
arrOfChartContainers = ["MyChart1"];
<script type="text/javascript" src=""></script>
<script type="text/javascript" src=""></script>
<script type="text/javascript" src="/test/English/Javascript/ChartUsingGoogleVisualizationAPI.js"></script>
Note the <div id=”MyChart1″></div> This is the chart “placeholder”. To add more charts, add more placeholders and add them to the array “arrOfChartContainers”.

The placeholders can be inserted in other CEWP’s as long as they render before the chart is initiated (place them above the “Chart CEWP”).

It is essential that the scripts tags for “jsapi” and “ChartUsingGoogleVisualizationAPI.js” are placed below the script tag that defines the “arrOfChartContainers”.

The code for the file “ChartUsingGoogleVisualizationAPI_v2.0.js”

This link provides you with three different “versions”:

  • ChartUsingGoogleVisualizationAPI_vX.Y.Z.js – Uncompressed code
  • ChartUsingGoogleVisualizationAPI_vX.Y.Z-min.js – Minified using YUI Compressor to reduce the file size
  • ChartUsingGoogleVisualizationAPI_vX.Y.Z-packer.js – Compressed/obfuscated using Dean Edward’s Packer to further reduce the file size

You must update the script name in the CEWP to reflect the version number.


When new versions are released, they will be placed in a folder with the version number as label. Be sure to download the latest version.

If you are using a browser other than IE, right click the file and select “Save link as” or “Save linked content as…”.


To make it convenient for end users to use this charting tool, create a few CEWP’s with your favorite setup (1 placeholder, 4 placeholders in a table form and so on). Export the CEWP and upload it to your web part gallery (in the root of the site collection).

You can now add it as a regular webpart from the “web part selector” in edit page mode.


SharePoint: Extending the DVWP � Part 31: Filling in Default Data on the insert Template with jQuery

Author: Jim Bob Howard

We’ve been building a solution for a division to keep track of its full-time employees. We’ve rearranged the layout of the DVWP, reprogrammed the form action links and even passed them some workflow variables, added cascading drop-downs, created an alternate edit template so that we can record edits AND deletes to an audit list.

Several pieces are beginning to fall into place as we draw nearer to the culmination of the Extending the DVWP series. We now have multiple pages (one per location/manager). Each page can have multiple DVWPs, either because multiple locations are managed together and/or because we keep track of "other" types of employees in separate counts.

As the title indicates, this is Part 31 in the series, so those who have been following along have been at it for several months. If any part of the above seems like Greek (and you don’t speak Greek), scroll down to the bottom of this article until you find "View all entries in this series" and click on the link to see them all.

Simplifying the User Experience

Now that we have separate pages for each location, it probably makes sense that the manager who accesses each page will only be adding new employees to his or her own location. So, let’s set the location by default based on which page we’re on.

Of course, we’ll use jQuery:

  1. Switch to edit mode on the DVWP page, by either
    1. clicking Site Actions -> Edit Page, or
    2. adding ?PageView=Shared&ToolPaneView=2 to the page URL
  2. If you’ve been following along and are using cascading dropdowns, open the existing Content Editor Web Part (CEWP) and let’s consider how we might set some defaults.
  3. (Note: If you haven’t added cascading dropdowns, skip down to Step 3, ignoring the dropdowns, but looking at the code on lines 6-7.)

    Here’s what mine looks like:

  4. The first SPCascadeDropdowns call is to cascade the Group from the Location. Let’s set the Location first so it can limit our Group dropdown based on our default Location setting:
  5. It looks like we’re setting it twice, but actually we’re dealing with two different methods for drawing the control: one for Internet Explorer (IE), one for Firefox (FF). (Note: I’ve not tested this on Safari or Chrome, but I would expect these two alternatives to cover the bases for a dropdown list.)

    1. Setting the default in FF is pretty straightforward: find the select and set the .val(). Firefox takes care of the rest.
    2. In IE, it works a bit differently. If the list is over 20, IE draws it differently: as an input control with some other controls programmatically attached.

    3. For the times when IE draws it as an input, we have to "click" the dropdown arrow to get it really set for use by the form, and especially the cascading dropdown call.

      In this case, after we set the .val(), we look for following sibling that is an img control (.next("img")) and the trigger its click event.

  6. For the main Location tab, that’s all we’ll do to default. But one more thing will ensure that things work well for IE when it draws the dropdown as an input:
  7. This will set the focus back to the Employee text control to avoid leaving the programmatic dropdown on the screen.

Houston, We Have a Problem

The above script works great when there’s only one DVWP on the page that has a control named Location. But we’ve been working under the premise that there is more than one DVWP on this page.

If we implement this script, it will set the Location to "ACB" for every insert template generated on the page. In the case of the alternate kinds of employees, we actually want the Location set to the kind, and their Group set to a "home site."

(Ex. A paramedic that picks up shifts among clinics, as needed, is considered a PRN. Even though he may be working at a different clinic each day, one of them (ACB) is considered his home site. For this implementation, we want to consider his Location to be PRN so that we can count the total PRN headcount separate from those who are FTE’s at the same Location. So, we use the Group to indicate his home site of ACB.)

But that won’t work with the above script.

Next time when we continuing Extending the DVWP, we’ll look at a way to know which DVWP we’re working with, so we can set the defaults accordingly.

Author: Jim Bob Howard

Jim Bob Howard is a web designer / webmaster in the healthcare industry. He has been working with SharePoint since March 2009 and enjoys sharing what he has learned. He is a moderator and frequent contributor to Stump the Panel, and answers SharePoint questions on Twitter (@jbhoward) and via email (


Hi all

I am having some issues with the scheduling start date and end date on pages library on MOSS2007-SP2. It reached the preset end date but the page is still on show. The same problem with the start date, the status of the page is Scheduled which is correct when the start date is a future date but the page has already been made live and the public can see it.

I have done all of what i found here and here but still no luck.

I suspected that something wrong with our environment. i have tried this on 2 of our SharePoint servers (all with MOSS2007-SP2, WSS 3.0). non of them work. It does not seem to do anything

Any help is much appreciated


SharePoint: Filtering with Web Part Connections

Have you ever noticed the ‘Connections’ option on web parts and wondered what it was meant to do?  Have you played with it a bit, tried connecting one thing to another only to have nothing happen?  Did you do a web search to try to figure it out and come back with results like "Connection Interface Pair: ICellProvider, ICellConsumer" and then give up, just to assume that was something you needed Sharepoint Designer to master (and you don’t have Sharepoint Designer?)  Welcome to the club!   

Turns out creating a ‘connection interface’ between web parts is really simple; the hardest part is visualizing the purpose!  It seems to me the best way to get the idea across is just to throw a few examples out there, run through the basics and hopefully that will be enough to get your wheels rolling with the concept.  

Below is an example showing an abbreviated view of a list of stats we collect to monitor productivity:

This list actually deals with a number of issues I see people struggle with on Stump the Panel, so I’ll walk you through the capture of the information, just in case it helps to explain some other questions that might arise.

Here is what the New Item entry looks like:

It is best practice to never delete the Title column, so when it really isn’t needed, I just set the default to a generic value, in this case "Today’s Totals" since it has information in it, no one messes with changing it, and it wouldn’t matter if they did.  The date fields all use another default based on a calculated value in a Date and Time column:

Note the use of ‘Today’ in the default value.   Investigation into using [Today] in a calculated column might make the end-user leery of using ‘Today’ at all.  Rest assured though, capturing today’s date as a default value is stable, since it will not change (to a new �today�s  date�) even if the list item is later edited.  Using default settings encourages end-user adoption because it makes filling out the information much quicker (and generally more accurate!)  Using another calculated column, I capture how many days between dates and that looks something like this:

=[Scanning Date]-[Date Entered]
[Scanning Date] is the default of ‘Today’, [Date Entered] is the date we are capturing with a default of T-1, but it may be edited to hold other dates when holidays need to be factored in.

The other fields are quickly completed by entering the number of folders they need to record for the day.  Terrific, easy to fill out form, now we are capturing data, looks good! 

Then management asks for monthly average reports on those folder numbers.

Filter to just a month?  Of course 12 different views of the list can be created, each filtering on the name of the month.  No matter how we go about it, first is to create a column that returns the name of the month.  Using a calculated column, that is pretty easy to sort out and looks something like this:

=TEXT([Scanning Date],"MMMM")
This formula converts the date entry into text.  Using MMM will result in abbreviated month names, MMMM gives the text name in full.

Sharepoint handles averages with the aid of ‘Totals’ that can be created under Modify/Create View.   Notice that some of the columns listed below will only support averages in datasheet view.  That is because they were built on calculated columns.  Web part connections also work in datasheet format but all connected web parts must be displayed as Datasheet.  

Here is where it gets interesting.  Create a second custom list called "Filter by Month."  Edit the list in Datasheet format to quickly enter the text names of the 12 months.

Below I’ve added both lists on a web part page.  It will work on the default list page as well; just add the second list above the first. 

In order to see ‘Connections’, the page must be in Edit Mode, so either Modify Web Part, or choose to Edit the Page.  Choose Connections > Provide Row To.   ‘Get Sort/Filter From’ is the other option and it works much the same way as I describe here only in reverse.

There are only 2 lists here, so the only option of making a connection is the Scanning Stats list.  The need is to sort by month, which is the Title column, so use the Webpage Dialog and pick Title.

Next pick the column that contains the same data values as the first column, which is the Months column that lists out the full text names of the months.  Both columns must be visible on the screen in order to create the connection.

Click Next (or Finish) and check out the Filter by Month list:

Choose the month to filter on by clicking within the circle.

Notice the filter icon showing above the Months column; it is pretty obvious what is happening there, just as if we had filtered using the drop down options at the top of every column. 

Very cool, but there are few oddities I’ve discovered. First of all, the list can no longer be changed to Datasheet format, so if that is an option regularly accessed, consider building the connection with a datasheet to start with.  Notice in the example below, connecting the same web parts as above but with datasheet views, that the circles to choose are no longer there.  In datasheet views just click the column entry to filter.  Also take note that the averages of the calculated columns are also present.

These datasheet views with web part connections open up endless filtering options.  Datasheet views allow instant entry creation, so any data can be instantly entered in the Filter list and then click it to filter the list on that value.   

Another odd thing:  unless we navigate away from the page and re-enter, there seems to be no easy way to go back to an unfiltered view of the list.  The URL holds the secret of what is up with that. I have found that providing a link to the .aspx page is all that is needed.  How?  Grab the URL from the page where the connections are placed prior to initiating any filtering.  Add a Content Editor Web Part and add a title in text that indicates what clicking there might do.  Add a hyperlink to the text using the URL you just copied and hide the title of the web part (to clean up the appearance.)  I added the link below the filtering options:

One other discovery:  I got stuck in a loop of never ending error messages when I tried to use the filter column in Preview Pane view.  It works fine if the list holding the data is in preview pane, so keep that in mind.  

You don’t need to use a separate list to create web part connections. 

Maybe you need to display a most recent date.  In this example I’ve used a variation of the month calculated column to return the most recent month and year.  I called it Month, it looks like this:

=TEXT([Scanning Date],"yyyy-mm (MMM)")
This forces the year display so we can be sure of the most recent year.

This time add two of the same web parts to the page.   I’ve modify the view to ‘group’ the Month column, this makes the most sense for my entries.  It is possible to do the same thing with ‘modified’ if you want to group by a day.  I’ve restricted the results coming back on my column I plan to filter with these settings:

Making sure I have the two ‘Month’ columns both displaying in each list, I create the connection between them.  Because I have the filtering web part set on 1 group to return, those are the only results appearing in that filtering column and users can pick the specific date by expanding the grouped items.   Filtering on recent date has proven a real challenge to me in the past without the use of Sharepoint Designer.  

Connect multiple web parts and filter them on a common value.

Lastly, one more example and the origins of this connections discovery: my project management template.  If you haven’t yet been turned on to Dux Raymond Sy, check out his project management resources on his blog MeetDux.   Dux is passionate about getting the word out on using Sharepoint for Project Management, he spoke, I listened, and every one took notice. 

Fourteen of the web parts shown in the picture to the left represent the lists from my original project management template and detail requirements capture for our PM process.   All that was needed was to create a site based o the template that included these lists and some base content.  Management was thrilled with the results.  So thrilled, it turns out, that they (and the project managers) wanted to use the same method for smaller projects.  I have to admit, I had been stumped as to how to do that (again completely out of the box) until it occurred to me how to use web part connections to get the job done. 

The first web part here is the key.  I created a ‘Project Name’ list.  This list captures the project name, the project lead, and a yes or no column asking if the project is to be archived.  The archive column allows me an easy way to filter projects off the list when they are no longer active.

Then I needed to create a site column which references this Project Name list as a lookup column. 

From there I added the new site column to every one of the original 14 lists.   Adding all the web parts to a web part page, making sure the new column was visible in each, and then creating the connections to each was all that was needed.

But imagine scrolling through that mess, even with connected web part filtering you might be thinking that can’t possibly be enjoyable for the user.   Christophe from Path to Sharepoint provides the ideal solution with Easy Tabs; and they work perfectly with web part connections!  Someone recently said via Twitter "If you aren’t using Easy Tabs, you are missing out!"  I totally agree, best feature I can think of to expand my Sharepoint solutions. 

The end result:  (click to see full size)

Users can filter by project and click through the tabs to see only the information that pertains to that project.  Now we have nearly the same project management solution for smaller related projects as we do for our larger ones.  This in turn increases site usability by keeping all the elements in a familiar place and it eliminates rework since information for one related project can be assigned to another project by simply adding that project name to content.

Hopefully these examples will get your juices flowing on ideas for using these odd little connections.  Tell me about how you are using them; I’d love to hear from you.  Leave a comment! 

SharePoint: Extending the DVWP - Part 30: Using EasyTabs with Filtered DVWPs to Make Data Manageable

If you’ve been following along with the Extending the DVWP series, you might be wondering, "Why is he doing all of this?" The whole point of this project was to make things easier for managers to update the number of full-time employees in their department. The administrative assistant who is collecting that data for reporting needs it all in one Excel spreadsheet to run some percentages, etc.

But the managers themselves only need to update their own site, so they don’t need the Excel spreadsheet with access to the other 14 locations just to be able to update their own.

Making it Easy with EasyTabs

Now that we have this robust DVWP, wouldn’t be great to break each location out into its own tab so the managers can just edit their own?

Well, no it’s actually not that great. There’s so much data that the overhead involved to load it all in multiple tabs, so that a manager can just pick their own tab, is completely overwhelming. Plus, in my requirements, several of the managers are responsible for staffing at multiple locations. Additionally, there are categories of employees that are not "really" full-time employees, but count as a fraction of a person, or not at all but still need to be maintained in the same list.

All that to say, that the better implementation for our purposes is to create multiple pages with multiple (though a subset of the whole) tabs.

Like this:

A page with one tab

A page with a few tabs

A page with several tabs

… etc.

So, what I’m going to do is use Christophe Humbert’s EasyTabs to stack multiple DVWPs on a page, each filtered on a subset of the FTE data.

Easy Part - Filtering

To filter a DVWP:

  1. In SharePoint Designer (SPD), hover over the DVWP in the Design pane and click the chevron (right arrow), and click on the Filter: link.
  2. This will bring up the Filter Criteria dialog box:
  3. To add a new filter clause, simply do as it says and "Click here�"
  4. Select the Field Name you want to filter, the comparison you want to make, and the value
  5.  2010-08-19-ExtendingTheDVWPLayoutEnhancement-Part30-07.png

  6. Click OK and your DVWP will be filtered.

Let’s do one more filter and then we’ll stack them with Easy Tabs.

More Complicated Filters

In my implementation, I have several categories of employees that count as a portion of an FTE, but aren’t supposed to be included with the rest; they’re all supposed to be grouped together. Here’s how I filter them:

Location columns are grouped

Notice that all of the Location filters are OR’ed together and grouped. The result of that is AND’ed with Group = ‘ACB’.

To group them:

  1. Click the arrow next to the top clause
  2. Holding down the Shift key, click the arrow next to the bottom one
  3. The Group button will now be clickable
  4. Notice that the Filter setting now begins with ( Location Equals ‘Float’ Or �)

  5. Filter: ( Location Equals ‘Float’ Or Location Equals ‘Trainee’ Or Location Equals ‘Temp’ Or Location Equals ‘PT’ ) And Group Equals ‘ACB’

Your filter on this DVWP now will show all list items with Group=�ACB� and Location one of: �Float,� �Trainee,� �Temp,� or �PT.�

The Need for Tabs

Without Easy Tabs, my DVWPs just stretch down the page:


With Easy Tabs, they’re stacked for easy readability and accessibility:


Implementing Easy Tabs is extremely easy:

  1. Download the javascript from Christophe Humbert’s Path To SharePoint site (at this writing, 5.0 beta is the current version)
    1. At the link above, choose your options:
    2. As soon as you check the License box, your version of the code will appear in the box at the bottom of the page
  2. Copy it and paste it into the Source Editor of a CEWP on your page
  3. Click OK and Exit Edit Mode

All of a sudden, all of the webparts in the same zone as the Easy Tabs webpart will be stacked with tabs.

Next time: A little more clean-up of our DVWPs are necessary; we need to fill in default data for the page/tab, activate the cascading dropdowns with default data, modify the Total and Subtotal rows� we’ll cover those over the final few articles of Extending the DVWP.