Search This Blog

Saturday, December 17, 2011

Sharepoint Part 9: Custom Project Systems on a Shoestring

Develop your own project systems using Free Tools, just add labor

Using what you've built - more free enhancements

In this final session, we will look at expanding your productivity and the benefits of using Sharepoint - now that you have a system in place. By now, you will have some live data in your system, and want to be able to do more with it. Read on for some powerful tools and methods to work with your Sharepoint data.

We will take a look at several examples of using Sharepoint with external tools as high-value add-on features:
- Timesheet integration
- One-click status reporting
- Forecasting/Capacity Planning
- Billing Audit

And then
- How to do it yourself!

Timesheet Integration with Sharepoint
Excel is a powerful and flexible tool. In our system, we designed a timesheet spreadsheet that allowed resources to track their time in a more traditional grid model (Project / Date  / Hours). However, they also included two dynamic queries linked to Sharepoint to ensure the latest information and newest projects are always available, as well as all time the resource has entered in the last 30 days, for cross-validation purposes

(Why a separate timesheet? Our clients needed the traditional form, with task hours by day. It is also useful for cross-checking.)

In our company, Sharepoint drives client billing – so this timesheet also provides validation that all time worked has been recorded for billing. When the resource opens the spreadsheet, they are challenged with their Sharepoint login – so that the spreadsheet can go and fetch the latest results from the two linked Lists on the live Sharepoint site.

Note: Although technically possible, we chose not to have the spreadsheet feed back time into Sharepoint – we wanted a single point of entry and no possible conflicts with manual vs timesheet importing. However, the validation feature in the spreadsheet to confirm you have recorded your time in Sharepoint (which is what will be invoiced) works very well.

The Project Assignment records are structured so that time can be entered incrementally, or as a lump of time at the end of the week, depending on the duration of the task and granularity needed for the end client.

Note: The time entry has an Effective Date (defaults to today).

Project billing info is populated by selecting the project drop-down & task type, which pulls data from the dynamic Projects query linked to Sharepoint using VLOOKUP.

Benefit: All project billing info centrally maintained = no billing typos on the timesheet or invoices to the customer!

Each resource can verify all Project Assignment time entered in Sharepoint, using the built-in “My Time Entry-Last 30 days” dynamic query linked to Sharepoint.

The timesheet also supports export for “end client” timesheet system import, when needed.

One-click Status Reporting
Using dynamically linked queries from Sharepoint based on a suitable view, you can create a spreadsheet that contains either raw or processed status on all of your project items - and all you need to do to refresh it is open the spreadsheet and login to Sharepoint!

And don't forget Autofilter in Excel - very handy when you want to distribute status to a variety of team members and stakeholders, and they want to quickly filter for themselves.

Forecasting/Capacity Planning
Because our system is used for active project tracking and new work, we have information in Sharepoint that is useful for not only showing actuals, but for forecasting by resource based on booked and potential new work (pipeline) within the calendar year.

We have produced a weekly forecast spreadsheet with per-resource and per-customer views, with quarterly roll-ups and unbooked pipeline, and pivot table analysis - all driven from a single linked query from Sharepoint.

Resource view - Quarterly/weekly

Customer view - Quarterly/monthly

Note that this solution does require some additional custom fields in order to drive the statistics, which are included in the output query. But it is an example of what you can do with a little ingenuity.

And like all of the other spreadsheets, just open, login and run a refresh macro - and you have a complete forecast/capacity planning update for the execs - in minutes, not hours or days.

Billing Audit

As we also do our invoicing from Sharepoint, we have all relevant time tracking data for invoicing, which can be used for reconciling invoices to raw time - which can be very useful for conduting billing audits with clients, if there is a question on some invoice items.

Of course, you can always lookup individual items - but it is very handy to be able to export the data for analysis in a form that can be shared with the client. In this spreadsheet, the raw source time entry items are all brought in - with project number, resource, task, date, "billing week#", hours and other information, which is then analyzed using pivot table, where the auditors can break up the information any way they like by changing columns and rows in the pivot table.

And again - the spreadsheet is reusable in future by simply opening it and logging in to Sharepoint to get the latest data.

