Self hosted web-based reporting system with simple output defaults

Question

I would like a web-based system where I can publish a SQL query and users can run it with the least amount of friction possible. Ideally, I wouldn't need to do much more than configure the data source and paste in the query.

Right now, I give users access to a SQL query by embedding it into Excel as a linked query and emailing the Excel file to them. I use the "Format as Table" option to format alternating rows with a different color schemes and enable column filtering, and auto fit the column width (with some manual adjustments for columns that are way too wide because of one or two outlying text fields). That's about all the "report" formatting I do; I'm looking for a web-based solution that approaches that level of simplicity.

The downside to using Excel for report distribution is that other users (or administrators) can't discover this query. Also, I can't make any changes or improve the query without re-sending the Excel file (and, even then, I don't know who else the file has been sent to). I'm looking for some sort of central repository to solve these sorts of problems.

I've setup a couple of Pentaho Reporting reports, and, when using the Report Design Wizard for the initial setup, it isn't too bad, but it's a PITA to go back and make a modification like adding a column or adding a new group for banding. Maybe publishing a query directly using the Pentaho BA/BI Server is more along the lines of what I am looking for, but I never got that far when working through the various Pentaho tutorials.

If I were to try writing something like this myself I would probably try to use something like DataTables, SlickGrid, or Handsontable for displaying the data grid on the web page.

Requirements:

• Support for simple tabular reports
  • Automatically format reports with good defaults
• Can be open source (preferred) or commercial
• Supported Data sources:
  • MS SQL Server
• Web server or server OS required:
  • Any

Optional features:

• User authentication
  • Ideally, RBAC that can integrate with MS Active Directory (AD) and utilize groups in AD
• Export/download data grid to a CSV, XLS, or XLSX file
• Basic graphing
• Pivot tables
• Support for running/viewing reports on mobile platforms like the iPhone 5c or iPad Mini
  • Responsive design that uses the maximizes the use of the available web browser resolution
• Supported Data sources:
  • IBM DB2 on AS/400 (iSeries)
  • SAP NetWeaver ERP
  • Arbitrary ODBC data sources
  • Arbitrary JDBC data sources
  • MySQL
  • PostrgreSQL
  • MariaDB
  • Oracle
• Web server supported:
  • MS IIS
• Backend server programming language:
  • Python
  • Java
  • C#
  • PHP

I don't need:

• "pixel-perfect" reports

Answer 1

There's always SSRS: Sql Server Reporting Services. I have a love-hate relationship with this software, and even though I've often wished and searched for better alternatives (besides writing something yourself), based on your requirements, I'd at least recommend you give it a try.

The reason I do recommend it is because it technically holds up to just about all of your requirements, except for being open source. The reason I recommend this with "give it a try" is because the administration and creation of reports will have some quirks.

With SSRS you create your query either in the report itself or in a database view/sproc and drag/drop the data onto the designer. You deploy the report usually to a report server, which users can access in various ways: through a Sharepoint site, a custom website with a ReportViewer control, directly through the SSRS site, or they receive it by mail.

Requirements

Here's how it holds up to your requirements:

• Support for simple tabular reports: Automatically format reports with good defaults

  Decent enough. The report wizard creates a reasonable default, as does creating a blank report and dragging fields onto a fresh table. Only column widths are always fixed width by default, but it's easy to change this.

• Can be open source (preferred) or commercial

  Alas, it's properietary.

• Supported Data sources: MS SQL Server

  Got that, of course. I think other data sources would be possible too.

• Web server or server OS required: Any

  That's an easy requirement :S. Anyways, this runs on Windows.

Optional features

Here's how it holds up to your other requirements:

• User authentication: Ideally, RBAC that can integrate with MS Active Directory (AD) and utilize groups in AD

  All sorts of options here, depending on how you give your users access to the reports. Sharepoint and AFAIK the SSRS website have this by default (though I have no experience with this), and a custom website could be similarly equipped. Distributing the reports by mail (data-driven or periodically) might be an out-of-the-box alternative here.

• Export/download data grid to a CSV, XLS, or XLSX file

  Yes. Also available: (m)html, pdf, and xml (though not too well formatted).

• Basic graphing

  It's in there, though not the prettiest graphs in the world IMHO.

• Pivot tables

  Yes, it's got that, but it's best suited for aggregated data or smallish data sets. For (very) large datasets you may want to look into other solutions.

• Support for running/viewing reports on mobile platforms like the iPhone 5c or iPad Mini; Responsive design that uses the maximizes the use of the available web browser resolution

  The html viewer works okay on mobile devices, though I'd hardly say you can do responsive design with SSRS reports.

• Supported data sources: various

  Most of your sources are listed as supported. I only have used Sql Server, but I see quite a few people on Stack Overflow hinting they use Oracle or others.

• Web Server supported: MS IIS

  Well... SSRS is either a standalone installation or an addition to your Sql Server installation. It has a built in simple website that is self-hosted (I think). You can serve reports with an IIS hosted web application or Sharepoint, though Apache and a PHP site are possible too.

• Backend server programming language: Python, Java, C#, PHP

  C# is there, obviously. There are quite a few folks that have tried and gotten PHP sites to access the reports. It's probably possible in other languages too, if even only using the SSRS SOAP web service to stream exports to clients.

You don't need

• "pixel-perfect" reports

  Well good, because madness lies there with SSRS!

Conclusion

Like I said in the intro: I have a love/hate relationship with SSRS, but since it seems to technically stack up against your requirements, it seems worth a try.

Answer 2

DISCLAIMER: I'm a DBxtra evangelist.

I recommend you to try DBxtra, as it covers most of your requirements and it's easy to create and modify reports as you want, on DBxtra you can simply create the data connection, create a new report object, either create or paste the query, save and your customers will be able to see the report on the web immediately; additionally, you can give a little bit of formatting to the data grid so it's easier to use or understand the data you're presenting.

Against your requirements this is as it goes:

Requirements:

• Support for simple tabular reports: Automatically format reports with good defaults

• We have a simple tabular report (the Data Grid), which have some nice features like grouping, sorting and filtering on his own.

• Can be open source (preferred) or commercial

• Commercial, with a fully featured 30 days trial.

• Supported Data sources: MS SQL Server

• We support direct connections to SQL Server using either Windows or SQL Server authentication.

• Web server or server OS required: Any

• DBxtra requires IIS to run, so it requires Windows (Server or normal versions), clients can use any web browser they want, to use DBxtra.

Optional features:

• User authentication: Ideally, RBAC that can integrate with MS Active Directory (AD) and utilize groups in AD

• You can use either our built it in users authentication mechanism or you can integrate DBxtra with Active Directory.

• Export/download data grid to a CSV, XLS, or XLSX file

• The data grid can be exported to either format.

• Basic graphing

• Available on pivot tables (Pivot grid), reports and dashboards.

• Pivot tables

• Available as the Pivot Grid.

• Support for running/viewing reports on mobile platforms like the iPhone 5c or iPad Mini

• We have tested DBxtra on mobile devices and it worked fine, the design it's not responsive, but everything is functional.

• Supported Data sources:

• IBM DB2 on AS/400 (iSeries): Supported either directly or trough ODBC (iSeries) driver.
• SAP NetWeaver ERP: Not supported but you may be able to connect to the underlying database (SQL Server or Oracle) or use a third party ODBC driver.
• Arbitrary ODBC data sources: Supported
• Arbitrary JDBC data sources: Not Supported, however most JDBC data sources may have an ODBC driver.
• MySQL: Supported directly
• PostrgreSQL: Supported directly
• MariaDB: Not Supported, but you may try with MySQL's direct support or through ODBC
• Oracle: Supported directly

• Web server supported: MS IIS

• DBxtra requires IIS

• Backend server programming language:

• No programming needed, only some SQL knowledge; however, if you want to tweak the reports you can use scripts either on C#, VB.NET or JScript; if you want to embed the reports on other applications, you can use a simple HTML snippet (Web Report Widgets)

I don't need:

• "pixel-perfect" reports

• We have them, but if you don't want to use them, you don't need to.