Recently, Deltek contacted me to develop a custom report for the Planning module.
To the customer, it seemed simple enough at first, merely a graph that showed planned vs. actual amounts in the same space, with revised planned amounts going forward from the report date. In the original specifications, the example looked like this:
Keeping in mind that the data provided to develop the report against was pilot data (it looks kind of weird) here is what it’s shaping up to look like:
You can also see the chart of hours if you wish (you can turn on and off lines with check boxes on the same graph)
While there are still some refinements needed to this report, it’s 95% complete.
It’s actually a report that Deltek Vision does not provide out of the box because of its complexity. If you’re interested in getting this report for your implementation let me know.
If you’re interested in knowing some of the challenges faced in developing this report (part of the reason it’s not available as a standard item) read on…
Simple enough in concept, very challenging in practice. Here’s why:
- The implementation is in the cloud, not hosted on the client’s own servers. This means that all SQL underlying the report had to be resident in the report itself. No ability to add functions, store procedures, custom tables or anything else of that nature. Once I got the hang of Deltek’s cloud strategy for custom reports though it was conceptually easy to do.
- Different time scales for planned items inside the plan:
Deltek Vision allows you to create plans that span multiple time scales in the same plan. This means you could have an entry that was something like this:
John Sellers, Inspector | Start Date 1/1/2016, End Date 12/31/2017, Cost: $300,000 Bill: $600,00 – expressed in months.
Then in the same plane you might have something like this:
Susan Waller, Project Mgr | Start Date 1/1/2016, End Date 7/31/2016, Cost: $75,000 Bill: $150,000 – expressed in weeks.
- Different time scales for cost items:
You will have JTD Cost and bill items on the projects attached to the plans expressed in accounting periods from the summary tables. Easy enough if you want to graph everything by month AND the accounting periods line up with calendar months (in this case they do not). However, if like in this case the accounting periods do not line up with the plan, then you have to somehow spread out the actual costs over a timescale by day so you can graph both planned and actual items in the same space.
- Planned revenue on fixed fee projects is going to be a formula based upon planned percent compete, and each phase in a project can fall on either side of the fixed fee vs. time and materials (or cost plus) billing terms.
This required a gathering all the JTD cost and all the planned items (past and future) then somehow getting them all to translate to a per day cost or planned item, as well as make sure I kept the phase information on projects since this affected planned revenue.
Once I had everything to the right scale, then I had to put it all on a time series (a calendar) that included every day from start to finish of the planned and actuals for the project (since the data is represented in a cumulative fashion, you have to start from the beginning). Lastly, after spreading out actual items and plan items over the time series, making sure that months that were heavy on holidays and weekends were properly assigned the relevant amounts required incorporating the holiday calendars as well as weekends etc.
Then of course there is the difficulty with SSRS graphing tools. They’re a bit wonky and finicky.
But, got the job done.
If you’re interested in having a similar report for your Deltek Vision implementation, get in touch with me.