How to do it yourself - working with Dynamically Linked Queries
In Sharepoint Lists you can use the “Export to Spreadsheet” function:
and then incorporate that dynamic query (*.IQY file) in a tab of your spreadsheet and then use the results on other tabs and in formulas.

Open the *.IQY file.
Note: You will need to login to Sharepoint.
You will also be asked where you want to put the data from the linked query - into an existing workeheet, or a new workbook.
If you want to start a new spreadsheet, then choose "New Workbook", otherwise, specify an empty tab to put the data into. When you include the linked query in a spreadsheet, the entire range of cells is outlined in blue.

*Note that based on your query (view) definition that you exported from, the records pulled into Excel may differ each time you open the spreadsheet. For example, the view-query may look at the last 30 days of activity, or it may be a continually growing list.

You can then create a simple or sophisticated spreadsheet that uses the results of the linked data pulled into Excel from Sharepoint. Note that this is suitable for tabular data processing, including rollup calculations, VLOOKUP, pivot tables, etc.

Caution: As the range of cells in the dynamically linked query can change, any formulas that you based on the query data may suddenly become invalid, i.e. the last time you had 1000 rows, but this time you only have 850 - the lost "150" are treated as if you had deleted the cells with CTRL-X.

The workaround for this, however, is relatively simple. As long as you can be sure there will always be at least one record returned from Sharepoint, you can use an Excel macro to copy/paste the first row of formulas to all other rows.
Then all of your results will be consistent with no "breaks". Likewise, if your spreadsheet uses pivot tables, those need to be refreshed as well - but that can also be done by macro.

That's All Folks!

This is the end of this particular series - but not the end of the journey. Keep checking back for other Sharepoint articles from time to time - and for the other topics in my blog. 

Thanks for reading, I hope you found it helpful. This is the culmination of many (many) hours of scrounging the web and figuring out bits and pieces as I went along, from many different locations and postings (plus a few books and lots of experimenting). I hope this helps you get a quicker leg-up on developing your own systems.

And of course, if you need further specific help (or help to you build your own system), I can be contacted on a consulting basis if desired.

Gary Nelson

Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design
Part 5: Implementing Custom Workflows with Sharepoint Designer
Part 6: Advanced Workflows with PowerShell
Part 7: Hosting tips, Email & Notifications
Part 8: Doing it Yourself - Recommendations

Other Sharepoint Articles

For more information or questions, contact:

Gary Nelson, PMP

Friday, December 16, 2011

Sharepoint Part 8: Custom Project Systems on a Shoestring

Develop your own project systems using Free Tools, just add labor

Doing it Yourself - Recommendations

If you don't know where you are going, any road will get you there. But it may not be where you want to end up.

Plan it out.
Figure out what you need to do (the high-level requirements). Brainstorm!
- What do you really need?
- What do you need a system to do for you?
- What problems/pain points are you trying to fix?
- What works with your current processes (manual or otherwise)?
- What doesn't work?
- What are the inputs?
- What are the outputs?
- What are your "gates", decision points and start/end stages?

