How to automate TM1 reports without Excel

What's the hardest technical decision you made building Reportworq?

Matt Hopkins answered this one for Built To Know.

"The hardest technical decision we made building Reportworq? Cutting Excel out of our Excel reporting product.

Our customers live in Excel. Their reports are Excel. Our job is to generate and distribute those reports automatically from IBM Planning Analytics (TM1) and a growing list of other data platforms.

To run reliably at scale, the engine has to work overnight on a server with nobody at a machine. That meant removing the dependency on Microsoft Excel entirely. In its place is a server-side spreadsheet library that needs no Office installation and no per-server licensing.

That created a deeper problem. Many of our customers use IBM Planning Analytics, where TM1 formulas are resolved by a native Excel add-in on the client side. Remove Excel, and those formulas go dark.

So we built our own formula parsing and calculation stack from the ground up. The engine replicates that add-in behavior without the add-in. Live data comes in and every formula resolves inside our own engine. That was not a small undertaking.

TM1 is just one example. Every data platform we support through Excel has its own formula patterns, and the engine has to handle each one correctly.

The result is a server-side document engine that treats Excel files as first-class citizens without ever launching the application. Sometimes removing a dependency is the feature."

Why can't you just automate Excel on a server?

Many teams try. A script opens Excel on a server, refreshes the TM1 workbook and saves a PDF. The approach works in a demo and then fails at month-end volume. Microsoft says plainly that it does not recommend or support Office automation from any unattended, non-interactive application (Microsoft Support). Excel was built for a person at a keyboard. Dialog boxes appear with no one there to click them, and processes hang in the background.

Removing Excel takes that failure point out of the process. The trade-off is that TM1 formulas need something else to resolve them, which is the problem Matt's team solved.

What this means for your team

If your reports come out of IBM Planning Analytics, someone on your team probably still opens each workbook to refresh it. Then they save the output and send it by hand. Multiply that by every cost center and the distribution cycle runs for days after the model is already correct.

When the report engine resolves TM1 formulas on its own, those workbooks refresh and go out overnight. Your analysts start the morning reviewing numbers instead of producing them. Your cost center owners get their reports from the same live data at the same time each cycle.