I would like a web-based system where I can publish a SQL query and users can ru
ID: 658840 • Letter: I
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
Explanation / Answer
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.