Gather the ideas from your team, look at how your business processes work (or don't work), and put them together for discussion. Draw it out, use post-its, do your best to cover the various "what if's" that you know of or may happen.

Design the model
With the needs and wants identified, the inputs, outputs, key decision points etc - it is time to put pencil to paper. Mock it up. Draw the high level workflows and review with your team – make sure you do several passes to make sure you have covered all of your key needs.

Then break it down to the next level. Model all of the state transitions (a flow chart is suitable for this), and see if there are any holes. If you missed something, add it in, rework the model and make sure that it "works" for you.

It is important not to skip this step!

An important gate in your planning is determining if you actually do need a new system to help you do what needs going. Maybe you have decided you don't.

But if you do, is an off-the-shelf system close enough (and affordable enough) to do what you need? This might indeed be the case - but now that you have gone through the effort of figuring out what you really need, when you begin your evaluation of different products (vs doing it on on your own), you will be well prepared to make informed decisions. You will be better able to look past the pretty screens and glittering lights of the demo to see if a particlar package will do what you need.

Ideally, you will want to get a free trial copy (full featured, limited time period) to play with each system to really shake it out, and try modelling parts of your process workflow, and see how it works.

Do the same with Sharepoint - of course, WSS 3.0 is free with Windows Server 2005/2008, so it is just a matter of effort.

But if you do need to report on the decision to go with a particular product vs others, it is advisable to do a side by side trial - try modelling some easy bits and some hard bits in each before you pass final judgement.

And also decide if you have the people and skills to do it yourself - and maintain it. If so, great! If not, then grab some books, fire up Google and use my blog and others - or, consider a commercial alternative.

There are many benefits of doing it yourself - particularly the ability to design exactly what you want, but you just need to be prepared for it.

So you've decided to DIY - Now What?
Modularize & Prioritize
Break it down. Divide the overall solution by priority of business need. You will not need everything at once, but you should be able to determine "must have first", "must have next", "really nice to have", "nice to have", "maybe some day", etc.

Note: Document your processes and workflow logic as you go
- for user guides, and
- so you remember how you coded it!

Start with the basics. Get up and running with the essential components; don’t try to swallow the whole elephant at once. You may need to keep the “nice to haves” as paper/email/external processes until you have a chance to integrate them.

Add functionality as you need to, and as you grow. (Consider, plan then develop and test it in a contained, modular fashion). This is particularly important with Sharepoint - if you are planning to add on several pieces of functionality - I recommend you do it modularly.

If the pieces of functionality are independent - start work on one, finish it, test it and make sure it works before starting on the next item.

If some pieces are inter-dependent, develop them together and test the group of them before introducing any other functionality.

Establish your Backup / Recovery plan/mechanism from day one.
Network backup, online backup, a combination of both is fine - but do establish your backups from the very beginning. You will need, at a minimum:
- Regular server image backups (Windows or commercial products)
- Scheduled Sharepoint backups (SPBackup works very well for this) - at least daily
- Online backup service [optional]

Using SPBackup
Download the SPBackup package from Codeplex. Follow the instructions to install and use it, however here are some key points:

There will be two main files:

The XML file is used to define the source and destination for the backup, how many versions to keep, and email notification settings.The settings below in green are recommended for "full backup" of everything. The yellow highlights are what you need to modify for your setup.

<?xml version="1.0" ?>
   <site id="1">
 <hive>C:\Program Files\Common Files\Microsoft Shared\web server extensions\12</hive>

*Note that SPBackup can backup to any folder, however some online backup services will not backup from particular networked drive configurations. In this case, use SPBackup to backup to a local drive and then have the online backup service pull that backup file for safe keeping.

Schedule it
You can automate your SPBackups by creating a .BAT file in the same folder as your SPBackup files. Call it something like "SPBackup.BAT" and it should contain the following line:

powershell -command "& './spbackup.ps1' 'spbackup.xml' "

Solution Summary

- 1 Windows desktop server with Windows Server 2008
- Sharepoint WSS 3.0 Software: free
- Sharepoint Add-ons: free
- DynDNS site name: free
- Email passthru: free
- Labor to implement solution
- Books (optional: Google is very useful)

Requisite Skills
- Windows basic administration concepts
- Basic programming skills & concepts

Learn as you go (use Google, books etc)
- Windows Server 2008 administration
- Firewall setup / port forwarding
- Sharepoint Administration (learn on the job)
- PowerShell

Optional (Tip: Google helps with everything)
- Excel
- SSL setup
- Javascript
- .NET programming

- Windows Sharepoint Services 3.0 SP1:
- Microsoft Office Sharepoint Designer 2007:
- Codeplex:
- iLoveSharepoint: PowerShell 2.0 (Windows Server 2008 built-in-feature, enable through Server Admin)
- Google!

Suggested Books
- Windows Powershell Unleashed 2nd Ed, Tyson Kopczynski  Pete Handley / Marco Shaw
- Windows Sharepoint Services 3.0 (Microsoft Publishing)
- Office Sharepoint Designer 2007 (Microsoft Publishing)
- Many others are also available

Coming next:
Part 9: Using what you've built - more free enhancements

Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design
Part 5: Implementing Custom Workflows with Sharepoint Designer
Part 6: Advanced Workflows with PowerShell
Part 7: Hosting tips, Email & Notifications

For more information or questions, contact:

Gary Nelson, PMP