I am currently designing a functionality in my Web Application where the verified user of the application can execute queries which he wishes to from the predefined set of queries with where clause varying as per user's choice.

For example,Table ABC contains the following Template query called SecretReport

"Select def as FOO, ghi as BAR from MNO where "

SecretReport can have parameters XYZ, ILP. Again XYZ can have values as 1,2 and ILP can have 3,4

so if the user chooses ILP=3, he will get the result of the following query on his screen "Select def as FOO, ghi as BAR from MNO where ILP=3"

Again the user is allowed permutations of XYZ / ILP

My initial thought is that User will be shown a list of Report names and each report will have parameters and corresponding values. But this approach although technically simple does not appear intuitive.

I would like to extend this functionality to a more generic level. Such that the user can choose a table and query based on his requirements. Of course we do not want the end user to take complete control of DB. But only tables and fields that are relevant to him. At present we are defining what is relevant in the code. But I want the Admin to take over this functionality such that he can decide what is relevant and expose the same to the user. On user's side it should be intuitive what is available to him and what queries he can form.

Please share your thoughts what is the most user friendly way to provide this feature to the end user.

  • Your question in the current form is pretty unclear - why do you thing a list of report names & parameters is not intuitive? Shall the user be able to enter completely arbitrary WHERE conditions, or just combinations of parameter comparisons, combined by "AND", or do you have the goal to allow something in between? By the way, IMHO your question title does not reflect well what you are asking. – Doc Brown Jun 9 '14 at 11:44
  • 1. My feature is basically wrapping DB for the user. So I thought it to be a good title. Do you suggest anything better? I can change it. – Gaurav Parmar Jun 9 '14 at 12:01
  • Well, according to this logic, any DB application wraps a DB for the user. But your question deals with dynamic query generation, I guess? And you did not answer my questions - I encourage you to do so by editing the question and add the missing pieces. – Doc Brown Jun 9 '14 at 12:06
  • Changed the subject. As I shared in the question the list of report name and parameters is one way of doing things. But I felt it is very basic thing that one can do. I am expecting there is a better way to present it to the end user such that its intuitive for the user to learn. For example previously they used to prepare help pages in web applications, now everyone provides simple walk through. That sort of intuition makes an application attractive. Thanks for your reply though. – Gaurav Parmar Jun 9 '14 at 12:09
  • Sorry, but I still don't see any explanation to my question for why you think your approach is not intuitive in your case. That is probably because you did not provide enough context. – Doc Brown Jun 9 '14 at 12:14

My initial thought is that User will be shown a list of Report names and each report will have parameters and corresponding values. But this approach although technically simple does not appear intuitive.

I disagree.

If your reports aren't couched in terms the Users understand (i.e. intuitive), then who did you write them for?

You create a set of reports (with "titles" that mean things to the Users).

Then you add parameters to those reports, again with User-meaningful "descriptions"). It's rare - but not unheard of - for Users to "talk in codes" so favour descriptions over hard-coded values (e.g. what do "1" and "3" mean?)

Buried inside your application, all those "titles" and "descriptions" get converted into database terminology - tables, [joins?], fields, conditions, etc., so that you can build some SQL and actually run it on the Users' behalf, but the Users don't get to see any of that. Ever. Not even if the generated SQL is syntactically incorrect (log the "real" error to a file, show the Users something trite but apologetic).

Please note: I keep saying "Users" - plural.

You will have different User communities, with different levels of access to different sets of data. Make sure that your application can handle this and that your "Administrators" understand it as well. "Data Protection" legislation varies widely but the basic premise of "don't let anyone see something they're not allowed to" holds true pretty much everywhere and can cost you a lot of money if you get it wrong.

  • Thanks for your answer. I got some clarity from that. I was not looking for exact solution but some thoughts. I think your answer was great. – Gaurav Parmar Jun 12 '14 at 8:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.