Thursday, December 3, 2009

SharePoint KPI Best Practices

So, I'm hoping people will see this in google and give me some ideas. I'm trying to figure out some best practices for KPIs in MOSS (SharePoint) 2007. Thus far, it seems best to explain to those new to the topic why I'm looking for this.

A KPI stands for Key Performance Indicator, which basically means this is a report-at-a-glance of something specific in SharePoint. The more you track in SharePoint, the more you can generate these quick pictures that highlight (high-level) how areas are performing and give management a way to analyze the health of their departments, teams, divisions, etc. An example of this would be a task list:

1. You have a task list in SharePoint that shows every task that your team is working on right now and in the past.
2. You are given the responsibility of figuring out how many tasks are over a week behind schedule for every team member. Now, you could do this by sticking a bunch of the data in Excel and then using filtering and conditional formatting to get your data...but that data would be flat/static and you'd have to do a little extra work to make it dynamic...even then, it's not dynamic without a refresh. So, you want to make a KPI.
3. To make a KPI, you use the Site Actions->Create menu and create a KPI list (this is only available in MOSS 2007 and with the "Office SharePoint Server Enterprise Site features" is activated on your site). Form there, you can create a KPI based off of a SharePoint list and then set it to the URL of a view and then either count the number of items or a percentage of items exist there (even add additional filters for percentages if there's a number column involved). You then set whether a higher or lower number is better and what numbers change from an unsatisfactory to "ok" to satisfactory. This KPI list can then be opened directly or else placed on a web-part page in a dashboard style.

This is a common scenario and you may need to report quite a bit of information off of the exact same data. So, let's look at some performance best practices for KPIs:

1. SharePoint KPIs are based off of list views which means that the KPI will only be as good as your view. Views can be optimized by remembering the 3 F's: Folders, file indexes, and filters. Create folders to separate data (but don't let everyone create folders, make them yourself). File indexes involve taking a specific/key column in your list and having SharePoint make a copy of just that column so that it can reference it when searching the list (in views and, by extension, KPIs). You can turn on an index for a column by going to the List Settings and looking under the Column Section for "Indexed Columns"...just remember not to index more than 1 or 2 columns. Finally, filters will help your views keep from retrieving too much data at once (which would then be interpreted by KPIs). Use filters FIRST on indexed columns and THEN on non-indexed columns. Make sure that no view, personal or public, would retrieve more than 2000 items at once. This isn't a hard-and-fast requirement but it's best practice for SP 2007 (2010 raises this limit significantly as it handles your views/queries a little differently). Also, even if you only see items in batches of 5, you can still be pulling a massive amount of data (you just have it set to display them in batches of a certain number) verify the number of items returned when you switch to a certain view.

2. KPIs can be generated through a KPI list and also through calculated columns/data view web parts (see for more details on the latter). Where this is displayed is a key performance difference: if you can display a KPI inside a list (as a column) then you will be able to maximize performance. The reason that this can maximize performance is that each KPI queries a list view...which means that 10 KPIs that use the same view but show slightly different information will re-query the same view 10 times. If you have a column built into the list then you can include a KPI as a part of a specific view (maybe a reporting view).

3. If a KPI collection is going to be on a special page (maybe a dashboard for management), then there are some factors to think about: the number of KPIs, the views associated with those, and any custom graphics that we're trying to display; all of these will be performance hits on your web part page. To minimize these, try to actually use a list form web part and show a specific view of information in a list whenever possible. This can be a specific view of all late tasks grouped by person instead of a KPI that reports that information (so you don't get a pretty icon stating whether or not there's too many tasks behind schedule, but you'll see a number and be able to see corresponding data too). If we have more than 10-20 KPIs on a page then it takes maybe 30 seconds to display the data correctly (though some of that will be through inefficiencies in our own organization), so use KPIs to seriously high-level ONLY the information that managers need to see in a web part page and RESTRICT ACCESS to this page to the specific manager or manager SharePoint Permission group so that the page isn't constantly refreshed.

I'm welcome to receive other suggestions so that someone can reference this information as we discover more about KPIs. If any of this information is incorrect, please comment and explain why. Hope this helps!

