Parameterized SQL

edited February 2012 in End User
Hi,

We're using Report Builder 12.04 Enterprise for Delphi 2009.

When joining two dataviews you have the option of Magic SQL, Manual SQL
or Parameterized SQL. Magic and Manual are both self-explanatory, but
how does one use Parameterized SQL? Where does one set the value of the
parameter?

Could you please explain or point me to an article or help file, as I
was unable to find anything when I searched. Perhaps I'm just being thick.

Thanks,

Steve Branley

Comments

  • edited February 2012
    Hi Steve,

    Parameterized linking in DADE function very similar to the VCL.

    In the VCL, a query component will have a property (MasterSource, etc.)
    that can be used to link a detail query to a master query. For the
    detail SQL, you define a :FieldName in the Where clause and that refers
    to a field in the master query.

    Internally the dataset parses the SQL and generates a dataset parameter.
    As the master dataset traverses its data, the detail parameter is
    resolved and the detail query fires. This happens for each master
    record change.

    This differs from the way MagicSQL works in that for MagicSQL, the
    detail dataset does not need to execute for each master record change.

    Parameterized linking can be useful however when linking manual SQL
    datasets.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited March 2012
    Sorry, perhaps I'm being thick, but I can't seem to get this to work.
    How do I go about joining two dataviews with parameterised SQL as an
    End-User? Or is it something that can only be done programatically? All
    I get is an error saying one or more required values was not provided.

    Do you have an example or wiki article anyway?

    Thanks,

    Steve

  • edited March 2012
    Hi Stephen,

    Sorry if I was unclear. You link the datasets the same way you would
    normally. The only thing that changes is that the linking is handled
    behind the scenes by the Delphi VCL rather than ReportBuilder. It would
    be as if you were to set the MasterSource property of a TTable on a form.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited March 2012
    Also, if you wish to use this type of linking, simply link two datasets
    as you would in the past (drag a field). Then you can access the
    linking dialog by double clicking the link line. From this dialog you
    can select the type of linking you would like to use.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited March 2012
    Well this is a bit odd then, as we've never been able to make
    Parameterized SQL work. We create the link in the same way as with Magic
    SQL or Manual SQL, by dragging a field on one dataset to another and
    then double clicking the link line and selecting Parameterized SQL, but
    when we preview or run the report all we get is an error that says "No
    value given for one or more required parameters".
    I'd always assumed this was because we needed to somehow set the value
    of the parameter manually, but from what you're saying this is not the
    case. Any ideas why we're getting this error?

    Thanks,

    Steve

  • edited March 2012
    Hi Steve,

    In my quick testing with the DBDEMOS database, parameterized linking
    worked as expected. What database and connectivity are you using with
    your application?

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.