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

Nice data selection

edited May 2004 in RAP
I have a SQL Server database with linked tables, and am connecting via
ADO. I want to have the user select one of the records in the main
database and then output a report based on the other tables links to the
selection. Basic master detail stuff.

But I can't work out how to let the user select anything when using the
DADE data selector. They can select anything they want from the database,
but I can't seem to get a "join" on anything other than the ADO
connection. I figured I could use a DBPipeline as a way of feeding into
the system, and can set a global variable in the Calc code to the database
record I want. But I can't find how to reference a global variable from
the SQL.

Could someone please tell me if there is a means of modifying the SQL to
adapt to external data? Or adding to the ADO connection data to allow a
custom field to be added to it? Or suggest how I might do this in some
other way.

All thoughts and suggestions very welcome...

/Matthew Jones/

Comments

  • edited May 2004

    For standard Master/Detail using DADE, you can visually link the detail
    queries to the master in the data workspace. The master query Where clause
    can include one or more AutoSearch parameters.

    ReportBuilder automatically generates special linking SQL for the detail
    queries that will include the search parameters. To see the linking SQL,
    position the mouse over the detail query and press ctrl + left mouse. To see
    the query results, press shift + left mouse.




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


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited May 2004
    Thanks - I think I'm not making myself clear though. I'm using the ADO
    connection stuff to connect to SQL Server. I need the master query to
    select a single record (the detail queries are indeed easy to link - I am
    impressed with the whole solution). The thing is that the ADO data query
    tab only allows me to do things with the main ADOConnection. I can't seem
    to do a join on another pipeline. What I want is to be able to reference a
    "global" somehow in the query. Even just getting a simple number is all I
    need. This the SQL would end up as "WHERE MasterIndex = #EXTERNAL_VALUE#".

    Your suggestions all look good, but I can't see how to get them into the
    ADO query.

    I can see how to get it into code, but not the query.


    That would be easy - can I reference it in the ADO page?


    I've not seen this before, but again, how to get it into the SQL?

    Essentially, I think it would be perfect to allow the SQL to reference
    some sort of "global variable" that the code can set. Essentially SQL
    Parameterisation. I'm hoping I'm just blind to the obvious solution here,
    given how comprehensive the rest is, but I just need that nudge to
    complete it.

    Many thanks for your attention.

    /Matthew Jones/
  • edited May 2004

    1. I do not understand what you mean by "ADO connection stuff". If you are
    using ADOSession, then the Designer.DataSetttings.DatabaseName can be used
    to refer to an ADOConnection object that specifies the connection string for
    the database. The Query tools enable you to build queries on tables that
    reside in the specified database.

    2. I think the real question is how can you dynamically specify a search
    parameter for the Where clause. One solution is to specify an autosearch
    parameter for the Where clause. Then use the
    Report.BeforeAutoSearchDialogCreate event to do something like

    Report.AutoSearchFields[0].SearchExpression := {global value here}
    Report.ShowAutoSearchDialog := False;




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


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited May 2004
    > Report.AutoSearchFields[0].SearchExpression := {global value here}

    This sounds the ideal thing. Unfortunately I just cannot work out how to
    get it to work at all. Is this Delphi code or RAP code? The Autosearch
    example for SQL doesn't run either, but that is quite complicated and
    changes the SQL rather than passing in a parameter. The main hindrance
    seems to be that I can't put a parameter of any kind in the Search box in
    the DADE Query editor. I can put a constant, but not anything else.

    For some reason the autosearch dialog will not appear at all for me today.
    Yesterday it worked but I didn't like it. Today I learn it can be
    customised, and I can't get it to appear!

    Silly question 73: I'm using 7.02, but I see there is a 7.03. Is there
    anything that changed that may affect this?

    Next: The AutoSearchFields, they are different to the Parameters yes?

    Report.BeforeAutoSearchDialogCreate - in Delphi this should stop in the
    debugger yes? Why might it not? I think I'm going to reboot my machine to
    make sure there are no environmental issues here.

    /Matthew Jones/
  • edited May 2004


    Okay, so you have to click the search button to see it. Seems odd to call
    it "Auto" when it is manual. The rest of the questions remain though. 8-)

    /Matthew Jones/
  • edited May 2004


    Okay, I'm learning far too much tonight. The line:
    ppReport.AutoSearchFields[0].SearchExpression := 40 {value};

    is in my Delphi code. If I put it in the ppReportGetAutoSearchValues
    event, it only ever gets called when the user clicks on the designer
    search button. And AFTER the dialog is closed. It is never called when the
    preview pane is updated. It seems that the events purpose is to allow me
    to find out what the user asked for, rather than for me to set it. Fair
    enough.

    So I had the most stupid idea. Maybe I could use the line in ordinary
    code? And it worked. I can now set the AutoSearchField via a combo box,
    tell the report to reset and printtodevices, and hey presto the right
    master record appears. The solution was indeed there all along, I was just
    looking in the wrong places. And mis-understanding the terms "auto" and "
    GetAutoSearchValues". Maybe one day I'll understand them.

    /Matthew Jones/
This discussion has been closed.