Wednesday, October 14, 2009

SharePoint Conference 2009

I was put on the waiting list for SharePoint Conference 2009 and it went through! I'll try to be blogging during the event for the portions of the conference that I'll be attending. will also be having quite a few live Twitter streams and livebloggers for the event. I'm hoping to catch up to them and meet some of the big-wigs there.

Thursday, August 20, 2009

Thank you EndUserSharePoint

I just wanted to say that is a great website and very helpful for most SharePoint concerns including their Stump the Panel Q&A for anyone apart from coding questions. Several of the contributing authors are programmers/developers and provide detailed steps to accomplish just about everything you'd want in SharePoint (including special scripts/code that you don't have to really customize or know a whole lot about to make your site do some really cool stuff). Mark Miller, I salute you, sir.

Wednesday, August 12, 2009

Since you can't do Cascading Dropdowns...

We have a business requirement to create a course/section scheduling system with an automatic registration/attendance and then eval survey for special workshops/classes being taught to faculty within the university. We were trying to find a way, for attendance, to be done in InfoPath and let the administrators of a particular class setup a laptop that people can use to sign-in inside each workshop (lots of laptops needed, but hey, it works).

As you may or may not know, cascading dropdowns is impossible for Browser-compatible forms in SharePoint (at least for 2007). What we figured, though, was that there won't be a whole lot of sections for a particular course at one time; we decided to create a data connection to our SharePoint lists for both the Courses and the Sections. Now, the Sections list contains the Course ID, Course Name, and Course Description; we grabbed all of that from the Sections list and only the Course ID and Course Name from the Courses list.

To get what we want, we used a drop-down menu that connected to an "external data connection" and used our Courses list (setting the value to the Course ID and the Display Name to the Course Name column). We then used the Data Source menu (over in the right with the task pane) to create a repeating section with controls for all of the items in the Sections data source. When the repeating section is placed on the page, we deleted the unnecessary text/controls and then set Conditional Formatting on the table to hide the section if the Course ID was not equal to the Course selected in the Course drop-down at the top of the page. This works. If you want detailed instructions on how we did this, please comment, let me know.

Thursday, July 30, 2009

Setting a Person or Group Column in SharePoint Designer Workflow

Supposedly, you should be able to set a person or group column in SharePoint as long as the cumulative updates have been applied after December of last year (the December one apparently broke it lol). In our current setup, we still aren't able to set a person or group column using a workflow. I found this here at a MSDN blog archive and it basically helps you fix this problem in your workflow.

1. In your workflow, create a new variable whose type is a "String" and call it UserID
2. Use the action "Set Workflow Variable" and set it to whichever person or group column you are using
3. Use the action "Build a Dynamic String" and use a lookup to add the "Workflow Data" -> "UserID" to the string.
4. In front of the UserID lookup, add this: -1;#
5. Use the action to either "Set a Field in the Current Item" or "Update List Item" and, when setting the person or group column, use the "Workflow Lookup"->"UserID" instead of setting them directly.

Hope that helps someone, it certainly fixed our problems.

Filtering by a group in a SharePoint Person or Group column

