Reporting in ASP.NET

Question

I have an SQL Database and an ASP.NET website built to put data into the database.

One of the project requirements is to build a system that would let the user upload a Crystal Report to the server and run it as needed. This way, the user could create a customized report (for then turning into management, customers) that wouldn't force them to go through a developer.

I'm looking for suggestions on how to accomplish this goal.

Currently, I'm looking for a way to redirect the database connection in the Crystal Report from the database it was developed with to the database it will eventually run on. However, There doesn't seem to be a simple way to do this.

I'm also investigating the ReportViewer object. However, all the code I have seen involves specifying the query for the report in the code, which isn't acceptable.

One option (which I don't like at all) is to let them write their own queries so they can copy the results into Excel. This would mean a blank textbox and information about the structure of the database. Not a good idea for multiple reasons.

Another option is to create one report for each table (and maybe a few extras), let the user copy the data they want into Excel, and go on their merry way.

tl;dr How do I build a flexible reporting system?

=========================================
Continuation: 08/20/2012
I have decided to go the route of b.pell's extension methods. So far, it has gotten me closer than anything else. My code to bind to the CrystalReportViewer is below:

CrystalReportSource rs = new CrystalReportSource();
rs.Report.FileName = Server.MapPath("ReportFiles/") + Request["reportname"];
string connstring = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
rs.ReportDocument.ApplyCredentialsFromConnectionString(connstring);
rs.ReportDocument.ApplyNewDatabaseName("myDBName", "mySchemaName");
rs.ReportDocument.Refresh();

CrystalReportViewer1.ReportSource = rs;

This comes very close to working. It works fine on my dev machine, but when I run the code on the server, it gives the following error:

Logon failed.Error in File CrystalReport2 {5D2E82E5-783E-4DFD-A770-C8AE72A51E4E}.rpt: Unable to connect: incorrect log on parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: Logon failed.Error in File CrystalReport2 {5D2E82E5-783E-4DFD-A770-C8AE72A51E4E}.rpt: Unable to connect: incorrect log on parameters. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

The error is in this line in the code:

crTable.Location = String.Format("{0}{1}", prefix, crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1))

When I remove the call to ApplyNewDatabaseName, I am asked to enter the Server Name, the Database name, the Username and the Password or to select Integrated Security. I can't enter the Database Name or the Server Name (those fields are disabled).

Any thoughts?

Answer 1

I answer the changing Crystal Reports connection question a lot (it's something I'd think Crystal would make easier, but I wonder if they don't because that's what their server product does). :D Anyway, you can set the database credentials at runtime. Crystal is very particular in the order it's done, but I have some code that I turned into extension methods that do the trick. This code will go through the main report and all sub reports and change the connection information. This assumes that all sub reports connect to the same database that the main report does (if not, you'll need to modify it to handle multiple connections, but this rarely comes up at least with what I do).

Extension methods to change connection info: http://www.blakepell.com/2012-05-22-crystal-reports-extension-methods

It would be used something like this (although, you're binding to a viewer probably and not exporting, so you could ignore that, this is just for example).

Using rd As New ReportDocument 
    rd.Load("C:\Temp\CrystalReports\InternalAccountReport.rpt") 
    rd.ApplyNewServer("serverName or DSN", "databaseUsername", "databasePassword") 
    rd.ApplyParameters("AccountNumber=038PQRX922;", True) 
    rd.ExportToDisk(ExportFormatType.PortableDocFormat, "c:\temp\test.pdf") 
    rd.Close() 
End Using 

System.Diagnostics.Process.Start("c:\temp\test.pdf") 

You could use the Crystal Viewer at this point to deliver the reports and store the report in a database or on the file system (with a db meta data table) and have some predefined connections the user could select from that would be applied when it is run.

You also have the option to write your own front end. In this scenario a user would select a report from your meta data (you could put whatever security on it you wanted, I use AD). Then you can read the report parameters in and lay them out on the web form. When the user fills them in, you then sanatize them and pass them to the report via these extensions and you can output Excel, PDF, Word Doc, RTF, etc. A little more overhead and not the nice preview view, but can work well (I've done something like this in the past). Hope this helps.

Answer 2

About "...let them write their own queries" part of your question.

The solution can be to use some query builder component with friendly user interface which hides from users the complexity of your database and avoid any possible SQL injections.

There are few such products on the market. One of them is called EasyQuery, another one is build by Aspose if I'm not wrong. Try to search in Google for "query bulider for asp.net" or ".net query builder component".

Answer 3

I think what you're looking for is the Reporting Services, part of Business Intelligence Or maybe you can setup a UI that let the users pick the tables and columns they need for the report (this way you can limit the information they can access) an write a Dinamic Query Builder Function or something like that.