Skip to main content
J
jbreslow
Inspiring
August 25, 2011
Question

Generating Dynamic Query for Ad-Hoc Reports

  • August 25, 2011
  • 1 reply
  • 962 views

Hello,

What is the best way to create a dynamic query to generate ad-hoc reports? I have a couple of ideas but not sure which would work best and be most flexible.

I would like our users to be able to check checkboxes for what data they would like returned in their report. The data would be pulled from a number of tables, 10+, depending on what pieces of data they checked.

  • Should I write one dynamic query, with a bunch of IF statements in it?
  • Should I write many individual queries and then join them together in a temp query?
  • Should I create a temp table on our SQL server which contains the data from all of the tables; then query that? (I am worried about data being out-of-date with this option.)

Which one of these solutions should I go with or is there a better solution that I am not considering?

Thanks for the help.

Josh

    This topic has been closed for replies.

    1 reply

    A
    Adam Cameron.
    Inspiring
    August 25, 2011

    I'd probably write a proc which takes values for the params the user has set (or pass NULL if they've not selected that param).

    You should not do a bunch of individual queries and then try to swtitch them together.  THis means the DB is doing half the work, and CF is doing the other half which isn't a good way to distribute the work.  Use CF to manage the web pages, and use the DB to manage the data.

    --
    Adam

    J
    jbreslowAuthor
    Inspiring
    August 25, 2011

    Do you mean a Stored Procedure? Would the Stored Procedure then have one query on it with a bunch if IF statements? Maybe a bad example, but something like this?

    select @columns

    from @tables

    where 1 = 1

    if @table = 'users'

    being

         and users.id = someOtherTable.id

    end

    if@table = 'address'

    being

         and address.state_id = states.id

    end

    A
    Adam Cameron.
    Inspiring
    August 25, 2011

    Do you mean a Stored Procedure? Would the Stored Procedure then have one query on it with a bunch if IF statements? Maybe a bad example, but something like this?

    Yep.

    I haven't written a proc for a coupla years (I have DB people to do that for me now, bless 'em), but every DB is different, so the approach will vary depending on what DB you have.  It's perhaps time to buy a book or do a google or something.

    --

    Adam

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices