At the end of each EndUserSharePoint.com workshop, I request feedback from the participants to see what really worked for them, what they can put to use immediately. I thought it would be interesting to show some of the comments so people can get an idea on what can be accomplished in a few days of very focused training.

Reading this feedback from the classroom participants helps me hone the online workshops so there is no extraneous material that can’t be used in a real world situation.

This is feedback from the Site Collection Administration Workshop:

• The hands on, case study exercises gave us an opportunity to learn to build, break, fix, to get desired end results. I was skeptical as to why I needed another 3 days of training on SharePoint. It is clear that this class is very much needed, and provides critical information. — Jeff
• [Found most useful…] The fact that it did not provide merely concrete examples but rather guidance, tools and examples for thinking out how to munipulate the system to best serve individual needs.- Heidi
• Mark has an interesting style. It’s borderline sharp-edged: he’ll pick on people to answer questions, and sometimes can be a bit pointed in his humor. It keeps you on your toes! — Dirk
• [Found most useful…] Real time interaction with Mark Miller who is very knowledgeable himself as well as can route to exactly whom on his team has further domain expertise. — Alan
• Repetition of the same process for creating sites over a couple of days was very helpful to drive things home and get comfortably acclimated with the environment and where things live. — Jason
• [Found most useful…] To see the start-to-finish real-world application of a site collection from plan to finished site collection. You’ve got me thinking - i like that. — David
• [Mark Miller] provided a large overview/map of each area we were going to be learning. This helped me to continually conceptualize what we were doing, why and where we were going. — Leslie
• [Found most useful…] The deep drive into content type usage and look up lists. The architecture of the site within MindJet before doing any implementation. — Lynn
• [I] appreciated the thought process of always pointing to business needs to define why you would use a certain feature, vs implementing technology for technologies sake. -RJ
• I’ve been mucking around with various aspects of creating the sites and sub-sites — really feels very good to be able to put all the pieces together at the right level. — Amy

The Site Collection Administration Workshop is one of the most enjoyable workshops I deliver. People who come already have experience with SharePoint and have real world problems that can be solved as part of the flow of the workshop. Context, Context, Context… that’s the name of the game.

Happy SharePointing!
Mark

 

The question of the day comes from George

“Users submitting InfoPath forms to a library must have contribute permissions. How do you prevent these users from viewing and deleting the forms of other users? “

Response from guest poster Ryne Crabb

George’s issue is a common one, experienced not only in Form Libraries, but Picture Libraries, Document Libraries and all types of lists as well. When editing the Advanced Settings of a SharePoint List, you can limit the read/edit access of each item to the user who created it. Unfortunately, in SharePoint Libraries, which oddly enough are just a specialized list, these options are not provided OOTB.

I ran into this issue when developing a solution to handle Expense Reports. Obviously, every user should be able to see their own forms, but not the forms submitted by their colleagues or management. Certain users, in Corporate Accounting, for example, need access to see all forms submitted by all users.

How can we create a query-hacker proof solution to satisfy the various needs of this issue? Here’s the no geek speak solution for a frustrating feature, or lack thereof, of SharePoint Libraries.

Modify the default view.

Set up the library so that our users can only see the documents, forms, or pictures they uploaded, or created, to the library. You can access the settings of any view by selecting the drop-down box next to the view name in the upper-right hand corner of the library. From this drop-down, select “Modify this View.”

On the modification screen, scroll to the middle of the page, at the “Filter” section. Make sure that you have selected “Show items only when the following is true:” and then select “Created by” from the first drop-down box. Leave the second drop-down box as its default, “is equal to” and then type “[Me]” into the text-field.

The [Me] value is a dynamic variable that represents the current user signed in to SharePoint; this way, when I am signed in, it will only show the documents created by Ryne Crabb. However, when Mark Miller signs in, it will only show the documents he created, and not mine. [Me] can be utilized in various different ways, including “Assigned To” for a task list or “Approved By” for approval workflow fields.

EndUserSharePoint.com is starting a series of live, online workshops.

