I need to code a dynamic report builder in ASP.NET, where should I start?

Question

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.

Foxhound

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. – Unequal

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. – Foxhound

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. – Unequal

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 – Prissie

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). – Foxhound

You can use crystal reports. itzonesl.blogspot.com/2012/11/… – Br

I've just come across your question today. Given that we're over two years on, what did you eventually decide to do? – Couchman

Answer 1

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

Prissie

MS Access brings its own problems. Generally these users then start building their own application off the database and all of a sudden you have an undocumented MS Access system that is heavily depended upon that you have to support or reintegrate into the main application. – Pender

Answer 2

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.

Outset

I like the idea of 'big table' approach using views and excluding keys, etc. Concerns that come to mind are how do you handle aggregate data, rollups etc. I've created some custom interactive reports that work off the idea of having all the information available in one big table and allowing the users to use checkboxes or whatever to decide what is in and out, but how to you allow the users to access aggregate stuff? I have a couple of reports that involve 10 table unions and sums and everything, it gets a bit sticky. Thanks for the comment! – Foxhound

yes, I too like this approach and am wondering about the aggregate data. – Marketing

Answer 3

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

Impotence

Answer 4

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.

Cherise

Perhaps I should have used the word illusion, but thats the idea. I want to create the illusion of effortlessness, while all the heavy lifting is happening invisibly and behind the scenes. Thanks for the comment! – Foxhound

Answer 5

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.

Radii

Answer 6

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.

Lx