A small nonprofit organization needed a better process for time tracking and reporting. Our solution is to let users enter their time data into a SharePoint Online list by way of an InfoPath form. And we use Excel to display the data in a dashboard.
The organization has two paid employees and the rest are volunteers; the board also consists of volunteers. The paid staff registered their hours in an Excel file. The employees work irregularly and they get more money for work that they perform at night or in the weekend:
|
|
00.00 – 07.00 |
07.00 – 22.00 |
22.00 – 24.00 |
|
Monday – Friday |
30% |
|
20% |
|
Saturday |
30% |
|
20% |
|
Sunday and holidays |
100% |
50% |
100% |
Each month, the Excel file was mailed to an external agency for salary processing. The payroll administrator then had to calculate, based on the Excel file, how much the employees should be paid for the irregular hours in addition to the fixed salary. The requirement is to calculate this automatically, because the external agency bills the organization for all of this manual compilation and calculation and that is getting too expensive. The solution I have made uses the following table for premium pay.
The Excel files gave very little insight into the hours spent by the two employees. The board wanted more control over the work performed by understanding the hours spent.
The current Excel files gave the employees insight how many hours they worked and how much leave they have available. This functionality should remain available.
The solution
The organization is already using Office 365 for non-profits, which is provided free by Microsoft through TechSoup. TechSoup is an organization that governs software donations for non-profit organizations.
Because the organization is currently already using Office 365 and SharePoint Online, I chose to enter the hours in SharePoint. An InfoPath form ensures a correct input and validation. The reporting is done in Excel and displayed on a dashboard in SharePoint with the Excel Rest API.
Below I will describe the steps I followed to create the solution.
Step 1: List for time registration
The core of the solution is a list in SharePoint. I’ve created a subsite below the existing teamsite for that list. This is needed to give the external agency only access to the solution. At first I had created a list in the existing teamsite, but then the account for the agency could not refresh the data in Excel. The account needs read permissions on site level to refresh data and we don’t want the external agency to see the rest of our teamsite.
I called the subsite ‘Timesheet’ and on the subsite I created a list called ‘Time Entry’. To prevent the two employees from editing each other’s entries, I changed the item-level permissions. You can reproduce this step as follows:
Go to the list setting, select Advanced settings and at Item-level permissions select ‘Create items and edit items that were created by the user’.
Create the following columns.
On the following columns I set these settings:
- Title: default value ‘Time entry’
- Year: default value ‘2015’ (update each year)
- Week: minimum and maximum allowed value between 1 and 53
- Day: choice column with every day of the week and holiday
- Time Period: choice column with the choices from the table above (00:00-07:00, 07:00-22:00, 22:00-24:00)
- Work Type: choice column with different work types as choices
- Task: choice column with different tasks as choices
Step 2: InfoPath form for entry
Next, I’ve edited the ‘New item’ form. We use InfoPath to allow the employees to enter their data quickly and easily. We are aware of the fact that InfoPath will be phased out, but we can continue to use it for years anyway.
I didn’t use the option in the Ribbon to edit the form, but I started InfoPath and by using the wizard we will get the right settings. The reason is that InfoPath then gives you more options, like the option to include repeating tables. We want the employees to be able to enter their hours for the entire day or even the entire week in one go.
You can reproduce this step as follows:
Start InfoPath Designer and chose SharePoint List.
Fill the URL of the SharePoint site.
Chose for the list you just created.
At Advanced Option select ‘Manage multiple list items with this form’, allowing the employees to enter all of their hours for the entire day or the entire week in one form. Click the Finish button.
On the loaded form, you see all the fields displayed vertically. Remove Title, Employee and Year. Move the other fields next to each other, horizontally, and it should look like the image below. Probably you want to add a description above the form to explain how it works. Every separate item is a separate row in the form and will become a separate item in the list: different tasks, or the same tasks in different time periods etc.
Next, we need to set a couple of rules to prevent wrong data being filled in by the employees.
First of all, we want the field Employee automatically filled by the current user’s name. Right-click on Employee under Fields and select ‘Field Properties’.
In Value fill in this formula: substring(userName(); 19; 255)
Secondly, we want the irregular hours premium pay (called ORT) to be filled automatically. To accomplish that, the columns ‘Day’ and ‘Time Period’ has to be filled first, before we calculate the ORT percentage.
So we create a rule that the Time Period only can be filled if the Day also is filled.
In the ribbon click on ‘Manage Rules’ and select the column Time Period.
Select New and then Formatting.
Fill in a title and in the Condition field you select ‘Day is equals to blank’. If this condition is true, then the column must be disabled. Check ‘Disable this control’.
Then we create a rule that will fill the ORT field with the correct value. Choose New and then Action.
I will pick one rule as example: On Sunday night the employee will get 100% ORT. The rule will have this condition:
If Day is equal to Sunday and Time Period is equal to 00.00-07.00 and the task is not Vacation/leave then the ORT field must set to 100.
There will be another 10 rules for calculating the ORT. I added the full table with rules below.
We need to add vacation/leave to the condition. The employees will fill in the form how many hours holiday they have used, but never can get ORT (premium pay) over this hours. To prevent this we create an extra rule on the Day column.
Select the column Day, choose New and then Formatting.
Fill a title and as condition select Task is equal to blank. If this condition is true then the column should be disabled, check the ‘Disable this control’ option.
Finally we create a rule that will empty the columns Time Period and Day when Task or Day are changed. If we do not do that, it would still be possible to get premium pay on vacation/leave.
Below I add the rule on the Day column, but you also must do it on the Task column.
Choose for New and then Action.
Don’t fill any condition, so that the action will always be executed on any change of this field. As action we set the fields value must be equal to “”, we just don’t fill anything in here.
To summarize, we now have the following rules on the following columns:
|
Rule on column |
Name rule |
Condition |
Action |
|
Day |
Empty Time Period |
None |
Set a field’s value: Time Period = (empty) |
|
Task |
Empty Day |
None |
Set a field’s value: Day = (empty) |
|
Time Period |
Set ORT 100 Sunday night |
Day = Sunday and Time Period = 00.00-07.00 and Task != Leave |
Set a field’s value: ORT = 100 |
|
Time Period |
Set ORT 30 night |
Day != Sunday and Day != Holiday and Time Period = 00.00-07.00 and Task != Leave |
Set a field’s value: ORT = 30 |
|
Time Period |
Set ORT 20 evening |
Day != Sunday and Day != Holiday and Time Period = 22.00-00.00 and Task != Leave |
Set a field’s value: ORT = 20 |
|
Time Period |
Set ORT 100 Sunday evening |
Day = Sunday and Time Period = 22.00-00.00 and Task != Leave |
Set a field’s value: ORT = 100 |
|
Time Period |
Set ORT 100 Holiday night |
Day = Holiday and Time Period = 00.00-07.00 and Task != Leave |
Set a field’s value: ORT = 100 |
|
Time Period |
Set ORT 100 Holiday evening |
Day = Holiday and Time Period = 22.00-00.00 and Task != Leave |
Set a field’s value: ORT = 100 |
|
Time Period |
Set ORT 50 Sunday |
Day = Sunday and Time Period = 07.00-22.00 and Task != Leave |
Set a field’s value: ORT = 50 |
|
Time Period |
Set ORT 50 Holiday |
Day = Holiday and Time Period = 07.00-22.00 and Task != Leave |
Set a field’s value: ORT = 50 |
|
Time Period |
Set ORT 0 day |
Day != Sunday and Day != Holiday and Time Period = 07.00-22.00 |
Set a field’s value: ORT = 0 |
|
Time Period |
Set ORT 0 leave |
Task = Leave |
Set a field’s value: ORT = 0 |
Step 3: use Excel for reporting and dashboard
For reporting, we will use Excel. Go to the Time entry list and click in the ribbon on ‘Export to Excel’.
By creating a couple of PivotTables, we give insight in some details about the time entries. Firstly, we create a table with ORT information for the payroll administrator. We share the Excel file with the administrator so he/she has access to the latest premium pay information.
I created a couple of tables and charts to give some insight in the hours spent. I saved the file to a Document Library on SharePoint.
To make these insights easy accessible for the board, I’ve created a dashboard on a page in SharePoint. By using the Excel Rest API, you can show the tables and charts on the page as images or html. You can do that as follows:
You can access an overview of all available tables and charts using the API. Enter the following url in Internet Explorer, substituting the names of your tenant, site, etc into the address:
https://[tenant].sharepoint.com/sites/[SiteCollection/[Subsite]/_vti_bin/ExcelRest.aspx/[Documentlibrary]/[Excelfile].xlsx/Model
You will see the screen below.
When I click on Charts, I see all available charts. I created a chart for the hours an employee spent in a week on a task. When I click on the name of the chart I will get to see an image. I copy the url in the address bar and on the SharePoint page I choose adding a new picture by url and paste the url.
The url looks like this:
https://[tenant].sharepoint.com/sites/[SiteCollection/[Subsite]/_vti_bin/ExcelRest.aspx/[Documentlibrary]/[Excelfile].xlsx/Model/Charts('ChartHoursEmployeeTasks')?$format=image
When I choose a table and click on the link I will get a 404 error. I get this url:
https://[tenant].sharepoint.com/sites/[SiteCollection/[Subsite]/_vti_bin/ExcelRest.aspx/[Documentlibrary]/[Excelfile].xlsx/Model/Tables('TableHoursBalance')?$format=atom
This is a known issue in the API. You can work around this issue by changing the last bold text to ‘image’ or ‘html’. Then you will see the table as an image or in html.
Below you will see a part of the dashboard we use.
Conclusion
We have built a timesheet that employees use to register hours by entering them in an InfoPath form that saves the data to a list in SharePoint Online. Rules in the form guide the user and calculate the premium pay (ORT) they are entitled to. The payroll administrator automatically gets an overview of the premium pay the employees should get, in addition to their regular salary. And the board can see what the employees have done for that money, in a dashboard based on Excel charts and tables displayed on a SharePoint page by way of the Excel Rest API.
This solution has been used for nine months now, and the users are very happy with it: it is easy to use, less error prone and far less time-consuming than the old way of working.
by Frank Op 't Landt via Everyone's Blog Posts - SharePoint Community
No comments:
Post a Comment