The first workshop, on August 21,  will be lead by Mark Miller, founder and editor of EndUserSharePoint.com. "The Fundamentals of Libraries and Lists" will cover basic, End User use of SharePoint for contributing, collaborating and participating in Team Site libraries and lists.

Getting started with SharePoint is usually a trial and error process. This three hour, live workshop with Hands-On sessions, takes the mystery out of libraries and lists by going through step-by-step processes for contributing and using information stored in SharePoint libraries and lists. During the workshop, attendees have Contributor access to the lists and libraries used in the demos and are encouraged to participate in the Hands-On sessions.

For a complete description and agenda, go to: http://www.RegOnline.com/EUS

 

Mark Miller, Founder and Editor
EndUserSharePoint.com

KPIs (Key Performance Indicators) are a source of consternation for most beginning users of SharePoint, but also a source of joyful relief when used by experienced users to help manage massive amounts of data needing analysis for action items.

That’s a mouthful, deserving of a multi-part series.

This is Part 1 in a series of articles on using KPIs. The articles will contain step-by-step processes for creating KPIs as well as case studies on how you can immediately put them to use in your SharePoint site. It is assumed that the site has the KPI custom lists and the KPI dashboard web parts activated.

What is a KPI and why would I use one?

KPIs are a visual way to see the status of things you are trying to measure. Think about the ways you measure your job performance, customer satisfaction, time to response for handling issues… anything used to measure effectiveness in your processes. Get started by writing down on a piece of paper the things you use to gauge if you are meeting goals.

As an example, you might be responsible for handling project management issues. If those issues are kept in an issue tracking list, you might want to gauge your effectiveness in handling those issues by setting goals:

1. How is the team is doing for processing open, high priority issues?
   • Less than 4 open, high priority issues is the goal
   • 5 to 7 is acceptable
   • Over 7 open, high priority issues is an immediate problem
2. What percentage of open issues are high priority?
   • Less than 10% of open issues are high priority is the goal
   • 11% to 16% is acceptable
   • Over 16% open, high priority issues is an immediate problem
3. How many open issues are assigned to each of the IT support technicians
   • Less than 10 issues assigned to one person is the goal
   • 11 to 20 per person is acceptable
   • Over 20 open issues assigned to one person is an immediate problem

Figure out what you are trying to measure and then set the measurement criteria. By doing this, you will be able to create KPIs that will visually determine the critical touch points that are in immediate need of attention. The KPI will dynamically generate an indicator to tell if you are meeting your goals, and if not, where should you start working.

Stump the Panel, a Q&A forum for SharePoint End Users did a soft rollout last week and is now ready for public access.

The forum is moderated by the authors at EndUserSharePoint.com. There are two boards: "End User and Information Worker" and "Site Managers and Site Collection Managers".

Question and solutions pertain to Out-of-the-Box problems SharePoint, no coding or programming. Well, may be some XSLT, but not much.

If you have an audience or team of End Users, a link from your site to Stump the Panel would be appreciated. Also, if you are an advanced user, this is a good chance to get public exposure and show your chops by helping to answer questions.

So how about it. Can you Stump the Panel?

 

Dessie Lunsford
Points-of-Sharing

If you haven’t read through the previous three articles in this series (Part I, Part II, Part III), I would encourage you to read them first as we’ll be continuing on with the use of an Excel spreadsheet developed in each of them.

 

 

Review and Next Steps

In the last article, we created a calculated column which gave us the ability to see if a task had been completed early. This type of information could be beneficial to managers performing an audit of a particular users (or groups) performance in completing items in a timely manner.

This time, we’ll be completing our customization of the "Tasks" list by adding in our final column called "Overdue?".

The Formula

The "Overdue?" column is used to see "At-a-glance" which tasks are currently overdue and display a simple "Yes" or "No" depending on its status (which could assist in the day-to-day management and processing of Tasks).

Compared to previous examples, this column is rather straight-forward in its formula and what it’s looking for, but at the same time it can be somewhat confusing unless you understand the structure and logic of the formula and how it works to produce a specific result.

(See the previous articles in this series to create and setup your Excel spreadsheet.)

