Nice data selection

rbuser

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/

nardmoseley

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

rbuser

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/

nardmoseley

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

rbuser

> 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/

rbuser

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/

rbuser

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/