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.
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.
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.
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.
Steps:
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!
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.
Cheers!
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
www.gazzaconsulting.com
No comments:
Post a Comment