I've been tasked with creating a dynamic report builder to extend our current product that should allow our users to configure with relative ease a useful report drawing data from what they've inputted into the system. Currently we customize these reports manually, and this process involves a developer (me) taking the requirements of the report (fields, aggregate totals, percentages, etc) and publishing the results as a relatively interactive page that allows for the ability to 'drill down' for more information in the record rows, etc.

The reports are not extremely complicated, but they're involved enough that programmatically generating these reports doesn't seem possible. I feel like creating an interface to allow the users to customize the look of the report shouldn't be too difficult, though involved in and of itself. Where I am at a loss is how to create an interface that will allow users who have absolutely no 'programming' literacy the ability to easily generate the SQL queries that will pull the information they need.

In fact, they need to be able to create these queries and access the bowels of their inputted data without ever being aware of what they're really doing. I feel for this to work as required, the generation of the report has to be as indistinguishable from magic as possible. The user should be able to drag and drop what he/she needs from sets of possible data and magically produce an report.

I'm up for the challenge of course, but I really don't know where to start. Once I get the gears moving, resolving individual issues will be 'easy' ( well actually more like part of the process), but getting off the ground has been challenging and frustrating. If anyone can offer me a direction to search in, I'm not afraid of putting in the hours. Thank you for your time, and I look forward to some positive suggestions.

I've been tasked with creating a dynamic report builder to extend our current product that should allow our users to configure with relative ease a useful report drawing data from what they've inputted into the system. Currently we customize these reports manually, and this process involves a developer (me) taking the requirements of the report (fields, aggregate totals, percentages, etc) and publishing the results as a relatively interactive page that allows for the ability to 'drill down' for more information in the record rows, etc.

The reports are not extremely complicated, but they're involved enough that programmatically generating these reports doesn't seem possible. I feel like creating an interface to allow the users to customize the look of the report shouldn't be too difficult, though involved in and of itself. Where I am at a loss is how to create an interface that will allow users who have absolutely no 'programming' literacy the ability to easily generate the SQL queries that will pull the information they need.

In fact, they need to be able to create these queries and access the bowels of their inputted data without ever being aware of what they're really doing. I feel for this to work as required, the generation of the report has to be as indistinguishable from magic as possible. The user should be able to drag and drop what he/she needs from sets of possible data and magically produce an report.

I'm up for the challenge of course, but I really don't know where to start. Once I get the gears moving, resolving individual issues will be 'easy' ( well actually more like part of the process), but getting off the ground has been challenging and frustrating. If anyone can offer me a direction to search in, I'm not afraid of putting in the hours. Thank you for your time, and I look forward to some positive suggestions.

• javascript
• asp.net
• sql
• sql-server
• report

• 2 I don't know if you are seeking an out of the box solution, but have you investigated the report builder that comes with SSRS? It behaves like an Office application (albeit a complex one) and doesn't require much technical acumen to build reports. An Excel power user could master the basics. – Tim M. Commented Dec 28, 2012 at 21:58
• Good eye, but we've discussed that and the powers that be want something created in house that requires no previous knowledge: Something like a drag and drop click here UX. I'd also briefly looked into things like Crystal Reports, but I got shot down early on with that path. Thanks for your comment. – Francisco Garcia Commented Dec 28, 2012 at 22:02
• Well, Google Analytics has a drag-and-drop UI for creating simple ad hoc reports...you could show it to your business users and see if the paradigm is simple (yet robust) enough before you spend time building anything. Obviously Google has put a lot of time into it, so actually building such a thing isn't trivial. – Tim M. Commented Dec 28, 2012 at 22:05
• At my previous job we wasted months trying to design and build exactly what you describe, in a 2 developer team. Unless you plan on dedicating the next 2 years(or more) on building this, you management needs to consider alternatives or if out-of-the-box solutions will work – Earlz Commented Dec 28, 2012 at 22:07
• I definitely don't want to be working on this for 2 years! This has been bubbling to the top over the course of three or so months, as the output of custom reports is overwhelming our resources, we need to act 'fast' (or at least have a long term plan). – Francisco Garcia Commented Dec 28, 2012 at 22:10

6 Answers 6

I was tasked with something like this before. My advice: don't. Unless the required reports are extremely basic and your users don't care about how the report looks, it'll take a significant amount of time to implement. With you indicating your a single person team, just don't. It'd be cheaper for you(even in the long run) to hire a junior developer or intern or something to handle this part of the job.

Now, there are a few different report designers out there. I've not seen any that work completely on a web page, and all of them sucked pretty bad from the non-programmer perspective.

Now, there are ways around this. Most of the people wanting these types of reports know how to work Microsoft Access. You can leverage their knowledge of this to let them create their own reports. This isn't trivial though as you don't want them just connecting to your database. So, here's what I recommend:

1. Generate a downloadable database compatible with Access
2. Ensure that the downloaded database is "easy" to work on. This means duplicating data and denormalizing a lot of things
3. Ensure you don't leave anything sensitive in the downloadable database (passwords, internal things they shouldn't see, etc)
4. And finally, ensure they can download it in a secure manner and that it's performant. You may need to tell your users the downloadable database is only "synced" once a week or month since it's relatively expensive to sync this in real-time

Have a look at what data warehouses do (e.g. The Data Warehouse Toolkit). They create several basic table that are very wide, contain a lot of redundant data and cover a certain aspect of the database.

I would create several such wide views and let the users select a single view as the basis for a dynamic report. They then can chose the columns to display, the sorting and grouping. But they cannot chose any additional tables or views.

Of course, a typical view must really cover everything regarding a certain aspect of the database. Let's assume you have an Order Items view. Such a view would contain all items of all orders offering hundreds of columns that cover:

• The order product ID, product name, regular price, discount, paid price, price incl. the associated part of the shipping cost etc.
• The order ID, order date, delivery date, the shipping cost etc.
• The customer ID, customer name, customer address etc.
• Each date consists of several columns: full date, day of year, month, year, quarter, quarter with year etc.
• Each address consists the full address, the city, the state, the area, the area code etc.

That way, the dynamic reporting is rather easy to use because the users don't need to join any tables but have all the data they need.

I'd recommend you to look at ready reporting components. For example Microsoft's Reporting Services, Telerik, DevExpress or (I should confess, our product) SharpShooter Reports

Start looking around, what kind of reporting tools are there? Is there anything out there that even comes near what they are expecting? The tools around will be generic and your case might be rather specific. You already know part of the answer your users are looking for. Your solution should help them that way.

You used the word "magic". That should be a huge warning sign. As a developer, we don't do magic, we do logic. We can create illusions, we can't do magic.

I would dive into Sql Analysis Services and Excel. There is a presentation over here. These guys don't do magic either, but they are able to do a lot.

We use a combination of EasyQuery and FastReport.NET.

EasyQuery allows our users to build dynamic queries and extract the data necessary for report and FastReport - for actual report generation and exporting it to Excel or PDF.

Take a look at zpmsoftware.com. It has an open source report builder for ASP.NET MVC and outputs to screen, excel and pdf. Not to much trouble to adapt to Webforms. Since most of the fancy stuff is in jquery/javascript, adapting to other server environments should be doable.