Creating Custom DataView

rbuser

Hello RB-Team,

I'm using RB 11.08 Enterprise and Delphi2009. Database is Oracle 11.
I developed a Custom DataView with your instruction behind this link:
http://www.digital-metaphors.com:8080/DADE/Custom_Dataviews

Instead of this code
lTable := lSQL.AddTable('clients');
lSQL.AddSelectField(lTable, 'Last_Name');
lSQL.AddSelectField(lTable, 'First_Name');
I' ve tried this one
{modify the local version}
lSQL.SQLText.Text := 'Select 'A' As FIELD1, 'B' As FIELD2 From DUAL';
lSQL.EditSQLAsText := True;

The report designer opens and I switch to the DADE-Tab. The DataView is
visible and
it's possible to preview the date.

Then I create a new DataView on with the designer. Menu => File => New... =>
Query Editor. After choosing a table and all fields there is a second
DataView.
Next I try to create a DataLink between these two DataViews via drag&drop
and it occours an AV in TdaQueryDataView.CreateLinkObject, because
lMasterFiled is NIL.

There's another way to do it. Open the Edit SQL window of the first DataView
and close it without doing anything. If I try to create the DataLink then it
works.

It seems that the table and field aliases were not set. The DUAL table of
Oracle contains only the 'DUMMY' field, but I use it as described above.

Could you help me, please?

Best Regards,
Hartmut

nicocizik

Hi Hartmut,

Rather than editing the SQL text manually (which limits the linking
ability), I recommend creating the query using the TdaSQLBuilder class. See
the following articles and help topic on the subject on how this class is to
be used. Using the SQLBuilder rather than creating your own manual SQL code
will run more efficiently as well.

http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder

Also a helpful example...

http://www.digital-metpahors.com/tips/CreateDataModCustOrderLinkedDataViews.zip

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

thank you for your reply.
I think the main problem is that my SQL is not a static one.
Our end users are able to write their own SQL, for example in a TMemo.
We take the SQL and copy it into a Query component.
Here is my whole procedure:

procedure TfTest.Button1Click(Sender: TObject);
var i : Integer;
FReport : TppReport;
FDesigner : TppDesigner;

FSQL : TdaSQL;
FDataModule : TdaDataModule;
FDataView : TdaSDQueryDataView;
FPipeLine : TppDBPipeline;
FSQLBuilder : TdaSQLBuilder;
qrDrucken : TSDQuery;
sl : TStringList;
FTable : TdaTable;
begin
FReport := TppReport.Create(Self);
FReport.CreateDefaultBands;
FDesigner := TppDesigner.Create(Self);
// TSDQuery is a substitution for BDE TQuery
qrDrucken := TSDQuery.Create(Self);
sl := TStringList.Create;
try
qrDrucken.DatabaseName := 'Your DatabaseName';
qrDrucken.SQL.Clear;
qrDrucken.SQL.Add('Select ''A'' As F1, ''B'' As F2 From DUAL');
qrDrucken.Open;
// Database as connection from DADE-Plugin
FSDDatabase := TSDDatabase.Create(nil);
FSDDatabase.Assign(qrDrucken.Database);

FDesigner.DataSettings.SessionType := 'SDSession';
FDesigner.DataSettings.DatabaseName := FSDDatabase.RemoteDatabase;
FDesigner.DataSettings.DatabaseType :=
TdaSDSession.GetCnxDatabaseType(qrDrucken.Database);
FDesigner.DataSettings.AllowEditSQL := True;
FDesigner.DataSettings.SQLType := sqSQL1;

if FileExists('c:\Temp\Test.rtm') then
begin
FReport.Template.FileName := 'c:\Temp\Test.rtm';
FReport.Template.LoadFromFile;
end;
FDesigner.Report := FReport;

{+++ Create DataView +++}
{create a datamodule - note: this is only necessary if you need to
stream the report definition to an .rtm or database}
FDataModule := TdaDataModule.CreateForReport(FReport);

{create a query dataview}
FDataView := TdaSDQueryDataView.Create(FDataModule);
FDataView.Parent := FDataModule;
FDataView.Name := 'Nexum';
{initialize the dataview}
FDataView.Init;

FPipeline := TppDBPipeline(FDataView.DataPipelines[0]);
FPipeline.Name := 'plNexum';
FPipeline.UserName := 'plNexum';

FSQL := FDataView.SQL;
FSQL.DatabaseName := FSDDatabase.RemoteDatabase;;
FSQL.DatabaseType :=
TdaSDSession.GetCnxDatabaseType(qrDrucken.Database);;
FSQL.SQLType := sqSQL1;
FSQL.Session := FDataView.Session;
FSQL.DataPipelineName := 'plNexum';

FSQLBuilder := TdaSQLBuilder.Create(FSQL);
FSQLBuilder.SQL.SQLText.Text := qrDrucken.SQL.Text;
FSQLBuilder.SQL.ValidateSQLText(FSQLBuilder.SQL.SQLText);
FSQLBuilder.SQL.EditSQLAsText := True;
FSQLBuilder.ApplyUpdates;

{get a reference to the dataivew's pipeline and assign a Name to the
pipeline}
// FDataView.OutOfSync;
// FDataView.Sync;

// Without this line, there are no fields visible
FPipeline.AutoCreateFields := True;
{connect report to datapipeline}
FReport.DataPipeline := FPipeline;

FDesigner.ShowModal;

FSQL.Free;
FSQLBuilder.Free;
finally
qrDrucken.Close;
qrDrucken.Free;
FDesigner.Free;
FReport.Free;
sl.Free;
end;
end;

