Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Question Unanswered: Ideas in designing "Ad hoc capability" feature

    I am looking for a solution to allow users to generate data by selecting tables-->columns-->where clauses on the fly.

    I am thinking to maintain relationships, joins, etc., in some configuration tables. Based on the fields selected by the user, I can get these conditions and generate a query, execute and export to excel. Any ideas what is the good way of storing relationships, etc. in configuration table?

    Also suggest if there is any good excel add-in to do adhoc reporting. Point me if there is any other ways of doing.

    If I have to do it from scratch, i probably use VB.NET.

    Any ideas, information is appreciated. thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sounds like you need a reporting environment with a solid and flexible front-end and a GUARANTEE (!!!) that no ad-hocker is gonna kill your server... I'd say, start writing stored procedures and an inventory associated with them, that can be viewed from some front-end. Then let users use those procedures as they see fit. Doing it any other way may lead you where you don't want to be (if you know what I mean)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    I am thinking to put some restriction on the resultset level to fetch only first x number of records to avoid any complex queries(If possible). Anyway performance shouldn't any problem as only one or two persons will use this feature per sql server.

    Do you guys suggest any tools, ideas to get basic understanding of how can it be done?

    All I need at the end is to present the data in the excel sheet by allowing users to select what they want.

    I tried asking users what they exactly want, they keep saying they want everything. It is weird, but cant help.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Any reason that Excel's Data | Get External Data | New Database Query won't do what you want (except for the query governor)? I'd use the provided functionality unless there was some overriding reason to reinvent the wheel.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Posts
    11
    You are opening up your servers for some really dangerous queries (usually from ingnorance not maliciousness) from your users. I Would suggest that you make some views that join then main clusters of tables and then only expose those views to the Querying public. Also I would Make sure that the Public account has everything other than those Views Locked down as there are some really bad things that can happen if master database stored procedures can be accessed. (I have seen domain admin accounts created using a reporting account and Excell) Sorry for all of the doom and gloom but I figure I am the SQL guy and I get afraid whenever anyone wants to connect to my database and get data anyway other than what I let them.

    just some thoughts

    Tal McMahon

  6. #6
    Join Date
    Jul 2004
    Posts
    3
    Thanks Tal,

    I had no intention to give out the whole database to the users to choose from.

    I was thinking to select around 30 needed views, and store all the meta information in some configuration tables. Configuration table will consist of how all these tables are related(joined), pk columns, etc.,

    If user choose fields from 2 tables, my configuration table should know how to join these tables. Based on that information, it should generate SQL and pull the results.

    Is the solution ok? If so how should I design my configuration tables? And What information should I maintain? Any ideas?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •