The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Generating Custom Reports

My client has asked to develop a Custom Reporting Solution where the user can select any field from the database tables and the criteria to display any type of report based on his query. Please let me know how to develop this type of application. The users have no knowledge of any querying language like SQL. So the application must facilitate the user in creating criteria for reports.
K Send private email
Thursday, September 14, 2006
Run away, very fast.
Cade Roux Send private email
Thursday, September 14, 2006
Seriously, they may as well just buy Cystal Reports or similar.
Cade Roux Send private email
Thursday, September 14, 2006
SQL Server Reporting Services 2005 has a custom report generator built into it. Try that.
Chris McKenzie Send private email
Thursday, September 14, 2006
Run away and don't look back. ;)

This is a common request but the problem is not getting at what is in the database. The problem is combining the results into meaningful values through computations, business rules, and equations. No one ever just uses raw data returned from a query. How do you plan on allowing the user to sum up rows in the result or do things like adding computed columns? It ain't easy is it?

So don't write this yourself. There are existing tools that provide people with the ability to create custom reports. Just recommend one of those. Several that come to mind are Crystal Reports, Access, InfoMaker, and SQL Server Reporting Services.

As a side note, I had a higher-up once try to convince me that giving users the ability to create custom ad-hoc reports would be a good thing. I proceded to tell him that I didn't agree. Since we could create any custom report for our customers in less than 4 hours, we would have been better off giving away free reporting enhancements than to spend the hundreds if not thousands of hours writing reporting products and supporting them. Because don't think for one minute that you are going to hand someone a data dictionary for you product and they are going to successfully write reports from it without consulting you! And when their report doesn't work, who are they going to call?!?!
dood mcdoogle
Thursday, September 14, 2006
You could also use a query builder engine like the one in this demo ( : you may have to write one yourself). I did something similar with a query builder class and associated editors written in .NET, and integrated it into MS Word MailMerge using COM wrappers.
Chris McKenzie Send private email
Thursday, September 14, 2006
"How do you plan on allowing the user to sum up rows in the result or do things like adding computed columns? It ain't easy is it?"

The Dood is right.  The reason the users aren't creating reports isn't because they don't have access to a good reporting product, it's because they don't have the skills nor knowledge to create them.

Let's face it - the average user can barely create a simple spreadsheet or Word doc, never mind a report that pulls data of different formats from a data store and then arranges and aggregates it ... easier and more cost-effective to just do it for them.
Think outside the box Send private email
Thursday, September 14, 2006
Assuming that your users CAN understand the basics of how queries work, we've had good luck with a basic form that lists all of the columns as checkboxes, and next to each checkbox, a text field that accepts a "search parameter".

Err...  think of it as a two column table.  Left column has a list of table columns, right column has a list of text fields or drop down boxes.

However, this approach does only work for data that's contained in a single table or is intuitively joined with reference tables (if a user wants all data from a specific State, the system is smart enough to join with the States table).

For data mining, or the ability to combine multiple tables and use aggregate functions, yes, you're better off just purchasing reporting software.
Thursday, September 14, 2006
We do something similar to what TheDavid describes, but have a bunch of metadata & sql fragments behind the scenes. At runtime, those extra bits are used to assemble the queries, some of which can be complicated & nasty.

We're looking at SQL Server Reporting Services & some lightly denormalised views to replace it; putting a 'user-friendly' face on it is our issue.

Still, it all comes down the to end user a) knowing what they want and b) having some rudimaentary understanding of the structure of the data.
a former big-fiver Send private email
Thursday, September 14, 2006
The 'doods' post was good from one side of it.

The users are gonna have trouble doing it themselves.

But the other side of the coin is performance.

If I have a date range on the sales report, what stops the user from pulling back 5 years of sales records?

This is gonna screw up everything else running against that server.

Now, as to what you can do that will get you some of the requrements.

SQL Reporting Services 2005 has something called a 'model'.

You create a model in Vis Studio 05, and then deploy it to the reporting services site like a report.

The model is a 'view into the data' basically an abstraction on top of the database tables. Like a view in a way. So the user can make a new report based off the model.

They don't need to write sql, and when the report runs it actually parses their 'query' of sorts and turns it into TSql.

I haven't personally used it, so I don't know how it performs performance wise.
D in PHX Send private email
Friday, September 15, 2006
Use DBxtra
Rudolf F. Vanek Send private email
Friday, September 15, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz