Allowing a user to create a custom query of a table

Question

I am writing a program that generates a single large table of information. The table is made up of two types of columns, columns that contain quantities, and columns that contain properties. Quantities are numeric values that can be summed, properties are qualitative values that attribute the quantity values.

If the data is in a table in a database I can write a query that selects specific properties and quantities and sums the quantities that have the same value for the selected properties.

Example:

Table:

Quanity1    Quanity2 Quanity3 Property1 Property2 Property3
12          43            12           RED        Long      Rough
43     23      23      Blue    Short    Smooth
43        90      34       RED     Fat  Bumpy

Query:

SELECT sum(Quanity1), sum(Quanity2), Property1 FROM Table Group By Property1

Result:

Quanity1    Quanity2 Property1
43      23       Blue
55        133         Red

What I want to do is give the user a graphical interface to do this with out knowing how to write SQL queries, or any code for that matter. Such as a set of list boxes where they select the properties and quantities they want to view and a table is displayed that shows the selected fields with the quantities summed. I may also later want to add the ability for the user to perform other SQL query like actions such as filtering based on certain conditions. Also I know later I'll need to be able to generate nice looking reports based on these user Queries.

I'm very new to ADO and .NET in general. But I'm thinking the best way to do this is to export my data into a System.Data.DataTable and then create an interface for the user to create a System.Data.DataView by generating a string for it's RowFilter property. Although, it's not obvious to me how I can not only filter and sort a DataTable but generate another Table or view that only contains specific columns from the big master table.

Overall does this sound like the best option, or is there another method I should consider? Does anyone have any specific tips or suggestions on how I should implement this? I was also questioning if any of this would be made easier with LINQ.

Update I appreciate the suggestion of using Access or other available tool, but it's really not an option. Access is way too complicated for users here to try to figure out, and much more then I actually need. I'd always leave Access as an option for advanced users. But I would still like to setup a basic querying feature where the user selects the columns they want and the software automatically creates the view/query that selects and sums the appropriate columns.

Aside from being to complex the other issue with Access is there are to many clicks between changing something in my data structure and seeing a change in a report. I don't want the user to have to change something, re-export to access, open another program, and then open the report to see the effect of their change.

Answer 1

Consider buying an off-the-shelf query tool rather than re-inventing the wheel. The cheapest one that could do this sort of thing is MS Access or MSQuery in Excel. More elaborately you could use Report Builder (if your database is based on SQL Server - it comes for free with this) or a third-party tool such as Business Objects or Brio.

If you can live without tight integration this is far easier than trying to build your own ad-hoc query tool.

Answer 2

I also strongly recommend off-the-shelf - especially early on. If it becomes apparent later on that the users really need you to write a custom solution, then by all means go for it. But this early on I don't think it will be worth the time and effort you will spend.