I worked for a long time at a civil engineering firm that designed much of the built world that I enjoy, such as sidewalks, a faucet with water, and wastewater reclamation. As a professional services firm, they have a need to invoice their clients in order to generate revenue for what they do. We had a well-oiled machine for invoice, then there was a disruption.
A rather important client was the Wisconsin Department of Transportation (WisDOT) had implemented a new website for submitting invoices. It required the Finance Department to gather the invoice information that was not just the data on the standard invoice. They had to group the data by employee, task, and month for hours and expenses. This would mean, on average, and increase to the invoicing time by 1 hour. With perhaps 20-50 invoices a month.
Finance let me know this was coming, which I was also hearing from the user groups for the software I was managing at the time. What I found was functionality to import the invoice data into the WisDOT website versus manually keying the data. That interested me, so I dug deeper and learned it was an XML import with very detailed instructions on the schema. I can work with that.
The task wasn’t to just create a report from the finance system, it was to generate the needed XML, which we could also use to create a report. I dug into the XML to start to understand the requirements, while testing each piece by querying the database. This seemed doable. Now I had to set out and understand how to generate that XML, and I found a new way I had not considered before. I could use a SQL select statement to generate the XML directly.
I don’t want to get into showing code here, but it was as simple as just adding
FOR XML PATH
at the end of my normal select statement and it would generate XML. I could even pass in the name of the node I wanted, and it would interject it that way. All I had to do was use standard sub-select statements in order to make it nest. Super cool.
The last think I needed was to setup an interface in the finance system to generate and display the generated XML, but that is for another post.
The solution worked, and we put it in production before WisDOT cutover invoicing to the new system, and saved 20 – 50 hours for the billers in Finance. With a bill rate of $100/hour, that is a potential max efficiency gain of $1,000 of revenue per month, not to mention the simple reduction of time to complete a task and the cost of doing business.