ReportInitializeParameters - TdaSQLBuilder question

rbuser

Hi again,

Still playing with the dade things and here something i can't figure out
exactly.

I have found in the helpfile that i can put something like this in the
ReportInitializeParameters event in Delphi.

Var lSQLBuilder: TdaSQLBuilder;

lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline.SQL);
lSQLBuilder.SearchCriteria.Clear;
lSQLBuilder.SearchCriteria.Add('STUKNUMMERS','ST_KLCOD','=',
cxButtonEdit1.EditValue);
lSQLBuilder.ApplyUpdates;

Which works fine, but i wonder how i would do the same for a second query
(or third etc)on the same report.
Suppose i have another query which works for example on a subreport how
would i add Searchcriteria on that query from the same event?

Oh, i'm still using 9.03 btw, have yet try out the version 10.

Thanks in advance.

Franky

nardmoseley

You can create a SQLBuilder instance for any datapipeline - pass the
DataPipeline to the constructor.

lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);

or

lSQLBuilder := TdaSQLBuilder.Create(mySubreport.Report.DataPipeline);

When using RAP code you can reference the datapipline by the username that
is displayed in the data tree

lSQLBuilder := TdaSQLBuilder.Create(Customer);

rbuser

Hi Nard,

Thanks for your reply, and i apologise for the many questions but i don't
quite understand 100% yet.
Suppose i don't have any pipelines on my form and only have queries on the
reports Dade tab.
One query is called 'Query1' and another is called 'Query2', Query1 is used
for the main page and Query2 for the subreport page.
In delphi, in the ReportInitializeParameters event i need to put a where
clause on both queries(different on both).
What do i put in the Create constructor for query2?
Delphi won't recognise 'Query1' of course, i figured it would be more like:
lSQLBuilder := TdaSQLBuilder.Create(Report.SubReports[0]DataPipeline);

Sorry for being a pain, just trying to figure out things here

Secondly, i will have to 'link' the Query2 to Query1(Master-detail), so i
guess i need to assign the keyvalue.
What's the best event in the report to do that?
I don't like to compare products but in Fastreports you can assign a
MasterData to a Query and assign the keyfields, which is what i'm looking
for in rb also.

One final question,
I found out where and how to make a Global variable in reportbuilder but is
it possible to assign a value to it from a event on the report component in
Delphi?

Thanks for the answers

Franky

nardmoseley

- What might be more productive is if you describe details about the
reporting solution you are trying to design and implement. What is the goal?

- The Data workspace (DADE) supports the creation of DataViews. A DataView
is a container for the data access components (query dataset, datasource,
dbpipeline). See the DADE thread of the Tech Tips newsgroups for an overview
of the architecture.

- The Data workspace inlcudes tools to visually design and link queries. You
can also define search criteria that are specified as AutoSearch. For
AutoSearch criteria, a dialog will automatically be created and displayed.
Once the user enters the criteria, the SQL will dynamically generated. When
queries are linked, special linking SQL is generated for the detail query.

- The Visual Query tools and the TdaSQLBuilder class can configure the
TdaSQL object that is associated with a QueryDataView. The TdaSQL object
then generates a SQL string that is assigned to the query dataset.

- You can use myReport.DataPipeline and mySubreport.Report.DataPipeline to
reference the datapipeline associated with a main report and a childreport
respectively. You can alternatively use mySubreport.DataPipeline (this is a
convenies returns the childreport.DataPipeline).

- If you have ReportBuilder Enterprise you can optionally use the Calc
workspace to code run-time Pascal (we call this RAP). RAP supports the
syntax that enables you to code the username of the pipeline.

- If you are using Delphi code, you can prepare the SQL prior to calling
Report.Print.You can also use the Report.OnInitializeParameters event and
Report.BeforeOpenDataPipelines events to configure parameters and modify
sql, link datasets etc.
\

rbuser

Thanks for the explanation Nard.

I understand it's difficult to understand what exactly i want to do, I will
try to explain the best i can.

Old situation:
In Delphi, i have on a form, one ppReport and one pPDesigner component,
linked to each other; 3 Queries and Datasources each with their own
pipeline.
This is one 'master' query and 2 detail queries.
The report i'm calling uses the master query on it's main page and the 2
detail queries each on a subreport page on the report.
For each query there are options the users can set on the form in Delphi
which puts a where clause on all 3 queries.

The goal:
I'm redesigning the form and i would like to use Dade instead of having the
queries and pipelines on the form in Delphi.
Reason:This way the report contains the sql and our reports are much more
flexibel on adding fields, changing sortorders etc..

New situation:
The form in Delphi has a ppReport and a ppDesigner - that's all.
The report has 3 queries on the dade page which have to be linked like in
the old situation, and like before the main query is for the main page and
the 2 others are for subreport pages.
The queries on the dade tab have sql without a where clause, this where
clause has to be put on each query by use of the ReportInitializeParameters
event in Delphi (not on the report).
I already have the where clause working in the ReportInitializeParameters
event for the main query, but i don't know how to access the other 2 queries
to assign a where clause.
Your example said: .Create(mySubreport.Report.DataPipeline); , but what is
mySubReport? I only have one report component on the form in Delphi,
MySubReport of course exists if you are using RAP in the report but my
question was how i can access the other Dade queries from the
ReportInitializeParameters in Delphi, using one ppReport component.

I hope what i'm trying to do is clear now, simply said i can access the
reports main dade query from Delphi to put a where on it and just need to do
the same for the other 2 also.
I will read those DADE thread of the Tech Tips also.

Franky

nardmoseley

- See article below. When you link DataViews in DADE (the Data workspace),
special linking SQL is generated for the detail queries. When you add a
Where clause to the master, the where clause of the detail will be updated
automatically.

- The following code shows to access the TdaDataModule associated with the
report and get a list of the datapipelines

uses
daDataModule;

var
lDataModule: TdaDataModule;
lPipelines: TList;

lPipelines := TList.Create;

lDataModule := daGetDataModule(myReport);

lDataModule.GetDataPipelines(lDataPipelines);






-------------------------------------------
Tech Tip: Linking DataViews
-------------------------------------------

Overview
---------

Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
the detail data be sorted by the linking fields. When DataViews are linked,
DADE generates special linking SQL for the detail DataView. The linking SQL
(Magic SQL) can be viewed by positioning the mouse over the DataView window
and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
can be viewed by pressing Shift + Left Mouse button.

ReportBuilder's DataPipeline architecture includes support for linked data
traversal. Whenever the record position of the master data changes, the
traversal logic compares the master and detail field link values and then
determines where to locate the record position for the related detail data.
The linking logic will only work properly if the Database engine's collation
of the detail query result set is in sync with ReportBuilder's internaly
field link comparison logic.

rbuser

Thanks Nard.

That last example put me in the right direction and i'll be able to make
things work now, thanks for the help and your patience.

Franky