I recently stumbled across this in a technet site here and modified it for my own use and to help anyone who wants it. I was trying to put multiple people in a SharePoint group, use a person or group column to categorize an item to that group, and then have it automatically filter based on which group you are in when you look at the item. I found the solution but it requires you to create the view that filters the Person or Group column to [Me] but then open the view in SharePoint designer and copy-and-paste the following (I know, it's scary) code. This means that you CANNOT add anything else to that view or attempt to modify it in any way after you change the code in SharePoint Designer because it will break that view and you have to do this all over again. This isn't a great solution because of that limitation but it will do if you absolutely HAVE to do this. Here are the steps:

1. Create the STANDARD view and filter to [Me] on the Person or Group column that contains the sharepoint group in which a person exists.
2. Open this view in SharePoint Designer…if it ever says you do not have permission to do something, simply click ‘Cancel’ and everything will be fine.
3. Click on the CODE or the SPLIT view near the bottom want to be able to see the code itself.
4. If you are using SP 2010, skip to step 5. Look in the code for the phrase ‘ListViewXml=’ tag and then scroll a LONG ways toward the right (after all the ‘FieldRef Name=blahblah_x0020_blahblah’) and look for ‘<Where’ and it ends a little ways after that with ‘/Where>’
5. Look at the ‘FieldRef Name=’ and note what it is for this filter.
6. For 2007 users, replace the <Where thru the /Where> with the following:
<Where><Or><Membership Type="CurrentUserGroups"><FieldRef Name="REPLACEME"/></Membership><Eq><FieldRef Name="REPLACEME"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or></Where>
(NOTE: DON’T MESS WITH ANY OTHER > and < in this!!)

For 2010 users, the split view in SharePoint designer will show you the encoded versions of these statements inside the <View> tag (it's SOOO pretty compared to all this garbage), so, you will want to fix them by copying and pasting this instead:
<Where><Or><Membership Type="CurrentUserGroups"><FieldRef Name="REPLACEME"/></Membership><Eq><FieldRef Name="REPLACEME"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or></Where>

7. NOTE: TO USE CALENDAR VIEWS – For 2007 users, look inside the &lt;Where&gt; statement to find /DateRangesOverlap and then it has a couple of &gt’s and &lt’s and then it says EQ followed by the FieldRef Name of the key column you are using. Replace &lt;EQ through /EQ&gt; with the following:
&lt;Or&gt;&lt;Membership Type="CurrentUserGroups"&gt;&lt;FieldRef Name="REPLACEME"/&gt;&lt;/Membership&gt;&lt;Eq&gt;&lt;FieldRef Name="REPLACEME"/&gt;&lt;Value Type="Integer"&gt;&lt;UserID Type="Integer"/&gt;&lt;/Value&gt;&lt;/Eq&gt;&lt;/Or&gt;

Again, for 2010 users, it should look all HTMLified and pretty so you will want to copy and paste this instead:
<Or><Membership Type="CurrentUserGroups"><FieldRef Name="REPLACEME"/></Membership><Eq><FieldRef Name="REPLACEME"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or>

8. Replace the REPLACEME’s with the fieldref name that it originally said. For instance, on one form with the Ministry teams the field name was Singing_x0020_Team (the _x0020_ is a space in the field name…and the fieldref name is the name the column was given originally…doesn’t matter if you change it after that, the fieldref name remains).

There ya go. Use only in emergencies ^_^.

Monday, July 13, 2009

Simple InfoPath 2007 Tutorial

This is the start of a series of tutorials regarding the creation of InfoPath electronic forms. For the sake of simplicity and expandability in SharePoint, I will be limiting the form to a browser-based form (so that the form can be filled out using a browser like Firefox or Internet Explorer but this requires that you are running the Enterprise version of SharePoint and not the free version). Note some of the following limitations of using a browser-based form in SharePoint:
[EDIT: I've revised this information slightly on 2/25/2011 to better fit what I have learned over the past if you read this before, it will have changed]

1. Performance - Browser forms means that another computer than your own must handle anything complex or difficult to calculate...this can result in a slower form than you might like. If you aren't using browser forms, then the form will open in InfoPath 2007 on your users' computer (if installed).
2. Security - You will want SharePoint to be running in an secure environment. How do you know if it's running in a secure environment? One way (but it's not always THE way) is to look at the very beginning of the web address for sharepoint in your company. If it starts with HTTPS instead of HTTP then you are good.  If it is not, InfoPath transmits data as clear text in an xml file and thus can be intercepted. YOU are responsible for the security of the information in this form.
3. Difficulties with large lists - you will not want to use your form to connect and pull from a large list or library in SharePoint (or one that will be large any time soon...large being 4000+ items). In 2010, we will have the ability to just ask for specific information from SharePoint so this won't be a problem.
4. You must be willing to think and think logically to make a secure, fast form.

InfoPath 2007 and a well-rested mind.
Recommended: A SharePoint site in which you can create a Form Library (if you use a default configuration of SharePoint, you will probably need Full Control of a site or have the Site Owner create a Form Library for you to use and give you Full Control of it).

You can click below to go to the first step - planning out your form, menus, and layout tables - or use the links farther below for more information about InfoPath.

To get more information directly about InfoPath: - Site with topical tutorials on InfoPath 2003, 2007, and browser forms - includes code stuff - the blog of the team who actually design InfoPath - Microsoft's 'InfoPath: Help and How-To' site that usually has a bunch of quick articles and facts to help

Thursday, July 9, 2009

MiniDVD in Macbook Pro

Though you may not notice, the slot-loader in a Macbook Pro is rather small and doesn't open like a regular cd drive. This thing is similar to the wii except for one strong difference: the wii can handle smaller (gamecube) discs...the Macbook Pro cannot.

After attempting to use many manners of credit cards and paper, the idea was given in a forum to use a business card with double-sided tape on it to remove the miniDVD from the drive. I grabbed my trusty Blockbuster card, took regular tape and looked into a circle, mashed it into the corner of the card, slid the card in, angled it slightly, and pulled out my miniDVD. Thank God for forums!

Thursday, July 2, 2009

Will InfoPath help me?

You've heard about SharePoint or WSS and that it can revolutionize your paperwork with electronic forms through InfoPath. You're probably wondering whether or not this InfoPath is the way to revolutionize your office. Here are some things to consider when it comes to using InfoPath:

1. Do you have WSS or do you have MOSS (Microsoft Office SharePoint Server)? MOSS Enterprise 2007 (or Forms Server 2007 and prior) includes Forms Services, which handles server-side rendering of InfoPath forms (what does that mean? It means that the form can be filled out online using Internet Explorer or Firefox instead of needing InfoPath)

2. If you decide to go the non-browser route, everyone who fills out the form needs to have InfoPath and access to both the form itself and wherever you want the form to send the data. If you go the browser-form route, you'll have to learn how to place it in SharePoint or see how that process works in your work environment

3. If you want the form filled out by the browser, know that there are several limitations:
  • You can connect browser forms to databases and SharePoint but cannot, without programming, filter what you get from them. For instance, you can pull an entire column of information but not a certain portion of it (so, if you have 100,000 items in the column then it pulls them all)
  • You can't use several controls that are normally available in InfoPath
  • You can't have error message windows appear on the screen
  • Using a lot of special conditions and special formatting in browser forms can slow performance
  • If you do end up having to use programming, you have to get the uppity ups of SharePoint involved at your work
4. There is a learning curve to figuring out InfoPath, though not as in-depth as Microsoft Access or Adobe Designer forms. In our line of work, we get most people developing great, advanced, codeless forms with appx 6-10 hours of training (though advanced forms may take several weeks to develop well)

5. InfoPath does allow for using signature pads to capture the signature and place it as a JPG on the form. Unfortunately, this will work only as long as you aren't using browser forms (because it's usually an Active X control that accomplishes this). You might be able to accomplish this otherwise but not that I could find. Digital Signatures are difficult to apply as well in browser forms but are supported in regular forms.

6. Apart from these considerations, are you using a lot of paper? Do you have a secure place to store electronic forms? Dear Lord, InfoPath can help you (so could Word forms, Adobe forms, or even Access, but they all have different pros/cons). InfoPath was made to help transform the business processes of many current situations. If you combine InfoPath with SharePoint and possibly SharePoint Workflows, you can have a truly powerful business process system to handle most forms of paperwork like vacation/absence forms, applications, quizzes, and more.

Wednesday, July 1, 2009

Why am I here?

I have been working lately with WSS3.0/MOSS 2007 (SharePoint 2007) and InfoPath 2007 to accomplish business processes. Everywhere I turn, I run into a roadblock and the only solutions seem to be programming-oriented. I may not be a genius but I realize neither I nor many of my colleagues have the skill or access to fix these problems (like filtering a data connection in a browser-based infopath form).

I am creating this blog to help vent some of that frustration and to provide training and tutorials for those interested in learning different Office products and what they can do without special programming/code. If you happen to stumble upon this site in its infancy, please let me know of problems you have encountered that you wish would be answered without using (or using the bare minimum of) CSS, HTML, VBA, etc.