Overview:
One of the most common questions we hear from those new to Deltek Vision is “How can we send alerts when a project reaches a specific budget spent threshold?”. This is a common frustration in that Vision does not have a budget alert mechanism unless you own the Resource Planning module. Many are also confused by the “Budgeted Validation” option in the Project Info Center –> Time & Expense tab. This is simply a tool for preventing Employees from entering time to a project unless their labor code has been budgeted for the given Project (Phase or Task) – a useful tool, but not a budget alert.
The Problem:
Recently one of our clients came to us requesting a custom tool for triggering alerts when their projects reached specific budget spent thresholds, however there was more to this than simply firing an alert. Some of the key requirements of this project included:
- Budget amounts would be read from the Project Info Center Compensation field and Spent amounts would be a calculation of:Total Invoiced + WIP + Committed PO Expenses
- Alerts needed to be sent by e-mail, as well as to user dashboards
- All historical alerts needed to be saved in the system in order for senior management to be able to review them. Anyone familiar with Vision dashboard alerts knows that once a user navigates from a dashboard alert to the underlying record, the alert is gone from the system forever. Also, a user can clear all of their alerts in one click using the Alert Dashpart menu. Again, once these are cleared they cannot be retrieved.
- There needed to be multiple budget spent thresholds as well as thresholds that would trigger escalation alerts after a specific number of days had passed since the last alert with no action taken.
- Every time an alert was triggered, an entry would be made in a custom “Budget Tracking” grid in the Project Info Center –> Budget & Revenue tab. This entry would be used as a base line for tracking the number of days with no action taken before a subsequent alert would be triggered.
- All of the alert Thresholds, Recipients, Alert Messages, etc. needed to be configurable by the client – inside Vision – without the need for additional development.
The Solution:
The solution we developed was able to meet all of the client’s requirements as well add some additional functionality and consisted of the following key components:
- Custom UDIC – A custom User Defined Info Center was built to store each alert that was triggered. Since Vision dashboard alerts are gone forever once cleared and e-mail alerts can easily be deleted by the recipient, the UDIC provides a permanent storage system which can then be reported on by managers and finance staff.The secondary purpose of the UDIC is to hold a “CONFIG” record which allows the client to make adjustments to the alerting system without further custom development time. From this record they can add, delete and adjust:
- Type of threshold – % of budget or # of days since last alert
- Threshold in % of # of days before an alert is triggered
- Determine who the alerts should be sent to – between 0 – 3 individual recipients
- Details of the alert messages
- Stored Procedure – A stored procedure is run nightly (but could be as frequent as required) which reviews all active projects, calculates the % of budget spent and then determines which ones have hit a threshold and processes the appropriate alert.
- Budget Tracking Grid – In order to track all aspects of the project’s budget, a custom grid was added to the Project Info Center–>Budget & Revenue tab. Each time an alert is triggered, a record of this alert is added to the tracking grid. This is how the Stored Procedure can determine whether or not the specified # of days have passed since the last threshold alert have passed. If no entry has been made to the grid to acknowledge that there was an alert and action should be taken, then a new “# of days passed” alert is triggered.
- Budgeting Triggers – Because the client managed all of their project budgets in the Accounting–>Budgeting–>Project Budgets built-in tool, they wanted to be able to keep the Fee and Expense fields in the Project Info Center–>Budget & Revenue tab in sync. To accomplish this we added custom database triggers to the 2 tables that store Labor and Expense budgets, such that any new entries, deletions or changes to the budget will automatically update the the Fee and Expense info for the project.
It was very important that this entire utility be flexible as well as upgrade proof. By utilizing the built-in Deltek Vision UDIC functionality, with only a small amount of custom SQL code, this tool will be able to easily carry forward through Vision upgrades as well remain extendable while allowing the client to make adjustments to it as required. All of the alerts are run through the Vision system – even the e-mail alerts are pushed through the Deltek Vision Process Server and can be HTML formatted!
If you would like to learn more about this great utility and how it could be customized to suit your business drop us an e-mail at [email protected]
Some screenshots are included below:
Below is the Configuration record in the UDIC
The Configuration Tab of the Configuration record
A sample alert record (some fields removed)
A sample of a dashboard alert (Clicking "Navigate to Record" takes you to the project)