Thanks in advance for your help,
Hartmut

nicocizik

Hi Hartmut,

There is now a patch available for RB 11.08 that solves this issue. Please
contact support@digital-metaphors.com to receive the patch. Also, once you
apply the patch there is no need to assign the Pipeline.AutoCreateFields
property to see the selected fields in the dataview.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,


it works with your patch.
Thank you very much!

Best regards,
Hartmut

rbuser

Hi Nico,

here we go again. I modified this procedure a liitle bit and I've got nearly
the same problems.
New in this procedure is that there's no SQLText or Query available, but a
ClientDataSet which is the master.

Again:
// Without this line, there are no fields visible
FPipeline.AutoCreateFields := True;

The Problem while creating links in the Data-Tab has occured.

Here's the source:
procedure TfReport.BitBtn1Click(Sender: TObject);
var oReport : TppReport;
oDesigner : TppDesigner;
oSQL : TdaSQL;
oDataModule : TdaDataModule;
oDataView : TdaSDQueryDataView;
oPipeLine : TppDBPipeline;
oSQLBuilder : TdaSQLBuilder;
sSQL : String;
i : Integer;
cds : TClientDataSet;
begin
oReport := TppReport.Create(Self);
oReport.CreateDefaultBands;
oDesigner := TppDesigner.Create(Self);

try
oDesigner.DataSettings.SessionType := 'SDSession';
oDesigner.DataSettings.DatabaseName := AktDatabaseName;
oDesigner.DataSettings.DatabaseType :=
TdaSDSession.GetCnxDatabaseType(AktServerType);
oDesigner.DataSettings.AllowEditSQL := True;
oDesigner.DataSettings.SQLType := sqSQL1;
if FileExists('c:\Temp\Test1.rtm') then
begin
oReport.Template.FileName := 'c:\Temp\Test1.rtm';
oReport.Template.LoadFromFile;
end;
{+++ Create DataView +++}
{create a datamodule - note: this is only necessary if you need to
stream the report definition to an .rtm or database}
oDataModule := TdaDataModule.CreateForReport(oReport);
{create a query dataview}
oDataView := TdaSDQueryDataView.Create(oDataModule);
oDataView.Parent := oDataModule;
oDataView.Name := 'Nexum';

{initialize the dataview}
oDataView.Init;

{ Later...I want to create a Pipeline to every available ClientDataSet}
for i := 0 to ComponentCount - 1 do
begin
if Components[i] is TClientDataSet then
begin
cds := TClientDataSet(Components[i]);
end;
end;

oPipeline := TppDBPipeline(oDataView.DataPipelines[0]);
oPipeline.Name := 'plNexum';
oPipeline.UserName := 'plNexum';
{ ClientDataSet... }
oPipeLine.DataSource.DataSet := cds;
{ Without this line there are no fields in DataView visible}
oPipeLine.AutoCreateFields := True;

gMetaDataManager.Clear;
oSQL := oDataView.SQL;
oSQL.DatabaseName := AktDatabaseName;
oSQL.DatabaseType := TdaSDSession.GetCnxDatabaseType(AktServerType);
oSQL.SQLType := sqSQL1;
oSQL.Session := oDataView.Session;
oSQL.DataPipelineName := 'plNexum';
// oSQLBuilder := TdaSQLBuilder.Create(oSQL);
// oSQLBuilder.SQL.SQLText.Text := sSQL;
// oSQLBuilder.SQL.ValidateSQLText(oSQLBuilder.SQL.SQLText);
// oSQLBuilder.SQL.EditSQLAsText := True;
// oSQLBuilder.ApplyUpdates;
{connect report to datapipeline}
oReport.DataPipeline := oPipeline;

oDesigner.Report := oReport;
oDesigner.ShowModal;
oReport.Free;
finally
oDesigner.Free;
end;
end;

Thank you in advance and regards,
Hartmut

nicocizik

Hi Hartmut,

Looking at your code below it seems that you are trying to connect the
pipeline of the DADE dataview to an external dataset (TClientDataset). This
is simply not the way DADE is designed to work. The QueryDataView by
definition is a container for the entire data retrieval process (SQL -
Dataset - Datasource - Datapipeline).

The idea of connecting a dataview to an existing external dataset is
something we may consider for a later release. Currently you will need to
connect your TClientDataset to a TppDBPipeline object on your form (or in
code) to use that data inside the report.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

we haven't found a solution for this problem.
Maybe, is there another way to do it? The thing is the master I have is only
a DataSet, but not a DataSource. Also it could be a TSDQuery (similar to
TQuery) object as master. But this contains a SQL with parameters, which
can't be used like this:
SDQuery.SQL.Add('Select RecNo From DUAL Where RecNo = :RECNO');
SDQUery.ParamByName('RECNO').AsInteger := 1;
FSQLBuilder.SQL.SQLText.Text := SDQuery.SQL.Text;


Or is there an event where I can use ReportParameter while navigating
through the DataSet?

I want to give master data to DADE and end-user should be able to create
SQL-DataViews and link them with the master.

Best regards,
Hartmut

nicocizik

Hi Hartmut,


Ideally you will do all of this in DADE. There is no need to "give" DADE
any data, you simply create an manipulate the query inside DADE, then your
users can add-on, link to, or remove anything they would like.

With DADE there is no need for a SDQuery object on your form, DADE will
create it on its own using the SQLDirect plugin available from their website
and you can use it as you would normally from within ReportBuilder.

Note that it is also possible to perform all the parameter operations you
mention below in DADE with RB 11 or later.

http://www.sqldirect-soft.com/download.html

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com