2

I was tasked to automate reports used by multiple teams. Each team have their own procedure on how to generate the report that they need to deliver.

I can automate the reports but it takes so much time since I need to study each of their procedures and then test them.

The issue occurs when a team needs to update a procedure. If I will perform that adjustment, then it might take a week to deploy that to the live server which is not acceptable to the customer.

What I am thinking was to create interfaces for the users so they can configure their own reports, but the amount of complexity that they need to consider is too much. It would be nice if a team's requirements will just be to filter field1 with this value and field2 with this but no. They do all sorts of stuff like merging values, applying conditions, etc. It's multi step every team and not all teams have the same steps.

Closest tool that I can use is django's report builder. But it can only satisfy the requirements up to a certain point.

Improve this question
10
  • Is this a "report", as in "a specific complicated database query and its output", or more of a "report" as in "Highly structured heavyweight-format text document, such as PDF or DOCX"?
    – Kilian Foth
    Nov 22, 2017 at 11:59
  • their source is an excel report, each team have their own report. they do stuff with it, summarize, merge fields and stuff, perform conditions, then produce an output
    – chip
    Nov 22, 2017 at 12:14
  • @niccolom: So you are saying input (= source) is an Excel document, not a database? And what is the form of the expected output? An Excel document, too? Please clarify.
    – Doc Brown
    Nov 22, 2017 at 12:45
  • ... Note if you have complex requirements, buying a commercial tool like "List & Label" (which is a framework for developing complex, customizable reports) may be your best option.
    – Doc Brown
    Nov 22, 2017 at 12:49
  • 1
    What do you mean by "automate" exactly. Excel sheets are self-contained and automatic.
    – Olivier Jacot-Descombes
    Nov 22, 2017 at 13:23

2 Answers 2

Reset to default
7

Programmatically transforming Excel sheets is not the way to go. I would review the whole procedure completely and use a database as data source for the reports.

Either let the users enter the source data directly into the database through an appropriate application or import the Excel data into the database (in an automated process).

Once that data is in the database, you can put the data into the right shape using database queries. This is much easier than manipulating cells in an Excel sheet programmatically. Based on these queries you can create reports using a (commercial) report generator. Modern report generators can create reports in various formats including a screen preview, PDF, HTML, RTF and even Excel.

Go away from this Excel bricolage.

A major disadvantage of Excel is that the data has no predefined structure. Users can enter the data in any way they want from highly organized to completely chaotic. This makes it extremely difficult to process it programmatically. A database on the other hand has predefined tables with predefines columns, predefined column types and predefined relations between the tables, which makes it much easier to implement automatisms, because you have a solid base to work on. While Excel sheets can be spread among different file systems and folders and can have random names, a database is usually located at a central and unique place and accessible through a predefined connection string.

Excel sheets may be very sophisticated, but are not scalable. I am developing software professionally. Many of my database projects (including applications as front-end) started with Excel sheets I got from customers. They were very skilled in creating complex sheets. But they reached a point where the sheet became too complex and could hardly evolve any more. The major problem is that complex data sets almost always contain 1-to-n relations and these can only be reproduced very badly in a spreadsheet. A spreadsheet is basically suitable for flat data only. Also, presentation and structure are the same in Excel. But often you need different evaluations and presentations of the same data. Here is where databases (back-end) with an appropriate application (front-end) and report generators come into play.

Improve this answer
8
  • You beat me with that kind of answer by a minute ;-)
    – Doc Brown
    Nov 22, 2017 at 14:09
  • @DocBrown: You are welcome to post your version of the story.
    – Olivier Jacot-Descombes
    Nov 22, 2017 at 14:12
  • thank you @OlivierJacot-Descombes for this answer. I get your point to really move away from Excel manipulation, I totally agree with you. Actually, this is another option for us. We will standardize the reports so all the teams are using the same report format, once that is implemented, we can now upload the excel files to the database.
    – chip
    Nov 22, 2017 at 14:32
  • 2
    I think your answer is excellent, straight to the point and I have currently no idea of what should be added or changed.
    – Doc Brown
    Nov 22, 2017 at 16:36
  • 1
    Reports configurable by end users are a complex matter. You can configure them in different ways. E.g. you could provide a set of predefined filters that the user can select and parametrize. It is usually sufficient to combine selected filter with AND. You can also provide a standard report with a tabular design where the user can select the desired columns. You need to define column meta-data for selectable columns. Besides the display name and table/column name you must define a minimum and maximum width and a format string. The more dynamic your report is, the more complex it is.
    – Olivier Jacot-Descombes
    Apr 18, 2018 at 12:32
3

Depending on how diverse your teams are and what the guts of your objective is, an approach to consider is:

  1. Providing one or two common reporting solutions to the projects and let projects that have more specific or divergent requirements create their own solutions.
  2. Implementing the one or two common reporting solutions as microservices, so that they stand independently from a lot of project specific elements - possibly even independent from the project data sources. For example an API that expects as parameters the report data and instructions (output format, template, destination etc) means each project that calls the API can obtain the data they require however they need to and then transform it for the API call.

Hopefully that gives you another way of thinking about the problem.

Improve this answer
3
  • yes this gave me some idea on how I can manage this issue. so you suggesting that it might be feasible to let the customers handle the part of the process that varies? that are too complex?
    – chip
    Nov 29, 2017 at 14:03
  • Yes, or too specific. Consider the data for the report used by different teams. Does the data all come from the same source? If not, immediately your solution would have to cater to multiple data sources unless your API expects the data to be provided with the API call.If the user has to pass the data, then the general reporting solution does not have to care about how to get the data etc. There's lots of variation of course on that theme (it might make sense to have the reporting system connected to all the relevant data sources). Start with a couple of projects and an API perhaps.
    – Paul Jowett
    Nov 30, 2017 at 1:12
  • 1
    Thanks Paul,I will keep this in mind when I talk to my clients next time. They have such high expectations, but I will bargain to set their expectations right. And to keep myself sane
    – chip
    Nov 30, 2017 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.