Dynamic Reports, Joins, Tables and Fields

rbuser

Hi all,

I'm having some trouble creating a multi-table Dataview, with some
selectfields and joins.

In short I need a function or function(s) that will except a list of one or
more MS SQL (ADO) tablenames, a list of fields to select per table (or auto
all fields from list of said tables), and joins for any of those tables I
wish to join, by the fields I wish to join them on.

SOME CODE INCLUDED BELOW:-

Failing that, does anyone know of a good shrink??
)

T.I.A.

----------------------------------------------------------------------------
------------
type TawJoinData=Record
ToID,FromID: Integer;
aFieldName1: string[30];
aFieldName2: string[30];
aJoinOpType: TdaJoinOperatorType;
end; //type

function TawWizfrm1.CreateDataView(aReport: TppReport; aName,aDatabaseName:
string; Tables: array of TStringList; Joins: array of TawJoinData; aType:
TppDatabaseType; aSQLType: TppSQLType): TppDBPipeline;
var i,j: integer;
lSQL: TdaSQL;
lTable: array of TdaTable;
lDataView: TdaADOQueryDataView;
lDataModule: TdaDataModule;
lPipeline: TppDBPipeline;
begin
try
lDataModule:=daGetDataModule(aReport); //create datamodule
if lDataModule=nil then
lDataModule:=TdaDataModule.CreateForReport(aReport);

lDataView:=TdaADOQueryDataView.Create(lDataModule); //create dataview
lDataView.Name:=aName;
lDataView.Parent:=lDataModule;
lDataView.Init;

lSQL:=lDataView.SQL; //SQL
lSQL.DatabaseName:=aDatabaseName;
lSQL.DatabaseType:=aType;
lSQL.SQLType:=aSQLType;
lSQL.Session:=lDataView.Session;
lSQL.DataPipelineName:=aName;

//this actually adds tables but no fields

//
lSQL.CreateJoinObjects(lJoins[i].aTableName1,lJoins[i].aTableName2,lJoins[i]
.aJoinType,
//
lJoins[i].aFieldName1,lJoins[i].aOperators,lJoins[i].aFieldName2);

//add tables and fields
SetLength(lTable,succ(high(Tables)));
for i:=low(Tables) to high(Tables) do
if Tables[i].Count>0 then lTable[i]:=lSQL.AddTable(Tables[i][0]);
//add joins
for i:=low(Joins) to high(Joins) do

lTable[Joins[i].FromID].AddTableJoin(lTable[Joins[i].ToID],Joins[i].aFieldNa
me1,Joins[i].aFieldName2,Joins[i].aJoinOpType);
//add fields
for i:=low(Tables) to high(Tables) do
for j:=1 to Tables[i].Count-1 do
if Tables[i][j]<>'' then
lSQL.AddSelectField(lTable[i],Tables[i][j]);

lPipeline:=TppDBPipeline(lDataView.DataPipelines[0]); //pipeline
lPipeline.Name:=aName;
lPipeline.UserName:=aName;

lDataView.OutOfSync;
lDataView.Sync;
Result:=lPipeline;
except
result:=nil;
end; //try
end; {func, }

digitalmetaphors

Here are a couple of examples that creates dataview and joins in code. It is
a DBE example, but it should work the same way for an ADO query dataview.

http://www.digital-metaphors.com/tips/AddDADEJoin.zip

http://www.digital-metaphors.com/tips/CreateDataModCustOrderLinkedDataViewsA
DO.zip

Cheers,

Jim Bennett
Digital Metaphors