Reporting Tools

[500Foods]

At some point very soon, we'll need the ability to generate reports. The reports I'm thinking about here are of the highly structured variety - like multi-page invoices, account statements, and other financial reports. But also many other kinds of reports on any kind of data that we might be dealing with.

Must Haves:

• Generally, just PDF output is fine
• Images of all kinds
• Subreports
• Multiple charts
• Report headers and footers
• Page headers and footers
• A proper UI for us to use as developers

Nice to Haves:

• Output XML, XLS, CSV, etc. (though we can do that ourselves, often)
• Client report customization UI (separate Windows app)
• Client report customization UI (via the same web client app)

We already have everything we need from our project for handling users and so on. I've yet to see a good UI in a web client for doing this. Most, if not all, of the choices in this space are only available for a fee ($) if you want to use it for business purposes.

My preference is to use Digital Metaphors' ReportBuilder product. I've used it for a very long time and it has all the features I can envision ever needing. But it, too, is not free. And that would be yet another paid-for dependency, on top of, of course, TMS XData (and TMS WEB Core using our client).

Even with ReportBuilder, I'm hesitant primarily because I don't know whether it will still work if we create a Linux deployment of our current XData server. Specifically, the report-generation aspect. Client-side editing would likely still be done via a Windows app. And we might be able to pass enough parameters for most reports to make this less of an issue. I've not got enough experience with XData on Linux to know what the limitations are. Will be working on that in the interim.

Another option is to use a "report server" mechanism, where the reporting is done outside of XData, perhaps using a REST API or some other communications interface. In that approach, we can just create a separate project (or several) that we can call on to provide the report, passing it whatever data we like.

This might be the best approach, as we'd be able to set up such a server using ReportBuilder, and maybe another using another tool, and our current XData project wouldn't have to change. We'd just pass it JSON data. It would then put that data into a local DataSource or whatever it needs, and build the report off that.

Thoughts?
Other Approaches?
Other reporting tool vendors?

[wlandgraf]

I'm biased, but if you can't escape from a paid alternative, I'd recommend TMS Flexcel. Fast, thread-safe, cross-platform, works on Linux, works fine server-side, generates PDF, and the "template" can be edited anywhere - Excel, OpenOffice, Google Docs, etc., as long as a Excel-compatible xls (or xlsx) can be produced.

[500Foods]

Yes, I 've seen that as a bit of a commonality - using OpenOffice/Excel/etc. as the reporting template and replacing the contents with something else - the data for the report. I can't say I'm a fan of that at all, personally, but I do see the appeal from a few angles. Client-side customizations being the biggest one.

Perhaps multiple related projects will be necessary here. A sort of plugin system if you will. Maybe even a separate XData project for each, setting up report servers. This project (the TMS XData Template Demo Data) would potentially do all the work to gather up the data, as we're doing in some endpoints already, and then make a separate REST API call to a reporting server, passing it the data we've collected, the name of the report, rendering timezone, etc. and getting a PDF report back.

We could then have separate servers running, potentially. The "report definition" would indicate the type of report, and a configuration item somewhere would indicate where to go to get it. One server for FlexCel, one for ReportBuilder, mix and match with whatever the project decides to deploy. Maybe even one for FastReport - seems there's a free version included in Delphi? Been using ReportBuilder for so long, I'm not even sure anymore 😏

This would also make it possible to either expose the report server to the same audience as the main XData project, or have it remain hidden behind it, just using its REST API for IPC (or for scaling, more likely).

I think we have plan 😄

And another mega blog post in the making 🤣

[500Foods]

Just for a bit of context, my aversion to the approach of using OpenOffice/Excel/etc. (the Grinch in me really does loathe this idea entirely!!) springs from a few different wells...

1. I've often worked on reports where the ability to fully control the page layout, particularly with charts and subreports, is rather important. Financial reporting, for example, where consistency is perhaps most important.
2. For complex reports, having anyone other than the appropriate people editing reports is important. This has largely removed the need for "client editing" but here we have the problem of defining the client - end users versus IT professionals or others who are capable of writing correct reports. Naturally we want to have the ability to customize the reports. Having tiers of access over who can edit the reports, or even tiers of reporting tools, may be necessary but not all that desirable.
3. I've spent far too much time over the past few decades replacing Excel with actual reports so that people (and Excel itself) can't mess them up. Having Excel involved in any part of the reporting toolchain is something that will give me nightmares for some time. But that's just me 😏 I can learn to look the other way 🤣

[wlandgraf]

Of course, those are valid points.

I was just focusing on the items you mentioned, like being cross-platform, Linux compatible, PDF/XLS output, cross-platform UI customization, etc.. All that is supported by Flexcel.

Now using XLS(X) files as a template is a matter of taste and also application. Sometimes it's great, sometimes it might not. You mentioned one interesting point, by the way, which is the fully control of the page layout, and I find it interesting because personally that's exactly one of the points that I personally prefer Flexcel. Because Excel itself has many UI tools to make it easy for you to deal with layout - margins, fonts, alignment, cell size, scripts, auto width, auto height, toolbars, the ribbon etc..

Also another personal reason is that in the past I build software that had to create reports with a specific layout due to legal issues, or to follow standards, etc.. With Excel is very each to find a template that you have it already it almost done - all you do is to fill the values or tweak it.

With a report editor, you have to do it all by yourself from scratch. A report editor is something made by a much smaller team, so I consider the visual tools could never be on par with a general tool like Excel. I feel painful to visually edit things with FastReport or QuickReport (never used ReportBuilder) - having to be pixel perfect, move things from here to there, copy and paste, align, build a table, etc.

But again, it's a matter of taste, personal experience, and also the application. I never believed in silver bullets. There is a tool for each need.

[500Foods]

Well, as the plan here is to create a template for use with most anything related to running a business, we'll need a few options. Even one for creating reports from thin air just generating a PDF directly - also likely to be multi-platform! For those who like that sort of thing. I think as soon as I had posted this originally, the idea of just building separate report servers came into view, so we don't have to pollute this project with any more dependencies and can instead have that handled in separate projects, with developers able to pick and choose amongst them, just wired up via a REST API. Also means they can use other REST API-based reporting tools if they wanted. But why would they if they could use XData instead 😄

[500Foods]

https://github.com/500Foods/TMS-XData-TemplateReporting