Project managers and executives in asset-intensive industries like utilities, oil and gas, or chemicals need up-to-the-minute data on projects, as they usually manage a myriad of capital expenditures for infrastructure upgrades, capital works, or capacity expansions. But what happens when an ERP software reimplementation pressures project management processes, reporting, and strategy?
In this blog, Donna Christie, Finance Applications and Reporting Specialist for ActewAGL, shares how a reimplementation of Oracle E-Business Suite (EBS) version R12 from a highly customized R11i version impacted access to Oracle projects data. Located in Canberra, Australia, ActewAGL sells and distributes electricity and gas to more than 300,000 customers.
How did ActewAGL project managers access data they needed from Oracle’s Projects module prior to the R12 implementation?
Christie: Discoverer [Oracle Business Intelligence Discoverer] was initially used to report on 11i Projects data, but over time, a skilled SQL developer created excellent in-house web-based views of Projects data, as well as that in other Oracle modules. These web views were highly customized, and project managers and Finance staff really liked them, because they’d just enter information in a form that masked all the background tasks and SQL to retrieve the data. Simple to use, they provided data from Projects, General Ledger, Payables, Inventory and Purchasing modules in the same view.
What was the initial strategy for reporting Projects data in R12?
Christie: Because Oracle has discontinued Discoverer support, ActewAGL put a project in place to implement OBIEE [Oracle Business Intelligence Enterprise Edition], a complex business intelligence (BI) tool. The customization of the previous reporting process had been great, but the idea behind the R12 reimplementation was to minimize customizations, so the business had to prioritize which of the 200+ reports /views from the old system would be required in OBIEE. Unfortunately, the required reports weren’t well-specified upfront by the business, leading to a misunderstanding with the consultants, and the reports did not meet user requirements during the testing phases.
Problems materialized when the project wasn’t able to deliver the necessary OBIEE reports within the allotted timeframe. We also found that the way OBIEE had been configured would not provide report data in real-time, and the business users didn’t want day-old data. So, we were faced with an R12 go-live with no available transactional and project management reports. Oracle Projects does not supply good project management reports out of the box, so we had to find a solution.
When it became evident OBIEE wouldn’t work, what were your options for reporting from the Projects module?
Christie: We faced the possibility of creating custom Oracle reports—a complicated process—because Oracle Projects standard reports couldn’t deliver the kind of information the project managers required, such as total capex spend, margins, burdening, and commitments.
When the R12 project business representatives first saw the R12 Projects demo, it looked like they’d get what they needed from it, as well as helpful enhancements like dashboards, but they didn’t realize what they were shown required expensive customizations that weren’t in the project scope. So, we turned to Excel4apps Reports Wand, a third-party solution that lets you retrieve data directly through Excel using custom SQL.
The ability to run the reports in Excel was advantageous, as our Finance and Project Management users regularly use Excel and it’s familiar to them. This solution also offered additional value in the provision of NoetixViews for a small increase in licensing costs. NoetixViews offer pre-built Oracle views per module that can be used to produce reports without requiring help from a technical developer. A user who understands the module and views can create the reports.
How was the transition to project management reporting in R12 with the third-party tool?
Christie: The project management reports were in high demand, and we kicked off the new reporting project needing around 120 reports across all Oracle modules. We essentially have four internal customers in ActewAGL Finance: Water, Network Distribution (electricity and gas network), Corporate Services, and Retail (electricity and gas sales), and each has distinct project reporting requirements.
A massive benefit of the new reporting tool is that we developed lots of initial reports and then were able to create/cut down SQL from those to produce report templates for each of the four different areas with various columns, calculations, graphs, and the dashboards that project managers especially wanted. Unfortunately, because the Oracle database tables changed so much in R12 and we did a reimplementation instead of an upgrade, we weren’t able to re-use the custom SQL from our 11i reports and web views. New reports were created by an in-house development source, and we were able to modify the SQL in those for particular needs. Had we been able to fully utilize the NoetixViews, this would have sped up the production of reports.
Our project managers have been happy with the new reporting tool and appreciate its real-time data delivery. They continually think of ways to fine-tune the reports. Our other key group of users, the accountants, also appreciate the tool and claim that just one report has saved three hours on each monthly close.
Additionally, insightsoftware continually works to improve its products, and we have been able to improve our users’ experience through the introduction of those enhancements, like being able to use Oracle List of Values in Reports Wand.
What are some of the unique aspects you’ve accomplished with project management reporting?
Christie: We created a series of complex reports for our project managers. They were grouped into:
- Project Summary by single or multiple projects
- Project Task Summary by single or multiple task
- Project Expenditure by expenditure category
The expenditure by category sums project expenditures into a group of business specific categories by period-to-date (PTD), year-to-date (YTD), and inception-to-date (ITD). This provides project managers with a clear view on where the areas of most spend have occurred. This required custom SQL to combine large numbers of expenditure types into a series of six categories.
Another series of templates for our Corporate Services group shows the capex and opex amounts per task on each project, thereby allowing them to understand what their total capital costs versus operating costs are. They can then drill into the detail if required from the Excel-based reporting tool.
From a purchasing standpoint, we are able to track blanket purchase agreement (BPA) commitments and actuals for a project using a DFF function in the Purchasing module and custom SQL to pull back the BPA outstanding commitment amount.
Additionally, we created a report that checks internal orders in the Inventory module to see if an item is assigned to a project, but the item is not actually picked and shipped yet. So, for example, if a project manager is building a new substation and has ordered 10 poles that aren’t needed for three months, the project report still reflects the cost of the poles assigned through inventory.
All of our report dashboards required PTD, YTD, and ITD actuals and budget amounts with drills to the underlying details.
What are your future plans for reporting?
Christie: We’ve just touched the tip of the iceberg with our Excel-based reporting. The NoetixViews pre-built data views should assist us in generating ad hoc reports with a quick turnaround. It took me just 20 minutes, for example, to create a credit card report for a user needing to know outstanding acquittals for a specific corporate credit card. We saw significant savings when we discontinued our Discoverer and OBIEE licenses and support and purchased Excel4apps Reports Wand and GL Wand reporting tools by insightsoftware. We were an early adopter of the NoetixViews offered with Reports Wand. If we’d had more time to explore using NoetixViews to create reports, we could’ve achieved additional time and money savings, as we may not have had to engage additional developers to create custom views and packages because NoetixViews let you build reports without writing SQL. We’ll also use some pre-built dashboards now available that users can leverage with little effort.