Creating user generated reports

Question

Hello Everyone,

I need users to create custom reports. These users do not know any technical skills like SQL. We currently have a custom database report design. So basically whatever the user does on the GUI the application will have to generate the appropriate SQL to generate the report structure.

Has anyone done this before? I know there are reporting solutions out there but we already have our own database tables for reporting. We already have a section where users can view reports displayed in HTML.

Like for example if user selects a "UserID" and "Accounts" fields from GUI, how would I know that my SQL has to join the USER and ACCOUNTS table?

I guess I'm just looking for some ideas to help me solve this problem.

Thanks.

Answer 1

The question you listed is a rather broad and open ended one that does not have a simple answer. The complexity of the reports and GUI interface you have will determine what you have to do. I've had to write a reporting system from scratch for a project and it was an 8 month 3 person development effort. The fundamental basis of what you want is a way to map your GUI fields to database fields. In both reporting systems I've worked on in the past we've used XML to achieve this (the XML can get very complex as there are a lot of parts that you need to generate dynamic queries).

My advice is as a starting point look at JasperReports. In particular you may find iReport (for generating static reports) useful. There are also some technologies in that stack (DynamicReports and WebReportBuilder) that I don't have experience with but may be useful to you.

Answer 2

There are a number of reporting solutions, such as Business Objects or Oracle Discoverer, which enable end-users to drag-and-drop fields, filtering conditions etc. as required from a pre-designed view of the data.

These work best with data schemas that have been designed for reporting purposes (such as star schemas), as it sounds as though yours have. I would be wary of trying to use such tools to report from normalised, operational schemas.