Home DADE
New Blog Posts: Merging Reports - Part 1 and Part 2

Correct syntax for linking a parameter directly into manually "Edited SQL"

edited March 2013 in DADE

Hi Guys,

1. Under the "Data" tab in Report Builder a Manual SQL statement has been
created.
2. A Parameter has been setup under "Design>Report Tree>Report>Parameters".
3. What is the correct Syntax for linking the Parameter into the Manual SQL
statement directly?

We are running: ReportBuilder Enterprise Edition v11.05

For Example:

SELECT *
FROM Employees
WHERE TerminationDate < ??????

I have seen examples like Report.Parameter('MyParameter') but can't get
these to work.

Thanks
Ben Gelton

Comments

  • edited March 2013
    Hi Ben,

    To access a parameter from SQL code in DADE, you will want to use the
    following syntax:

    WHERE TerminationDate < :MyParameter

    This is identical to the Delphi parameter syntax.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited March 2013
    Hi Nico,

    Thanks for the info, I have tried your suggestion but get the following
    error:

    --------------------------------
    Invalid SQL statement.
    Incorrect syntax near '<'
    --------------------------------

    I am trying to place the parameter directly into my manually "edited SQL"
    statement in the "Query Designer" window of Report Designer.
    I have attached a couple of screen shots as examples of what I am trying to
    do.

    We actually embed "Report Designer" inside our own product (I do not have
    access to the source code for our own product so normally have to work
    within the bounds of the exposed interface of your app. I also do not
    understand the relationship of DADE to the designer maybe you could
    explain...)

    Is what I am asking possible? What are the steps?

    If it is not possible I have tried an alternative approach which appears to
    work:

    1. I created an "SQL View" in our main database that contains the raw SQL.
    2. I can see the resulting data under the Data tab in Report Designer (so it
    appears that everything is working, this then avoids the need to manually
    edit the SQL)
    3. I then create a parameter in the normal way via the "Search" magnifying
    glass option.
    4. Report filters as expected

    Your feedback is appreciated.

    Thanks
    Ben Gelton

  • edited March 2013
    Hi Ben,

    Which version of ReportBuilder are you using?

    In my testing with the latest version, the following SQL code executed
    correctly using the DBDEMOS database.

    SELECT orders.OrderNo, orders.CustNo,
    orders.AmountPaid
    FROM "orders.db" orders
    WHERE orders.CustNo = :Param

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited March 2013

    For future reference...

    1. Please do not post attachments to the newsgroup. :)

    Attachments may be sent to support@

    2. If you only have access to the embedded solution, we cannot provide you
    with tech support. :)

    ReportBuilder is a single developer license. We provide tech support to
    Delphi developers that use Delphi and ReportBuilder to build reporting
    solutions.


    In regards to your issue...

    RB 11.08 is the last produced released for RB 11.x. I recommend updating and
    retesting. If that does not resolve the issue, then download a trial version
    of RB 14.08 and perform the same test.

    Going forward hope you will be upgrading to RB 14, which includes many
    powerful new features.

    Here is a link to What's New for RB 14. (Use the side menu to see what was
    new for prior releases)

    http://www.digital-metaphors.com/rbWiki/General/What's_New/RB_14


    -
    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited March 2013
    Hi Nico,

    The version is:

    ReportBuilder Enterprise Edition
    Version 11.05

    Thanks
    Ben.

This discussion has been closed.