Formula in Excel:
=IF(AND(B7<TODAY(),B4 <> "Completed"),"Yes","No")

Once again we see our old friend, the "IF" statement, as well as the "AND" function we discussed last time.

Dissecting this formula, we begin with the "IF" and its three parts:

1. The logical condition to check (is the "Due Date" less than today’s date? AND is the value of the "Status" column not equal to "Completed"?)
2. Value if "True"(display the text "Yes")
3. Value if "False" (display the text "No")

In the formula’s conditions, our first check is looking to see if the "Due Date" is less than "Today’s" date. If it is, the result of the first check is "True". This is "technically" ok by itself, but it doesn’t deal with tasks that haven’t been completed yet, so we need to combine this with a second check to see what the "Status" is of the task.

For the second check, instead of looking to see if a specific value exists, we’re looking to see if it doesn’t. In this case, if the "Status" field does not equal "Completed" then the result is "True" (anything else will yield a result of "False"). Again, by itself this doesn’t really help us because not every task that hasn’t been completed is overdue (it wont be overdue until we have moved past the actual due date).

Combining multiple checks together (up to 30 of them) is a form of what’s known as "Boolean Logic". To accomplish our end result of either "True" or "False" for the combination of these checks, we’re going to use an "AND" statement to merge our two checks into one statement that must be "True" (as a whole) in order to produce a result of "Yes" being displayed (note - the "AND" by itself will only produce a "TRUE / FALSE" result, so by adding it to an "IF", we can then control the displayed output of "TRUE" being displayed as "Yes" and "FALSE" being displayed as "No").

If we use another type of "Logic Diagram" tool called a "Truth Table", we can check each combination of values to see just what results are possible.

Input1 (1st check)	Input2 (2nd check)	Result of each	End Result
Due Date is less than today	Status not equal to "Completed"	TT	TRUE
Due Date is not less than today	Status not equal to "Completed"	FT	FALSE
Due Date is less than today	Status is equal to "Completed"	TF	FALSE
Due Date is not less than today	Status is equal to "Completed"	FF	FALSE

 

The "Truth Table" although depicted rather simply here, can be used in conjunction with advanced calculations as well. In future posts, I’ll dive deeper into the use of this tool to aid in checking your formula logic.

In our formula (and following the examples in the Truth Table):

1. If the "Due Date" is less than "Today" (meaning we’re currently past the "Due Date") and the "Status" field does not equal "Completed", the entire comparison is "True" which results in a "Yes" being displayed (True: True).
2. If the "Due Date" is not less than "Today" (meaning we still have time left) and the "Status" field does not equal "Completed", the entire comparison is "False" which results in a "No" being displayed (False: True).
3. If the "Due Date" is less than "Today" (again, meaning we’re currently past the "Due Date") and the "Status" field is equal to "Completed", the entire comparison is "False" which results in a "No" being displayed (True: False).
4. If the "Due Date" is not less than "Today" (again, meaning we still have time left) and the "Status" field is equal to "Completed", the entire comparison is "False" which results in a "No" being displayed (False: False).

In the above four possibilities, the only result that will display a "Yes" is if we’re past the "Due Date" and the "Status" is anything but "Completed (each of the other three will either mean the task has been completed or we still have time left).

Testing this in Excel is as follows.

Enter a start date in the past and a due date in the future (notice the "Overdue?" column displays "No" indicating the task is currently not overdue):

Change the due date to be yesterday (notice the "Overdue?" column displays "Yes" indicating that we’re currently past the due date and that the task is overdue):

<center

Modify the task again and set the status as "Completed" (notice the "Overdue?" column now displays "No" since the task is completed).

One extra thing to point out in the logic of this column is that it really only applies to tasks that are open. Once a task is completed we’re not concerned with it being overdue anymore, so after its completion it will always display a "No" (we’re using the "Completed Early?" column to inform us of tasks that were completed ahead of schedule, so adding in logic for that information on this column would be redundant).

To get this into SharePoint, we follow the same approach as before:

In SharePoint, go into the "Tasks" list and its settings Page.

