I am investigating for a tool that allows a user to perform in a user friendly way queries to database for extracting datas and creating reports.
Primary requirement is that we can't know queries users are going to do. So we need to design a flexible UI allowing them to specify in a non technical way.
My question is: do you know any tool that does something similar? Have you some inspiring user interface?
Many modern web dev frameworks let you make scaffolds from your daabases. which are simple web pages containing crud operations. where you can search and browse related records.
I personally have seen Linq2SQL for asp and Ruby on rails doing that. there are many more
For inspiration, there are five established approaches I know of:
Attribute List. For a given table, you (the designer) provide a list all attributes for which the user specifies the query attribute value. Sometimes the user also specifies the operators (e.g., Like, <, >, etc.); otherwise it’s fixed by you depending on the data type (strings use Like, numeric and date attributes use ranges). All criteria between attributes are combined by a logical AND. If you allow the user to list values for a single attribute, they are joined by a logical OR. Users generally assume that a blank attribute value implies that that attribute is not included in the criteria. Generally, you specify the table(s) that are likely to be of primary interest to users and hard-wire the Joins. Because the tables, joins, and Boolean possibilities are fixed, this has limited ad hoc flexibility, but for most cases it’s more than adequate. It’s the most common approach out there.
Query by Example. Users select the table(s) they want to query and you provide an empty multi-record form (e.g., grid) that includes all the attributes of the joined tables; that is, an empty query result (in some cases, the user selects the attributes to show too). The user completes the attribute values as if they were example records, where a blank attribute is assumed to vary. Thus, if Priority and Status values are entered for the same record, their attributes are combined by a logical AND. If they are entered on different records, they are combined by a logical OR. In some cases, the user may also specify operators for each attribute (e.g., to specify ranges of values). This provides a very high level of flexibility and seems to be relatively intuitive for users.
Diagrammatic Query. Tiziana Cararci’s Query by Diagram approach allows users to specify sophisticated Joins and Boolean by graphically manipulating an entity-relationship diagram. For more details, Google for:
Catarci T & Santucci G (1995). Diagrammatic Vs Textual Query Languages: A Comparative Experiment. Proceedings of the IFIP Work Group. 2.6 Working Conference on Visual Databases, (March).
Catarci T, Costabile M F, Levialdi S, & Batini C. (1997). Visual Query Systems: Analysis and Comparison. Journal of Visual Languages and Computing, 8(2), 215-260, (June).
Graphic Filter Query. Ben Shneiderman’s Filter Flow approach allows users to specify sophisticated Boolean by constructing visual networks of operators and criteria, leveraging a plumbing or electrical metaphor. Google for:
Shneiderman B (1991). Visual User Interfaces for Information Exploration. Proceeding of the 54th Annual Meeting of The American Society for Information Sciences, 28 (Washington, DC, October).
Murray NS, Paton NW, Goble CA, Bryce J (2000). Kaleidoquery: A flow-based visual language and its evaluation. Journal of Visual Languages & Computing, 11(2), 151-189 (April).
Natural Language Query There’s been a lot of effort to try to parse natural language or semi-natural language into a structured query, but it hasn’t had much success, partly due to the ambiguity of natural language (e.g., in “Commission income of all salespersons assigned to Great Britain and Ireland,” the “and” could be interpreted as either a logical OR or AND). You could try returning multiple results (one for each interpretation) for the user to choose (sort of like Google). This approach may be adequate for unskilled users and for when a sufficing rather than perfectly correct result is sufficient.
"user friendly" and "non technical way" sounds like Ad-Hoc reporting. In this case you can take a look in this Ad-Hoc reporting demo. But there are many other Ad-Hoc reporting tools.
"non technical way"? Intuitive way - yes. I do not know how to make master-detail report more easy. It is w/o any SQL and so on.
Take a look at this query builder demo. It is based on EasyQuery library. The same functionality can be implemented in standalone application. But it is a library, not a ready-to-use tool. So you will need some coding to use it.