Since we’re once again using a "Today" reference in our formula, we’ll need to create our temporary "Today" column that will allow us to use the reference without displaying any errors.

Create a new column called "Today" leaving the rest of its settings as default.

Next, create another new column called "Overdue?", make it a "Calculated" type and paste in the formula from Excel. Before saving, replace "B7" with "[Due Date]" and "B4" with "Status" (making sure to include the brackets [] around "Due Date" so the system will recognize it as a column).

Formula in Excel:
=IF(AND(B7<TODAY(),B4 <> "Completed"),"Yes","No")

Formula in SharePoint:
=IF(AND([Due Date]<Today,[Status] <> "Completed"),"Yes","No")

After you have the new calculated column, we need to go back and delete our temporary "Today" column (as discussed previously, this column was simply a placeholder that fools the system into allowing us to use the name for our formula, so deleting it will now match it up with the real "Today" data in the system, but without throwing an error).

To test our new column, create a new item on the list with a "Start Date" of the (default) current date and a "Due Date" of tomorrow.

Once saved, we can see that our new column displays a value of "No" indicating that the task is not completed and that we still have time to complete it before it comes due.

Our first check is to see if the "Due Date" is less than the current date. In this case, since the "Due Date" is tomorrow (greater than today, not less than), the result is "False". The second check is looking to see if the value of the "Status" column is "less than or greater than" (equivalent to the statement "Not equal to") the text "Completed", which is "True" since the task is in fact not completed. Since both checks are not "True" (first is "False", second is "True"), the entire check is "False" which results in a display of the text "No".

Looking back at our "Truth Table", we see that any combination of the two checks that results in anything other than the two of them being "True" will result in the entire check being "False", which in this case, is our final result and displays the text "No".

To test what other values could be displayed, we simply modify the details of the task to see what the results are.

Set the "Start Date" back a couple of days and the "Due Date" to today’s date (notice how the value remains "No"?).

Since the "Due Date" is not less than today’s date (it’s equal), the first check is "False". The second check however, is "True" since the value of the "Status" field does not equal "Completed", so since both checks do not result in "True", the entire check is "False" resulting in "No" being displayed.

Modify the item again, but this time set the "Due Date" to yesterday (notice how the value now display’s a "Yes"?).

In this example, the first check is "True" since the "Due Date" is less than the current ("Today") date. The second check is also "True" since the value of the "Status" field does not equal "Completed". As a result of both checks being "True", the entire condition is "True" which gives us a displayed result of "Yes" indicating that the task has not been completed and is currently overdue.

Modify the item once again and mark the task as completed (notice how the value now display’s "No"?).

For this one, without even looking at the first check we know immediately that this will display a value of "No". Why? Because in the second check we’re looking at the value of the "Status" field, and testing to see if it "Does Not" equal the text "Completed". Literally, if it equals anything but the text "Completed" it will be "True". Since we’ve set the task as completed, it will now automatically become "False". Because of this, no matter what possible value could be in the first check, the entire (combined) check will always be "False" for the simple reason that the second check is "False". So, once the task is completed it is no longer overdue (logically, this should make sense).

Summary

Putting all of this together, let’s take a quick look at what we’ve accomplished throughout this series and how to make it an effective solution for making the "Tasks" list more useful.

Our five calculated columns we’ve created are:

1. "Date Completed" - Uses the "Today" reference to mark when a task was completed.
2. "How many days left to complete?" - Visual indicator of time left on a task.
3. "Days Overdue" - Advanced version of the "How many days left to complete?" column with better information.
4. "Completed Early?" - Visually indication of tasks completed ahead of schedule.
5. "Overdue?" - At-a-glance view of tasks that are overdue.

Through each of these articles, we’ve worked with multiple functions (some separate, some in concert with each other):

• IF - Conditional statement containing three parts (logical condition to check, value if true, and value if false) that allows us to display a particular result based on a check of a given condition.
• AND - Boolean operator that allows us to check multiple conditions at the same time where each individual check must equal true in order for the whole (combined) condition to result in true.
• TEXT - Conversion function that takes a value and converts it to text in a specified "number" format.
• INT - Math function that simply rounds a given number down to it’s nearest integer.
• ISERROR - "Error trapping" function that gives us the ability to decide what will be displayed if an error is discovered somewhere in our calculation.

And, we’ve also worked quite a bit with the "Today" reference and how to use it in a Calculated Column (despite Microsoft’s own documentation stating that it can’t be done) by using a temporary column to bypass the infamous SharePoint error messages.

In the "Out of the box" implementation of the Tasks list, the functionality is limited, as it only gives us information as to when the task is due, a percentage of its current completion and status.

By adding in our custom columns, we can now see if a task is overdue, how many days it’s overdue or have left, the date it was completed, and whether or not it was completed ahead of schedule. With these new columns, the possibility for custom views begin to surface allowing us more advanced ways of filtering tasks for the users they are assigned to, those that review the tasks, and those that are responsible for reporting and auditing task history after task completion.

(Simple Custom Views)

Example of custom view for assignees:

Example of custom view for mangers:

Example of custom view for auditors:

In future articles, we’ll be covering more functions and more advanced formulas that will give us even more power in how we view data (perhaps even a few more additions to the "Tasks" list).

Till next time…

- Dessie

Suggestions for future posts on calculated columns are always welcome, and if fact are encouraged.

Some of the best scenarios to illustrate are the "real-world" problems that we each face day to day, so if you have an example, an idea you want to explore, or a "Can this be done with a Calculated Column?" question that I can use as the topic of a future post, please submit it as a comment below and I’ll see what I can do to work up a post covering it.

Dessie Lunsford
Points-of-Sharing
“I enjoy figuring out how to get my users to understand what they can do with SharePoint, and “how to do it” – this is probably the biggest hurdle my users have to face.”

Paul Grenier
AutoSponge

Paul Grenier has written a set of articles for EndUserSharePoint.com on performance management, The Road To Performance Management. The first article, Part 1, lays the ground work using a series of "Why" questions to setup the context for SharePoint.

The section included here walks through Paul’s diagram of a SharePoint site collection hierarchy to use for Performance Management.

Because of the number of digrams in this segment, I am including it as a .pdf download instead of the usual posted article format. The download also contains Part 1, so you can use it as a reference. The series will conclude next week with Part 3, "You’ve Failed Me for the Last Time". As always, Paul appreciates your comments and feedback.

Downloadable Resources

• The Road to Performance Management (Article)
• Site Diagram (Mind Map)
• Site Diagram (.gif Image)
• Mind Map Viewer (Free viewer for Mind Maps)

Paul Grenier
Autosponge - A Non-Developer’s Blog About Administering SharePoint

 

Tanya Gegare
Quad/Graphics

Tanya Gegare is a frequent reader of EndUserSharePoint.com. She has offered a solution to a problem when trying to sort within a hierarchical folder structure and was kind enough to document her solution with screenshots.

Problem

I have one document library with two levels of folders. Level 1 has 10 folders that I want sorted A-Z. Inside of each folder I have documents 2000-2008. I want 2008 at the top of each folder (Z-A, 9-0). How can I do this?

Solution

Use the information, or lack of information, in the title column to help manage your sort.

The first level of folders, A-Z, were created with New>Folder within Sharepoint. When titling each folder A, B, C it applies the folder name to the “title” field as well.

Within each first level folder (A-Z) are folders titled 1990-2008. These folders were created in explorer view. When creating and naming a folder in explorer view the folder name is NOT applied to the “title” field.

To sort the first level folders A-Z and the second level folders 2008-1990 (or Z-A respectively), use the title field for sorting. Click on View: “All Documents” >Modify this view

Set sort first by title, then sort by: Name. The first level folders will be sorted by their title (A-Z) ascending. However, since the second level folders don’t have a title it will sort them by their name, descending.

Tada! And you by doing this…you have happy co-workers…

Tanya Gegare
Tanya is a Corporate Trainee with Quad/Graphics and is currently working with the Corporate Change Management team. Her role is to work with a team to implement SharePoint company wide

Paul Culmsee
CleverWorkArounds.com

Greetings, my EndUserSharePoint.com kung-fu students. Paul here again to talk once more about Zen and the art of SharePoint. Now I don’t want to appear all arrogant and pretentious, but for this post you can all call me "sifu" :-). I don’t deserve the title in the slightest but since I am writing it you are all forced to live in my fantasy world for a while :-).

I have previously written extensively on SharePoint project failure. In some ways that particular series is just as much about "thinking SharePoint" as this series, but I really do not want to rehash the content there. At the same time, I must confess I was trying to think of a way to round off this particular series of articles with a nice logical conclusion and was lost for awhile. But after watching Kung Fu Panda, I realised exactly how I can end it. So this post is the last in this series - for now anyway.

The thing about using pop culture references as I tend to do is that there is always a risk that some readers may not have seen the movie or heard the album that I refer to. So, if you haven’t seen Kung Fu Panda yet, I want you to visit this website and watch the trailer, http://www.kungfupanda.com .

Having done that, I now want you to read this article, and picture my voice as one of those old kung fu dudes with the long wispy beards offering riddle-like advice that makes no sense. If you can’t picture that, use Yoda instead.

For those who have not meditated on the other sacred scrolls (i.e articles in this EndUserSharePoint.com series), it is recommended that you unroll your mediation mat, get into position (i.e do the splits) and start on those scrolls before reading this one. We have been attempting to write about the sort of thought process that needs to go into SharePoint.

In Scroll 1, Listen to your Mother, we learnt about the folly of trying to find the panacea; why folders have been around for the last 40 years and learnt of the important kung-fu concept of the "Ikea effect".

Scroll 2, The "Unconciously Incompetent" Ikea Mecaa, expanded on the Ikea effect, and used the plight of the mythical Ikea furniture installer to highlight that "dysfunction" stems from both personality types and learning styles. SharePoint has an effect of exacerbating these differences between people via the sheer choice of features to use and no clear direction on how to use them.

Scroll 3, A Tale of Two Clients, then highlighted the ideal kung-fu master needed to help you tame the SharePoint beast. In effect, Dr Phil with a computer science degree, an MSCE, CCIE, CISSP, MBA and CFA and ECM guru. (Anyone who actually has that combo *seriously* does not have a life, therefore no girl/boyfriend!). We then learnt the importance of running workshops to both educate and understand the motivations of your stakeholders and create shared understanding. Kung-fu is just as much meditation as it is combat. This scroll concluded by comparing two former kung-fu students (client engagements) and the difference that came from an increased awareness of the importance of only fighting when one is ready in body and mind.

Author: Chris Quick
WSS Development

In a previous article in the Exploiting or Being Exploited By SharePoint Search series , we discussed how to create a simple Search Center that will allow you to search your team site for information. We have also discussed managed properties and scopes in previous articles.

It is now time to put all of our resources together to build a more complex search center that can be used to search multiple team sites housed in their own site collections. You will need to have MOSS standard edition to complete all of these steps.

Since this is a project I need to accomplish within my organization anyway, I’m going to document the steps as I complete them myself!

The Scenario

Our Technology Services department is divided into three sub-departments. Each sub-department has an individual team site contained in its own site collection. Each team site is used to collaborate on the various areas of responsibility - in our case this is the ERP management group, operations group and custom application development group. Each team needs to be able to share information with the other teams and resources need to be searchable across all of these sites.

These sites have recently been tied together with a Technology Services Team Site, but information has become difficult to find as it may be stored in the sub-group’s site or on the group team site. I have been tasked with creating a site specific search center that will allow searches only within the Technology Services team sites. It would also be nice if searches could be executed against individual team sites from the Technology Services department.

Planned Solution

I have decided that I am going to build a tabbed Search Center that will allow searches to be conducted against all sites or against a specific site. Since the groups maintain a large library of Visio and MindMap drawings, I want to allow searches to focus on that content to provide better results to the team. For brevity, I’m going to focus only on the Visio Drawings.